Hi all,
we are using DB2 v8.1.0.104,FixPak "11"

we have table with 3 columns one is clob(500000),row count over 4 million,
iam trying to export data and import it back to have limited back in table.

I tried for 200,000 row for testing purpose, it takes around 50 minutes to import back. i remove the index to make it bit easy the column value is almost near to max allowed size( clob 500000).
Is there any better way to make the import faster ?

below are the command iam using:
db2 export to file.del of del modified by lobsinfile messages export.msg 'select * from A_table';

db2 import from Event.del of del lobs from /home/Data_export/ modified by lobsinfile commitcount 10 messages import.msg 'insert into A_table'

--
thanks in advance..

I don't think I understand why you are doing this, but LOAD is usually faster than IMPORT.

You're modest, LOAD is always faster than IMPORT.
Is the flat-file essential? A load from cursor workt great!
When the databases are not on the same machine, create a nickname (google for federation) for the source-db on you target machine and "load from cursor".

thanks all for your suggestions,
I have used db2 load to import data to table and the process was successful
but iam now unable to access the table.

Details:
***Type: com.ibm.db2.jcc.c.SqlException
***Error Code: -668
***SQL State: 57016

DB2 SQL error: SQLCODE: -668, SQLSTATE: 57016, SQLERRMC: 1;

please suggest me..

thanks all.

Have a look at the manual. It contains detailed descriptions for all error messages that DB2 issues: SQL0668N

Re: sql0668n RC=1. The table is part of R/I and therefore is in check pending state. The set integrity command will take it out of this state.

Hi all,

thanks for your reply, Load is much faster compaired to import but unfortunately we cant use load because it dont log & we need db2 logs to keep in sync with another server.

basically we have 3 table with huge data and we are running out of table space and we thought of droping the table to reclaim space provided we get our data back.

Is there any easier way of copying data to temp table & moving it back to original tables once they are recreated ?

please suggest .. thanks

You may want to lower the high-water mark and reorganize the table. That would be much easier than copying data around.

Hi all,

we are trying to export 70million row worth data having clob column.

iam using below cmd:
db2 export to test.del of del modified by lobsinfile messages error.msg "SELECT * FROM TEST_TABLE";

getting error:

SQL3040N Unable to use the lobpath/lobfile parameters as specified. Reason
code: "6".

SQL3040N
The utility cannot use the file name file-name specified in the option-name parameter. Reason code: reason-code.

Explanation
6
The sum of the starting position and the length of the input data exceeds the size of the file with file name file-name.

-----------------------------------
any idea if there is any limitation on clob file size. It only exported 4millions rows of data.

try specifying multiple paths with the LOBS option.