Truncated CLOBs?
Michael Caines
I am trying to import a 62KB string from a flat-file and store it in a
CLOB field. This string is getting truncated upon import to 32,700
bytes. This happens both when I try to import it into a 64KB CLOB
column, and into a 128KB CLOB column.
The columns are definitely defined as CLOBs (not LONG VARCHARs) so I'm
really confused by the 32,700 figure...
Michael
dav...@ca.ibm.com
Sounds like you might be hitting the statement length limit of 32K,
but it depends how you are doing the 'import'. Let us now the command
and the error (if any). Note, if the clob is a literal in the statement,
then you definatly hit the limit.
FYI, v6.1 has a 64K statement length limit.
Hope this helps,
David.
Michael Caines
is not a literal in the actual SQL statement. Column 7 is the 128KB CLOB
column. Column 6 is the 64KB CLOB column, which yields similar results.
Maybe this isn't the way you're supposed to do it...
See the statement I used and the results below.
Thanks,
Michael
From the Command Center's Results window:
--------------------------- Command entered ----------------------------
import from D:\claimmd2.txt of del modified by coldel0x09 method
p(1,2,3,4,5,6,7) insert into test_tab
------------------------------------------------------------------------
SQL3109N The utility is beginning to load data from file "D:\claimmd2.txt".
SQL3115W The field value beginning ""<H>Inital Page of Submission<" in row
"1" and column "7" is longer than the longest allowable table column. The
value was truncated.
SQL3110N The utility has completed processing. "1" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "1".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "1" rows were processed from the input file. "1" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 1
Number of rows skipped = 0
Number of rows inserted = 1
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 1
Doug Doole
To import LOBS > 32K then need to be in individual files.
--
___________________________________________________________________________
Doug Doole
DB2 Universal Database Development
IBM Toronto Labs
Miro Flasza
As documented in the Administration Guide (section: Using the
Import utility) the maximum length of a single field when using
a delimited ascii file (or as a matter of fact any other file
format) is restricted to 32KB.
You might want to explore the lobsinfile option of the import
command, it's a little cumbersome to set up (each LOB value
needs to be in a separate file) but should get the job done.
Regards
Miro
Michael Caines
Michael