Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 683
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 Question related to Data dictionary f... rexx77 SYNCSORT 3 Thu Aug 31, 2017 7:23 am
No new posts Extract Data till prev hour balaji81_k DB2 16 Fri Aug 25, 2017 9:25 pm
No new posts FTP data transfer from PC to Mainfram... AJAYREDDY All Other Mainframe Topics 8 Wed Aug 23, 2017 9:59 pm
No new posts Unable to copy data from spool in a d... srikant314 JCL & VSAM 8 Fri Jul 28, 2017 9:10 pm
No new posts Paasing symbolic parameter to instrea... Robert Sample JCL & VSAM 2 Thu Jul 27, 2017 1:22 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us