capture rejects from oracle enterprise stage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
jreddy
Premium Member
Premium Member
Posts: 202
Joined: Tue Feb 03, 2004 5:09 pm

capture rejects from oracle enterprise stage

Post by jreddy »

I have a simple parallel job which has a source oracle stage, a transformer and a target oracle stage (uses the upsert method). I want to be able to capture rejected records that dont get inserted into the target table due to violation of either referential integrity constraints or check constraints.

I tried to put a stream link output from transformer to a reject file and checked it as 'OTHERWISE' in the constraints.. didnt work. Then i tried setting the property 'Output Reject Records' to TRUE in the target oracle stage and dragged a reject link from it to a reject file.

The only rejects that are directed to the reject file are primary key violations. Not null violations will not let the job compile in the first place, but somehow the target operator fails, giving a fatal error at the first record to violate a referential integrity or check constraint. Since they are valid rejects and i dont want to carry them forward to the target tables, i would just like them to be written to a reject table/file and continue with the job, (like in a server job) and not abort the job with a status FAILED. I noticed that it wont even write the initial records that seemed to satisfy the constraints if it aborts due to further rejects, even if i give a commit frequency of 1.

I would like to learn from any of you who have implemented any kind of error trapping or handling mechanisms in such load jobs using oracle enterprise stage.

thanks in advance
fabianorb
Participant
Posts: 31
Joined: Fri Mar 05, 2004 12:00 pm

Post by fabianorb »

You can use the transformer to filter only the rigth rows.
In constraints, you have to set up all the conditions (valite key, colunm is not null...)
In the transformer you put 2 links output. The first link is the correctely row, and the second one is no correctely row.

I think it can help you.

Fabiano
jreddy
Premium Member
Premium Member
Posts: 202
Joined: Tue Feb 03, 2004 5:09 pm

Post by jreddy »

Assuming i can use a derivation to check if the column value satisfies check constraints.. (which is impractical, since i dont want to go to target table definition, see what are valid values and create derivations for all columns, which are about 10-20 minimum in each table)..
how do u think i can write a constraint to check the referential integrity..?
Post Reply