transaction grouping

veygur · Post by **veygur** » Tue Apr 03, 2007 10:44 am

Hi,

1)What is the exact explanation of on skip at transaction grouping? According to what can we specify continue or rollback?

2)When we enable transaction grouping, if there is an error will datastage rollback all of the transactions or will it commit the ones before the error? What we want is to rollback all of them. How may we do that?

Thanks...

ray.wurlod · Post by **ray.wurlod** » Tue Apr 03, 2007 10:19 pm

Welcome aboard. :D

Please describe exactly where you find the term "on skip".

veygur · Post by **veygur** » Wed Apr 04, 2007 5:50 am

Oracle Stage->Input->Transaction Handling at this tab if we enable transaction grouping we see on skip and on fail columns. There are two options for both of them: contiue or rollback.

chulett · Post by **chulett** » Wed Apr 04, 2007 6:12 am

If you want to rollback all work when an error occurs, you cannot use Transaction Grouping. Use it when you have multiple related links and you need each 'group' of links to either all be individually committed or rolled back. The Transaction Size is forced to 1 when this option is enabled, which should be a big clue as to how it works - each group is handled as a separate transaction.

When on that tab of the OCI stage, click the Help button. See if the explanation there makes sense. On Skip defines your success action, On Fail your error action. Very seldom would you need to change them from their defaults.

For your 'all or nothing' load, forget about this option. Simply use a Transaction Size of 0 and ensure that the job aborts if there is any problem.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 04, 2007 7:51 am

Click on Help when you're in that page (Transaction Grouping). You can then click on the controls to get individual help about them.

I am using an Oracle OCI (ORAOCI9) stage, and do not see these controls. Can you be more specific about what kind of stage you are using? The stage type appears in the title bar of the stage when it is open.

chulett · Post by **chulett** » Wed Apr 04, 2007 8:22 am

Ray - you need to have multiple links from the same Transformer to a single OCI stage before the option becomes available on the Transaction tab of that OCI stage. And you need to then enable it before the options being questioned shows up.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 04, 2007 6:42 pm

The multiple input links I had - transaction grouping on one link is silly.
Enabling it I missed. But I can not see the control described in the help (which, incidentally, contains a description of what "on skip" does). The only control on the Transaction Grouping page is Rows per transaction - the remainder is a big grey area.

chulett · Post by **chulett** » Wed Apr 04, 2007 7:27 pm

Huh - if you have multiple links from one transformer to one OCI stage, then the Enable transaction grouping option will appear. When you actually enable / check it, the 'big grey area' will fill with the Input Link Names with the On Skip and On Fail settings per link.

Is that not working for you?

ray.wurlod · Post by **ray.wurlod** » Wed Apr 04, 2007 9:38 pm

It's not working for me in 7.5x2. Did it on razor, just to check. Didn't work there (7.5.1A) either.

chulett · Post by **chulett** » Wed Apr 04, 2007 10:43 pm

Not sure what's going on. Check razor, project chulett, there is a single job in the Test category. Nothing except a transformer, two links and an OCI stage but it shows the Transaction Grouping information if you check the Transaction tab.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 05, 2007 7:28 am

Solved. See the other job. You need an active stage driving the OCI stage to cause the transaction grouping stuff to happen.

chulett · Post by **chulett** » Thu Apr 05, 2007 7:48 am

Right. As I said - "you need to have multiple links from the same Transformer to a single OCI stage before the option becomes available on the Transaction tab of that OCI stage".

And from the online help:

"All the input links in the transaction group must originate from the same Transformer stage."

:wink:

ray.wurlod · Post by **ray.wurlod** » Thu Apr 05, 2007 7:54 am

All the other active stages in server jobs have only one output link.