DB2 Load - how to avoid data rejects if quote is in data

RSR2011 · Posted: Wed Mar 02, 2011 2:23 pm

Hi,
I have written a DB2 load utility that loads a tab delimited file ftpd from SQL server. For one of the columns defined as VARCHAR in DB2, we receive data with a quotation mark in the beginning (") without an ending quote. The load utility throws a delimiter mismatch error and rejects this record.
I have following two questions:
1. Is there an option in DB2 Load to avoid the default CHARDEL value (")? I saw that DB2 unload utility has an option 'NOCHARDEL' but an option in LOAD.
2. Also is there a way to make the utility skip the problematic column and load rest of the columns instead of skipping the entire row?

daveporcelan · Posted: Wed Mar 02, 2011 6:07 pm

One option would be to 'scrub' the data before doing the load.

You could use Fileaid or a Sort product to do this.

The purpose of the DB2 load utility is to just do that LOAD.

It has every expectation that the data given to it is clean.

This would be my approach.

Ignore me if you will.

GuyC · Posted: Wed Mar 02, 2011 10:48 pm

why don't you look in the manual for the load statement.
You would have found
LOAD ... FORMAT DELIMITED {[CHARDEL'"' |CHARDEL chardel]}

so maybe try CHARDEL '§' or some other strange character that does not appear in your input

dbzTHEdinosauer · Posted: Thu Mar 03, 2011 12:28 pm

since you are getting garbage from an outside source,
you could try running the file thru DFSORT and inserting a '"' where necessary.