Capturing rejects in px jobs

[jreddy]

hey guys,
back with another question..

i have a source stage (oracle), a transformer and 3 target oracle stages. My requirement is that i have to capture rejects of first target ONLY into second oracle table. I tried using otherwise in transformer (with the link ordering as first target, second target reject table, third target),but it didnt seem to work, coz once the same row is accepted into another target table, it will not be considered a reject anymore. (surprisingly, according to manuals the link marked as 'otherwise' should capture rejects of output links ABOVE it in link order, but it doesnt in my case.

Also, i tried to use linkname.REJECTED in the constraint for the second reject table link, but it seems this is not available in parallel jobs, though the manual says i can use it. I am getting a syntax error when i use it..

anybody who did this, please help me out. Thanks.

P.S: Now most might wonder why i am not using the direct reject link of the target table and why i am using another target oracle stage to capture rejects..well, my objective here is to just capture the row_id's, job name, table name details into this reject table, so i can just lookup the row_id in source to troubleshoot the error.

[raju_chvr]

and also this issue shld posted in Parallel Extender forum.

[Teej]

In summary, you have:

Code: Select all

Oracle ->  Transformer -> 3 Oracle

In Transformer, you have a constraint field for the first Oracle table.

You want "rejects" from that constraint to go to the second Oracle table.

Then you have a second constraint to the third Oracle table.

* * *

This will not work for PX as it does for Server. PX consider "reject" as an universal "default" as in a switch statement. In fact, Transform stage uses the Switch Stage for the constraint behavior. This will make sense if you know C/C++.

What you really want is:

Code: Select all

oracle -> copy stage -> Transform -> Oracle
               \
                ------> Transform -> 2 Oracle

In fact, if you are not doing any transformation other than the constraint, the following should work (you will need the reject link for the 2nd switch to be used for the 'reject'):

Code: Select all

oracle -> copy stage -> Switch -> Oracle
               \
                ------> Switch -> 2 Oracle

Also, the gospel is the OSH documentation. I have spotted so many errors (including one noted yesterday on the forum) for PX documentation.

-T.J.

[jreddy]

actually, i have the correct the summary that you have assumed

In Transformer, you have a constraint field for the first Oracle table.
You want "rejects" from that constraint to go to the second Oracle table.
Then you have a second constraint to the third Oracle table.

I do not have a specific constraint on first Oracle table, I just want anything that was rejected from it due to any error to be captured to second oracle table. Then there is a totally different third table that i have no constraints on and have nothing to do with its rejects..

I sort of worked around another solution meanwhile. I did this

[code]source -> Transformer -> Third oracle table
-> First oracle table -reject link -> Transformer -> Second oracle table.[/code]

So, now i made sure that only rejects go into second table, and all the extra information that i need into reject table, like job name, job start time etc i derived in transformer and sent it to second table...

kind of work around maybe.. but it works.. thats all that matters.. but thanks so much for your valuable suggstions.. helps me learn new stuff too..

[/code]

[Teej]

Actually, that would be exactly what I would do now that I understand what you were referring to as rejects.

Good job!

-T.J.

[GIDs]

Hi,

Was browsing through some posts, .... there is a simple solution, if you have user-defined SQLs

1. Encapsulate your SQL in a PL/SQL block (this will not affect any partitioning as the rejects will be trapped for every array of records)
2. In the EXCEPTION section of this block, insert the rejects into a separate REJECT table
3. So, your code may look like...

begin
INSERT INTO test (col1, col2) VALUES (ORCHESTRATE.col1,
ORCHESTRATE.col2);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN RAISE; -- this is to capture UPDATEs,
-- do not treat them as rejects
WHEN OTHERS THEN -- all other cases, reject
BEGIN
INSERT INTO REJECTS (JOB_ID, REJECT_DATA);
VALUES (#pJobID#, some_text);
END;
END;