Page 1 of 1
How to capture Db2 datbase rejected records
Posted: Tue Dec 14, 2010 2:08 pm
by svhari76
Hi
I am new to datastage and i am trying to do a datastage job in which there can be rejects from Db2 database if i try to insert the same key records.
SeqFile -->Transformer -->Db2 stage
---------------Rejected(from transformer)
in my transformer i don't have any thing defined for the constraint, so DS thinks that all the records are success all the time, but in reality there are some rejected records from Db2. How can i get these records and wriite to a file.
Any help would be greatly appreciated.
Posted: Tue Dec 14, 2010 3:34 pm
by ray.wurlod
Mark the second output link from the Transformer stage as "Rejects". This is a check box in the constraints editor. Leave the constraint expression for this link blank. The mere fact that it's marked as "Rejects" will cause it to capture database rejects from the first output link.
You also have access to stage variables from the first output link, such as DBMSCODE and LASTERR, that can aid diagnosing the cause of any rejected rows.
Posted: Tue Dec 14, 2010 4:01 pm
by svhari76
It does make no difference...job is executing okay, but the no rejected records were in rejected file.The log shows that the records were rejected due to duplicate keys.
Posted: Tue Dec 14, 2010 4:04 pm
by chulett
Check your link ordering, the Reject link must be after the main target link.
Posted: Tue Dec 14, 2010 4:14 pm
by svhari76
Nope, Databse loading is first and then rejected link(order 2).
Posted: Tue Dec 14, 2010 5:22 pm
by ray.wurlod
Does the word "Reject" appear in the constraints area of that link within the Transformer stage?
Posted: Tue Dec 14, 2010 8:31 pm
by svhari76
Yes, it does.
Posted: Wed Dec 15, 2010 3:14 am
by ray.wurlod
What kind of DB2 stage, and what write method are you using? A reject link from the Transformer stage is the way it's supposed to work.
Posted: Wed Dec 15, 2010 9:24 am
by svhari76
Hi
I am using DB2 API Stage and 'insert rows without clearing' action.
Just wanted to make sure i am briefing the procedure what i have done.
Ihave sequencial file,transformer, Db2 API and Reject file from tranformer stage.Read records from File(separated by pipe) and in the transformer i don't have any constraints defined for DB2 stage and [Rejected] for Rejected file stage.
Posted: Wed Dec 15, 2010 2:31 pm
by svhari76
Ok, Finally i should be able solve the Db2 rejects capture issue.
The Arraysize should be 1, it has 50 before and now works fine.
Thanks all for taking time to respond to questions.
Posted: Wed Dec 15, 2010 2:59 pm
by chulett
Ah... it does need to be 1 to capture them
reliably, however it seems like it should still have 'worked' after a fashion when it is set higher. From what I recall, Oracle would report the right number but put the wrong record in the reject file. It also depended on if your total row count exceeded the array size or not, IIRC.
There should be a post in here from me on the subject from five years or so ago.
