IBM DB2 9.7 Advanced Application Developer Cookbook

Rolling back to a savepoint

As we discussed in the previous recipe, the motivation behind creating savepoints is to divide a transaction into sub-tasks. It allows us to partially roll back a transaction, saving total execution time. Once a transaction is rolled back to a savepoint, the savepoint still exists but all nested savepoints no longer exist. In this recipe, we will see how to use savepoints in a ROLLBACK statement.

How to do it...

Use the ROLLBACK TO SAVEPOINT statement to roll back to a savepoint. To understand better, we will create a test table and use multiple savepoints in a single transaction, and observe the effect of rolling back to different savepoints.

In this example, we will create a table and insert a few records in the table. ...