I have created a table with clob column and a long tablespace for it to go in.
However when I try to use the LOAD utility it truncates the column at 32K. The data is stored in a simple text file.

I thought I have done everything correctly...but obviously I missed something...

If somebody could take a look at the following SQL I would much appreciate it.

Here is my SQL:

CREATE TABLESPACE RBTEMPLATES_TS
MANAGED BY DATABASE USING (FILE '/udb/dmscontainers/rbtemplates' 1M)
;

CREATE LONG TABLESPACE RBTEMPLATELONG_TS
MANAGED BY DATABASE USING (FILE '/udb/dmscontainers/rbtemplateslong' 1M)
;

CREATE TABLE DB2ADMIN.RBTEMPLATES
(TEMPLATE_NM CHAR(30) NOT NULL
,TEMPLATE_INSTANCE CHAR(20) NOT NULL
,EFFECTIVE_DT DATE NOT NULL
,TEMPLATE CLOB(50000) NOT LOGGED
,PRIMARY KEY(TEMPLATE_NM,TEMPLATE_INSTANCE,EFFECTIVE_DT))
IN RBTEMPLATES_TS LONG IN RBTEMPLATELONG_TS
;

You missed the most important one... your LOAD command!

If you're loading from a non-delimited ASCII file "The total length of any record, including LOBs, cannot exceed 32KB." (from the Data Movement guide).

Is that possibly what you're running into?

It is a tildy delimited file with one of the columns > 32K.....



LOAD FROM ./RBTEMPLATES.dat OF DEL MODIFIED BY pagefreespace=0 totalfreespace=0
coldel~ MESSAGES db2load.msg REPLACE INTO DB2ADMIN.RBTEMPLATES STATISTICS NO CO
PY NO INDEXING MODE AUTOSELECT;

The documentation seems to suggest the 32K restriction is only for positional ASCII, but I'm pretty sure it applies to any ASCII file where lobsinfile isn't used.

I'm almost 100% certain it's your LOAD that's truncating, the column definition looks fine.

I externalized my LOBs in a separate file and it worked....

Thanks for you help..much appreciated.


Now I get to test the performance of this yippeeee!

No problem, good luck!