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. Then we will see how savepoints work:

1. 1. Create a test table:

   CREATE TABLE TEST_SVPT (
   EMPNO INTEGER,
   EMPNAME VARCHAR(20),
   MGRNO INTEGER);
   
   

2. 2. By default, the DB2 prompt is enabled for auto commit. For savepoints, we need to maintain an open transaction...