DSXchange

I have searched and read all articles related to this, even the Leakage article and still I can't find an example on how to capture rejects due to duplicate entries.

As I am still learning the product, I can't find any good examples on how to set this rejectlogic up. I create a test job where I read from one DB2 Table and try to insert it into another that contain the same records. This will cause a duplicate. I used the tranformer stage but I am not sure what I need to do. Is there a constraint I must add or something. I am not sure if I am suppose to capture the SQLSTATE = 23505... etc. I agrre that there is alot of posts on this but none really clearly state how to accomplish this problem.

Could someone please shed some more light on this. Maybe it will decrease the amount of posts that have been written on the subject.

Thanks.

In the transformer, specify the constraint as OTHERWISE for your reject link. See if that works. I am not sure if DB2 API supports reject links. The enterprise stage does.

In parallel jobs it's not done in the Transformer stage. DB2 rejects are captured by a reject link on the DB2 stage.

However, better practice is to detect the duplicates upstream. The easiest way is a Sort stage that generates a Key Change column. The second easiest way is to use stage variables in a Transformer stage.

Hi Raftsman,

Even I tried to implement the same logic.. but it did not workout by using the SQLSTATE at the constraint.

As a workaround, I fetched the keys for the table into a Hashed file and then in the job determined whether it is an Update ( i.e already existing ) or an Insert.

HTH,

Regards.

Hashed files are not present in px jobs.

In a parallel job?!! Good trick!