A savepoint is a mechanism of undoing work done by the DBMS when a database request fails. Savepoints make non-atomic database requests behave atomically. If an error occurs during execution, the savepoint can be used to undo changes made by the transaction between the time the savepoint was started and the time the savepoint rollback is requested.
A savepoint is similar to a compound SQL statement. It allows you to group several SQL statements into a single executable block. Before the first sub-statement of the block is executed, a savepoint request to start a savepoint block is required. If any of the sub-statements end in an error, only that sub-statement will be rolled back. This provides more granularity than a compound SQL statement, in which a single error causes the entire block to end in an error and rolls back the entire compound SQL statement. At the end of a savepoint block of statements, you can either release the savepoint, or rollback to the savepoint.
The following SQL statements enable you to create and control savepoints:
SAVEPOINT savepoint1 ON ROLLBACK RETAIN CURSORS
RELEASE SAVEPOINT savepoint1
ROLLBACK TO SAVEPOINT
For the complete syntax of the SAVEPOINT, RELEASE SAVEPOINT, and ROLLBACK TO SAVEPOINT statements, refer to the SQL Reference.
DB2 Universal Database places the following restrictions on your use of savepoints in applications:
DB2 enables you to include DDL statements within a savepoint. If the application successfully releases a savepoint that executes DDL statements, the application can continue to use the SQL objects created by the DDL. However, if the application issues a ROLLBACK TO SAVEPOINT statement for a savepoint that executes DDL statements, DB2 marks any cursors that depend on the effects of those DDL statements as invalid.
In the following example, the application attempts to fetch from three previously opened cursors after issuing a ROLLBACK TO SAVEPOINT statement:
SAVEPOINT savepoint_name;
PREPARE s1 FROM 'SELECT FROM t1';
--issue DDL statement for t1
ALTER TABLE t1 ADD COLUMN...
PREPARE s2 FROM 'SELECT FROM t2';
--issue DDL statement for t3
ALTER TABLE t3 ADD COLUMN...
PREPARE s3 FROM 'SELECT FROM t3';
OPEN c1 USING s1;
OPEN c2 USING s2;
OPEN c3 USING s3;
ROLLBACK TO SAVEPOINT
FETCH c1; --invalid (SQLCODE -910)
FETCH c2; --successful
FETCH c3; --invalid (SQLCODE -910)
At the ROLLBACK TO SAVEPOINT statement, DB2 marks cursors "c1" and "c3" as invalid because the SQL objects on which they depend have been manipulated by DDL statements within the savepoint. However, a FETCH using cursor "c2" from the example is successful after the ROLLBACK TO SAVEPOINT statement.
You can issue a CLOSE statement to close invalid cursors. If you issue a FETCH against an invalid cursor, DB2 returns SQLCODE -910. If you issue an OPEN statement against an invalid cursor, DB2 returns SQLCODE -502. If you issue an UPDATE or DELETE WHERE CURRENT OF statement against an invalid cursor, DB2 returns SQLCODE -910.
Within savepoints, DB2 treats tables with the NOT LOGGED INITIALLY property and temporary tables as follows:
To improve the performance of DB2 applications, you can use buffered inserts in your applications by precompiling or binding with the INSERT BUF option. If your application takes advantage of both buffered inserts and savepoints, DB2 flushes the buffer before executing SAVEPOINT, RELEASE SAVEPOINT, OR ROLLBACK TO SAVEPOINT statements.
For more information on using buffered inserts in an application, see Using Buffered Inserts. For more information on precompiling and binding applications, refer to the Command Reference.
If your application uses savepoints, consider preventing cursor clocking by precompiling or binding the application with the precompile option BLOCKING NO. While blocking cursors can improve the performance of your application by pre-fetching multiple rows, the data returned by an application that uses savepoints and blocking cursors may not reflect data that has been committed to the database.
If you do not precompile the application using BLOCKING NO, and your application issues a FETCH statement after a ROLLBACK TO SAVEPOINT has occurred, the FETCH statement may retrieve deleted data. For example, assume that the application containing the following SQL is precompiled without the BLOCKING NO option:
CREATE TABLE t1(c1 INTEGER);
DECLARE CURSOR c1 AS 'SELECT c1 FROM t1 ORDER BY c1';
INSERT INTO t1 VALUES (1);
SAVEPOINT showFetchDelete;
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
OPEN CURSOR c1;
FETCH c1; --get first value and cursor block
ALTER TABLE t1... --add constraint
ROLLBACK TO SAVEPOINT;
FETCH c1; --retrieves second value from cursor block
When your application issues the first FETCH on table "t1", the DB2 server sends a block of column values (1, 2 and 3) to the client application. These column values are stored locally by the client. When your application issues the ROLLBACK TO SAVEPOINT SQL statement, column values '2' and '3' are deleted from the table. After the ROLLBACK TO SAVEPOINT statement, the next FETCH from the table returns column value '2' even though that value no longer exists in the table. The application receives this value because it takes advantage of the cursor blocking option to improve performance and accesses the data that it has stored locally.
For more information on precompiling and binding applications, refer to the Command Reference.
If there are any active savepoints in an application when an XA compliant transaction manager issues an XA_END request, DB2 issues a RELEASE SAVEPOINT statement.