ssowmya New User Joined: 10 Oct 2006 Posts: 40
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.
ashwinreddy Active User Joined: 16 Sep 2004 Posts: 106 Location: Hyderabad
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
Rollback db2 command
William Thompson Global Moderator Joined: 18 Nov 2006 Posts: 3158 Location: Tucson AZ
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.
All times are GMT + 6 Hours