How to capture Db2 datbase rejected records

svhari76 · Post by **svhari76** » Tue Dec 14, 2010 2:08 pm

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.

ray.wurlod · Post by **ray.wurlod** » Tue Dec 14, 2010 3:34 pm

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.

svhari76 · Post by **svhari76** » Tue Dec 14, 2010 4:01 pm

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.

chulett · Post by **chulett** » Tue Dec 14, 2010 4:04 pm

Check your link ordering, the Reject link must be after the main target link.

svhari76 · Post by **svhari76** » Tue Dec 14, 2010 4:14 pm

Nope, Databse loading is first and then rejected link(order 2).

ray.wurlod · Post by **ray.wurlod** » Tue Dec 14, 2010 5:22 pm

Does the word "Reject" appear in the constraints area of that link within the Transformer stage?

svhari76 · Post by **svhari76** » Tue Dec 14, 2010 8:31 pm

Yes, it does.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 15, 2010 3:14 am

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.

svhari76 · Post by **svhari76** » Wed Dec 15, 2010 9:24 am

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.

svhari76 · Post by **svhari76** » Wed Dec 15, 2010 2:31 pm

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.

chulett · Post by **chulett** » Wed Dec 15, 2010 2:59 pm

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.