IBM DB2 9.7 Advanced Application Developer Cookbook

Creating a savepoint

The savepoints are used to divide a big transaction into smaller sub-transactions. It gives the developer the ability to do partial rollbacks, saving re-execution time. Multiple savepoints can be created in a transaction and we can roll back to any savepoint without affecting the processing done before the savepoint. In this recipe, we will see how to create a savepoint.

How to do it...

We can use a SAVEPOINT statement to create a savepoint. This statement can be embedded in any host language within a transaction:

SAVEPOINT svpnt_temp
UNIQUE
ON ROLLBACK RETAIN CURSORS
ON ROLLBACK RETAIN LOCKS

How it works...

• UNIQUE: If this clause is specified, then we cannot create another savepoint with the same name until the savepoint is ...