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
capture rejects from oracle enterprise stage
Moderators: chulett, rschirm, roy
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
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
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..?
how do u think i can write a constraint to check the referential integrity..?