DSXchange

Hi,

I am using tansaction grouping (unit of work) and would like to capture errors using a reject link.

In my job I have one transformer and one Oracle stage, and there are two links from transformer to Oracle stage with transaction grouping enabled. If there is a reject on either of the links I would like to capture the reject through a reject link. But right now it is only capturing rejects on the first link, but not on the second link.

Is this the default behaviour. Does the first link controls the whole transaction group? Is there a solution where I can capture rejects on either of the links.

Your advise is highly appreciated.

Thanks
Sridhar

Did you try

LK1.REJECTED OR LK2.REJECTED if LK1 and LK2 are your output links.

Just an idea, I am not sure whether this will work or not. But surely worth a try as OR is perfectly acceptable in Constraints.

Just another design suggestion, I would highly recommend that you create 2 jobs instead of one to write/update on ORACLE instead of using
2 output links to ORACLE stage. There is nothing wrong in it, but it gives you again modularity which many people here stress including me.

Something wrong in the first table load should not effect the second table load if the second table is not having any problems with it. The ideal design would be doing it in seperate jobs unless you are loading millions of rows for which anyways you should be using ORABULK.

Assumptions
1. This is not a batch job and maybe two jobs isn't appropriate.
2. You want transaction control for some foreign key constraint reason.

I prefer to use REJECTEDCODE because REJECTED includes the constraint. You can easily interrogate the REJECTEDCODE from each of the output links in a third link constraint and proceed accordingly down the third link if either of the other output link REJECTEDCODE's are non-zero. A non-zero would indicate the write to Oracle failed. If this isn't confusing enough check out the Designer help. It's a doozy!

raju_chvr,

Thanks a lot for sharing your ideas.
I tried LK1.REJECTED OR LK2.REJECTED.
Like I articulated in my original message only a failure on LK1 (first link) is resulting in reject, DS is not considering rejects on LK2. Though it generates a warning in the log.

As for designing two jobs, these two tables are interrelated. Bascially its a data table and an audit table. Whenever I update a record in data table, I need to insert an audit record. So its a transaction.

trobinson,

1. This is not a batch job and maybe two jobs isn't appropriate.
Your assumption is right on the money.
2. You want transaction control for some foreign key constraint reason.
Basically I want to audit, hence one transaction.

As for using REJECTEDCODE, REJECTED - I tried various scenarios. But they all work if the reject is on the first link. If there is a reject on the second link the reject link is not even traversed (tested in debug mode). Basically everything boils down to the first link controlling the transaction.

Thanks a lot again for helping me. Please feel free to share your ideas.

One quick fix if you have time limitations is: to use another Reject link to Sequential file and then in after-job subroutine merge them into one as they have same structure.

This is not solution for the error. It is just a quick fix. Meanwhile we will wait for other inputs on this...

In the transaction Handling tab, I assume you have rollback for On Fail?Do not have the reject checkbox checked? If, so I would uncheck it, in fact I would never use it.

If the first link fails and transaction handling is enabled, why would it bother with the second link? Logically, you would never expect a REJECTEDCODE on the second link if the first had one too. However, you need to code both anyway to cover the case where the first succeeds and the second fails.