Transaction Grouping

aravindk · Post by **aravindk** » Wed Apr 14, 2004 1:08 pm

Hello all,

I am using Transaction Grouping in one of my jobs. I have one OCI8 stage with three input links coming into it. All three are insert new rows or update existing rows. If link2 or link3 fails, I want all *previous* links to be rolled back also. How can I achieve this?

Transaction grouping is enabled in this case. But when link2 fails, it doesnt back out link1.

TIA,

chulett · Post by **chulett** » Thu Apr 15, 2004 5:26 am

Since no-one has replied yet...

I'm assuming you have 'rollback' selected as the failure action, correct? In that case what you describe you want to happen should be what is happening - with one important clarification, depending on what you mean by 'previous' links.

Since Transaction Grouping requires a commit level of 1, then it cannot roll back transactions for any previous rows when something fails in the current row. However, when processing the current 'set' of rows, if Link 2 or Link 3 fail, then yes Link 1 or Link 1 and Link 2 should be rolled back. If that is not happening, I'd open a case with Ascential and see if there is a known bug in your version of DataStage or OCI.

aravindk · Post by **aravindk** » Thu Apr 15, 2004 7:50 am

Thanks Craig,

I have three links and all three links have 'rollback' as 'on Fail' action and
'Continue' as 'On Skip' action.

I dont want the previous rows (that are already committed to the database) to be rolledback. I only want the current row to be rolled back.
Just exactly as you mentioned, If Link2 fails, Link1 should rollback. If link3 fails, link1 & link2 should rollback.

Anybody else, seen this problem before?

trobinson · Post by **trobinson** » Thu Apr 15, 2004 9:41 am

What is the failure that link2 is reporting?

aravindk · Post by **aravindk** » Thu Apr 15, 2004 10:20 am

link2 fails due to database constraints. e.g. NULLS in non-null fields, duplicate keys where there is a unique constraint etc.

Thanks,