How to Ouput Reject Records from DB2 Connector

hsahay · Post by **hsahay** » Sun Jun 03, 2012 10:00 pm

I went through resolved topic How to Ouput Reject Records from DB2 Connector to Flat File.

I could be missing something but, I'm unable captute actual records except error code and/or error text either dataset ot file. I have RCP enabled in my job and in administartor.

Problem:
1. I have 10 records coming from source, 5 of them got inserted and 5 of them rejected, and I could see the count as 5 on reject link as per performance statistics, but unable to see the actual records in the dataset ot file. Also I don't see the 5 record count in the performance statistics in the link coming to db2 connector, although records got inserted.

2. Aslo 5 inserted records commit to db even though property in db2 connector set to autocommit off.

Any input on this greatly appreciated. Thanks.

ray.wurlod · Post by **ray.wurlod** » Sun Jun 03, 2012 10:39 pm

Try capturing the reject records into a sequential file, and have a reject link on that stage too (to a separate text file).

Hitting the end of your data will cause DataStage to generate a COMMIT irrespective of the number of records sent to the database.

hsahay · Post by **hsahay** » Mon Jun 04, 2012 9:17 am

Hi Ray,

Thanks for the reply.

I tried having a reject link to the sequential file I'm trying to capture the reject records in to. I was unable the see the records except the error code which I have checked in the db2 connector stage. If I don't check either error code or error text, my view data is greyed out.
I want records with the data and the error code in to text file or data set. Any way would work for me.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 04, 2012 1:51 pm

And do you have a reject link from the Sequential File stage also? You need to be able to handle the case that the data don't match the metadata.

hsahay · Post by **hsahay** » Mon Jun 04, 2012 5:42 pm

Yes.

Here is my design..

Source_DataSet-->Copy Stage-->DB2 Connector----RejectLink--->Sequential File---Reject Link---->Sequential File.

Thanks.

chandra.shekhar@tcs.com · Tue Jun 05, 2012 8:06 am

In the 'Reject" tab in the DB2 connector stage,
tick mark the all 4 options i.e. SQL error, Row not updated, ERRORCODE and ERRORTEXT. Then use a very large number like 100000000 in "Abort after (rows)" option.
I guess that'll be suffice.