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 Trimming option - highlight trimmed data during loading

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

New User


Joined: 06 Sep 2010
Posts: 19
Location: Bangalore

PostPosted: Wed Apr 25, 2012 4:27 am    Post subject: DB2 Trimming option - highlight trimmed data during loading
Reply with quote

Hi

I have CSV data coming from an external system which mainly puts quotes for those data which has comma(,) within it mainly to mask the commas as separate data columns.

For e.g.
Data has a layout as below

Emp id, Name , address Line 1 , Address Line 2 , Country, Pincode

Sample data may contain

123123, Abc, " Address Line 1, Street1",Street2,Bangalore, 575007
1234,sdcdfer,Address 1,"Street 2, Karnataka",Mangalore, 534

So quotes occur dynamically based on occurrence of comma as data and not as a delimiter. I am using DB2 utility to load the data on a table. But during loading few of the data are getting rejected due to delimiter mismatch or length exceeding. So I thought to use 'Trimming' option during loading. But I want to know is there any option to identify the data which has been trimmed during DB2 table loading. As down the line I need to separate the erroneous data from correct ones.

Can you please help?

Thanks,
Back to top
View user's profile Send private message

saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Wed Apr 25, 2012 8:34 am    Post subject:
Reply with quote

In order to load the data which are enclosed with codes please use below mentioned load card

Sample Load Card:

Code:
 LOAD DATA                                                   
          FORMAT DELIMITED COLDEL ',' CHARDEL '"'                 
          INTO TABLE TABLE_NAME                                               
              (EMP_ID DECIMAL,
               EMP_NAME CHAR,
               EMP_ADDR1   CHAR,
               EMP_ADDR2   CHAR,
               CITY CHAR,
               ZIP_CODE INTEGER )                                 

//SYSREC DD *
123123, Abc, " Address Line 1, Street1",Street2,Bangalore, 575007
1234,sdcdfer,Address 1,"Street 2, Karnataka",Mangalore, 534
/*
Back to top
View user's profile Send private message
vina2010

New User


Joined: 06 Sep 2010
Posts: 19
Location: Bangalore

PostPosted: Wed Apr 25, 2012 3:01 pm    Post subject:
Reply with quote

As mentiioned earlier, the same load utility as below has been used, but I want to know, is there any option which can help me to find which data are trimmed and which data are loaded as it is?

LOAD DATA
FORMAT DELIMITED COLDEL ',' CHARDEL '"' DECPT '.'
INTO TABLE TABLE_NAME
(EMP_ID CHAR STRIP BOTH TRUNCATE,
EMP_NAME CHAR STRIP BOTH TRUNCATE,
EMP_ADDR1 CHAR STRIP BOTH TRUNCATE,
EMP_ADDR2 CHAR STRIP BOTH TRUNCATE,
CITY CHAR STRIP BOTH TRUNCATE,
ZIP_CODE CHAR STRIP BOTH TRUNCATE )
Back to top
View user's profile Send private message
senthilnathanj

New User


Joined: 31 Jul 2007
Posts: 47
Location: chennai

PostPosted: Wed Apr 25, 2012 3:57 pm    Post subject: Reply to: DB2 Trimming option - highlight trimmed data durin
Reply with quote

Hi,
When I saw first post, EMP_ID is numeric and last Post EMP_ID is char data type. which one is correct?
Back to top
View user's profile Send private message
vina2010

New User


Joined: 06 Sep 2010
Posts: 19
Location: Bangalore

PostPosted: Wed Apr 25, 2012 4:29 pm    Post subject:
Reply with quote

the data type is not the problem here, basically the table is a temorary one, and all the fields are CHAR type.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Wed Apr 25, 2012 5:31 pm    Post subject: Reply to: DB2 Trimming option - highlight trimmed data durin
Reply with quote

If no other way, compare the data after it is in the database to the original source.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Apr 25, 2012 8:46 pm    Post subject:
Reply with quote

if you load without truncate , but with DISCARDDN ddname
I believe the ddname will contain all rows that would have been truncated.
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 Paasing symbolic parameter to instrea... Anirudh Anand JCL & VSAM 1 Thu Jul 27, 2017 1:22 pm
No new posts Passing data from REXX to C programme... chong.zhou CLIST & REXX 3 Wed Jul 26, 2017 9:11 pm
No new posts copying data without knowing location arunsoods DFSORT/ICETOOL 11 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 19 Thu Jul 13, 2017 2:11 pm


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