Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CASE-OPENED] DB2 bulkload gives error with last column being character
Go
New
Search
Notify
Tools
Reply
  
[CASE-OPENED] DB2 bulkload gives error with last column being character
 Login/Join
 
Platinum Member
posted
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
 
Posts: 153 | Registered: March 29, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 382 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CASE-OPENED] DB2 bulkload gives error with last column being character

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.