Dear DB2 experts,
Have an issue that we need your advice/help on. Basically, we do an export from db2 by extracting data from 3 tables. One of the tables has a clob field with multiple lines of data that we need to extract. So, in our export (please see sample below) we put the clob field as the last field and run the export db2 says it exported 15000 rows.

Now we use the DEL file and run a java program to parse it and load the data into a different database which is non DB2. Now during the parsing, we loop thru the file and populate the data into a separate column on this non db2 database. When the java code completes, the count is off by either 5 or 10 records depending on the number of records that we exported. We have gone thru the java code line by line to verify if we were missing anything and they all seems to look ok. We have also put a count on the number of lines we read and it exactly matches to the number of lines in the DEL file. So, we are not sure if the number that gets displayed after the db2 export is accurate. We have noticed that this happens only on data which spans across multiple lines.

So any ideas/suggestions that you can provide to help us look into?

Here’s a sample of the data in the DB2 DEL file

112345,0031716,1,25,-1,0,"Email","746335","#Wed Nov 10 20:57:44 EST 2008
REALACCOUNTNUMBER=XXXX6789
ROUTING_NUM=XXXX6283
ACCOUNT_NUMBER= XXXX6789
ACCOUNTNUMBER= XXXX6789-1
MAX=
MIN=5.00
ROUTINGNUMBER= XXXX6283
"
112346,0031718,1,25,-1,0,"Email","746335","#Wed Nov 10 20:57:45 EST 2008
subject=Test message
to=xyz@usemail.com
from=onlineemail.com
"

Thanks
Santhosh

DB2 will export up to 32KB of LOB data, the rest will be truncated (by default). So, if your LOB value exceeds 32KB, you need to use LOB modifiers. Please see here for more info:
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows


I'm not sure if this is related to your problem or not... probably not since the number of records should still match, just some data may be missing.

What I'd suggest to do is to create a DB2 table to match your exported file and then try to load it to see if the number of exported and loaded records match. This may help you figure out if the problem is within DB2 or somewhere else.

My guess would be that either the Java application or the target DBMS doesn't correctly parse the data file due to the additional line breaks in the CLOB values. Due to the truncation mentioned by db2girl, I'd recommend to use the LOBSINFILE option on the EXPORT command.