Focal Point
[CASE-OPENED] DB2 bulkload gives error with last column being character

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/5967009926

April 02, 2013, 06:18 AM
Martin vK
[CASE-OPENED] DB2 bulkload gives error with last column being character
With DataMigrator (7.6.4) I just started using Bulkload flows into DB2 (running on unix V9.5).
I got warning messages on almost all loaded records:
SQL3125W The character data in row "F0-1" and column "2" was truncated because the data is longer than the target database column.

As it is only a warning the bulk load did end succesfully, but about 100 times slower as should be because of all the message processing.
After trying several things I did find out it has to do with the last column being a character field, and I think it might be caused by the CR/LF as record delimiter in the tab-delimited file on Windows being loaded into DB2 on unix where there is a 1 character record delimiter.

Below the relevant code from the DM flow:
TABLE FILE &BLKIN

PRINT
E01 NOPRINT
COMPUTE CODEKEY/I11=E01;
COMPUTE AUDATEX/A6=E02;
ON TABLE HOLD AS SQLINLD FORMAT TAB
IF RECORDLIMIT EQ &STOPAT
END
-RUN
-SET &&CM__RETURN = IF &LINES EQ 0 THEN 18708 ELSE &FOCERRNUM; -IF (&&CM__RETURN NE 0) GOTO :ENDJOB; -TYPE (ICM18216) Starting bulk load procedure

BULKLOAD tst_rdccodeaudatex FOR SQLINLD CODEKEY; AUDATEX; BULKPARM BLK_MODE=REPLACE END -RUN

I tested it with several DB2 tables, and it happens with all tables having a last column being character. If I have a table with the last column being numeric, I do not have this problem.

I tried several workarounds like changing the order of the Target Transformations, but it did not help, as in the tab delimited file and the bulkload always the order is taken from the synonym. That was finally the workaround that I found: to edit the synonym to change the order of the fields to make sure that the last field is numeric.

Nevertheless this workaround does not work if I have a DB2 table with only character columns.

Is there any other setting that I should change to prevent this problem?

This message has been edited. Last edited by: <Kathryn Henning>,


WebFocus 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
April 04, 2013, 10:33 AM
Clif
7.6.4? Really? That release shipped in 2007. We've made a lot improvements in the last 5½ years. I would recommend that you explore upgrading at your earliest convenience.

In the mean time since I see that you've already reported this issue to hotline please continue to work with them so that they can reproduce the problem so that we can help you to find a solution