ROLLBACK for previous table updates

ssowmya · New User Joined: 10 Oct 2006 Posts: 40

Hi

We need to code a program that will be updating/inerting/deleting into tables T1,T2,T3,T4 and T5. When there is failure in one of the operations, all the previous updates need to be rolled back.

For example, consider the below scenario

New row is inserted into table T1 which is succesfull
A row is deleted from table T2 which is succesfull
When I try to insert record into table T3, I get an error.

In the above scenario, the updates to T1 and T2 need to be backed out. Could anyone please let me know if there is a simple solution to this problem.

Thanks
Sowmya

ashwinreddy · Posted: Tue Apr 24, 2007 12:10 pm

Hi,

Its simple use a flag as WI-Rollback, and set this flag if any one of the T1,T2 or T3 sqlcode not equal to zero.

And after the three table processing check as

If WI-Rollback

Rollback db2 command

Cheers
Ashwin

William Thompson · Posted: Tue Apr 24, 2007 12:21 pm

Do you understand the concept of LUW? All inter-related operations are considered to a Logical Unit of Work. Once all parts of the LUW are successfully completed, commit the LUW to make the updates permanent. If there is a failure of any of the parts of the LUW, issue a rollback and all the other parts of the LUW will be reversed.