db2 corrupts data while loading from a TXT file

[Metin Esat]

Hi,

I have to deal with the following problem to increase the integrity of
our data. While doing a load to a DB2 table from a TXT file if there
is a double quote before the data that needs to be parsed, load parser
ignores the specified coldel delimeter. For instance,

"óERKEKóS02 ó0123456óóóóANKARAóóóYAŞIYORóTÜRKİYEóTÜRKİYE

gets loaded in a single column while it should be parsed into several
columns because of the specified coldel ó. Here is the load script
that I use. This loads appx 7 million rows in the table and I think
atmost a couple of hundred rows have this problem.

LOAD FROM /data/TEST.CUST_V.TXT OF DEL
MODIFIED BY DATEFORMAT="YYYY-MM-DD"
TIMESTAMPFORMAT="YYYY-MM-DD HH:MM:SS.UUUUUU"
COLDELó
WARNINGCOUNT 1000
MESSAGES /data/logs/TEST.CUST_V.LOG
REPLACE INTO TEST.CUST
STATISTICS YES AND INDEXES ALL NONRECOVERABLE INDEXING MODE
AUTOSELECT@
THANKS ALOT IN ADVANCE!!
METIN

[Wolfgang Riedel]

Metin Esat wrote:

Hi,

I have to deal with the following problem to increase the integrity of
our data. While doing a load to a DB2 table from a TXT file if there
is a double quote before the data that needs to be parsed, load parser
ignores the specified coldel delimeter. For instance,

"óERKEKóS02 ó0123456óóóóANKARAóóóYAŞIYORóTÜRKİYEóTÜRKİYE

gets loaded in a single column while it should be parsed into several
columns because of the specified coldel ó. Here is the load script
that I use. This loads appx 7 million rows in the table and I think
atmost a couple of hundred rows have this problem.

LOAD FROM /data/TEST.CUST_V.TXT OF DEL
MODIFIED BY DATEFORMAT="YYYY-MM-DD"
TIMESTAMPFORMAT="YYYY-MM-DD HH:MM:SS.UUUUUU"
COLDELó
WARNINGCOUNT 1000
MESSAGES /data/logs/TEST.CUST_V.LOG
REPLACE INTO TEST.CUST
STATISTICS YES AND INDEXES ALL NONRECOVERABLE INDEXING MODE
AUTOSELECT@

THANKS ALOT IN ADVANCE!!
METIN

Hi Metin,

You have to use - modified by chardel s.th -,
if your data can contain '"'s.

hth,
Wolfgang