Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
RSR2011

New User


Joined: 02 Mar 2011
Posts: 1
Location: Chennai, India

PostPosted: Wed Mar 02, 2011 2:23 pm    Post subject: DB2 Load - how to avoid data rejects if quote is in data
Reply with quote

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?
Code:
 input row  "QTR RND WM11 BAM 12X13X4
Error message DSNU1143I   DSNURILD - RECORD 156058 WILL BE DISCARDED DUE TO DELIMITER MISMATCH FOR COLUMN NAME
Back to top
View user's profile Send private message

daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 668
Location: Pennsylvania

PostPosted: Wed Mar 02, 2011 6:07 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Mar 02, 2011 10:48 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Mar 03, 2011 12:28 pm    Post subject:
Reply with quote

since you are getting garbage from an outside source,
you could try running the file thru DFSORT and inserting a '"' where necessary.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts copying data without knowing location arunsoods DFSORT/ICETOOL 6 Thu Jul 20, 2017 1:03 pm
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts NDM syntax checking without actually ... GAPX1 All Other Mainframe Topics 0 Wed Jun 07, 2017 2:36 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us