SAVEPOINT statement

With the SAVEPOINT statement it’s possible to set savepoints in a transaction. In the code you can ROLLBACK to this point and all statements which where executed since the savepoint were set will be rollbacked. Multiple savepoints can be defined. If a savepoint is no longer needed it’s possible to RELEASE this savepoint.

Setting savepoints is not possible in an atomic procedure.

Syntax:

 SAVEPOINT <savepointname> [UNIQUE] ON ROLLBACK RETAIN CURSORS

Example:

 create procedure savepoints
 begin
   savepoint savepoint1 on rollback retain cursors;
   insert into test values ('Savepoint1');

   savepoint savepoint2 on rollback retain cursors;
   insert into test values ('Savepoint2');

   savepoint savepoint3 on rollback retain cursors;
   insert into test values ('Savepoint3');

   rollback to savepoint savepoint2;
end!

Test of this example:

 call savepoints;

Result:

Just the string ‚Savepoint1‘ was inserted into the table.

 


Link to the DB2 Information Center: DB2 9.7 DB2 10.1 DB2 10.5