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.
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. Create a test table:
CREATE TABLE TEST_SVPT ( EMPNO INTEGER, EMPNAME VARCHAR(20), MGRNO INTEGER);2. By default, the DB2 prompt is enabled for auto commit. For savepoints, we need to maintain an open transaction...