DB2 savepoints: SAVEPOINT and ROLLBACK TO SAVEPOINT

What a savepoint is

The SAVEPOINT statement sets a named mark inside the current unit of recovery. Later, ROLLBACK TO SAVEPOINT undoes relational database changes made after that mark. The unit of recovery does not end. You can continue INSERT/UPDATE work and eventually COMMIT (keep everything that remains) or ROLLBACK (undo the whole UR).

Classic use: book a flight, then a hotel. If the hotel fails, roll back only to the savepoint before the hotel attempt, try the next date, and COMMIT when both succeed. Up to three dates without losing a successful earlier step you still want — or without committing a half-booking.

SAVEPOINT

1
2
3
4
5
6
SAVEPOINT START_AGAIN UNIQUE
  ON ROLLBACK RETAIN CURSORS;
 
SAVEPOINT A ON ROLLBACK RETAIN CURSORS;
SAVEPOINT B UNIQUE ON ROLLBACK RETAIN CURSORS
  ON ROLLBACK RETAIN LOCKS;

ON ROLLBACK RETAIN CURSORS is required in the z/OS syntax diagram. It means Db2 does not track those later cursors for automatic close when you roll back to this savepoint. The cursors can remain open, but if rollback moved the row they were on, a later positioned UPDATE or DELETE can fail. Plan to CLOSE and OPEN again after a rollback to savepoint if the cursor was active across the undone work.

ON ROLLBACK RETAIN LOCKS is optional. If you specify it, locks taken after the savepoint stay when you roll back to it. That can surprise concurrency: you undid the data change but still hold the lock. Omit RETAIN LOCKS when you want lock release to follow the undone work as closely as the product allows.

UNIQUE versus reusing a name

UNIQUE means this name is taken for the rest of the UR. A second SAVEPOINT B UNIQUE fails. Without UNIQUE you may issue SAVEPOINT A again. Reusing the name destroys only that savepoint. It is not the same as RELEASE SAVEPOINT A, which also drops every savepoint set after A.

1
2
3
4
5
SAVEPOINT A ON ROLLBACK RETAIN CURSORS;
-- work ...
SAVEPOINT B UNIQUE ON ROLLBACK RETAIN CURSORS;
-- work ...
SAVEPOINT A ON ROLLBACK RETAIN CURSORS;  -- reuse: old A is gone, B remains

Savepoint nesting

On Db2 for z/OS you can set any number of savepoints in one unit of recovery. They form a sequence, not a single-slot object. If you set A, then B, then C:

• ROLLBACK TO SAVEPOINT A undoes work after A and releases B and C. A itself stays active so you can roll back to A again.
• ROLLBACK TO SAVEPOINT (no name) uses the last active savepoint.
• RELEASE SAVEPOINT B releases B and C; A remains.

That is the nesting model to learn. Older Db2 LUW documentation said “savepoints cannot be nested” in a different product sense. Do not copy that restriction onto z/OS application design.

Name scope across CALL: if the application sets savepoint S and a stored procedure also sets S, ROLLBACK TO SAVEPOINT S inside the procedure uses the procedure’s more recent S. Design unique names (or UNIQUE) when caller and callee both use savepoints.

You cannot use savepoints in global transactions, triggers, user-defined functions, or stored procedures nested inside triggers or UDFs.

ROLLBACK TO SAVEPOINT

1
2
3
4
5
6
7
8
9
SAVEPOINT START_AGAIN UNIQUE ON ROLLBACK RETAIN CURSORS;
 
-- attempt 1: air + hotel for date 1
-- if hotel fails:
ROLLBACK TO SAVEPOINT START_AGAIN;
 
-- attempt 2: air + hotel for date 2
-- if both ok:
COMMIT;

TO SAVEPOINT says: do not end the UR; undo only after the named (or last) savepoint. Savepoints set after the target are released. The target remains.

A ROLLBACK with no TO SAVEPOINT is a full rollback: the UR ends, all savepoints in it are released, and (when Db2 is the only recoverable resource) the unit of work ends too.

In IMS and CICS, SQL ROLLBACK TO SAVEPOINT undoes Db2 changes only. VSAM, DL/I, MQ, and CICS resources in the same transaction are not rolled back by that SQL statement. Use EXEC CICS SYNCPOINT ROLLBACK or the IMS rollback call when the whole transaction must reverse.

RELEASE SAVEPOINT

1
2
RELEASE SAVEPOINT START_AGAIN;
RELEASE SAVEPOINT A;

RELEASE SAVEPOINT name drops that savepoint and every savepoint established after it. If you no longer need the mark before COMMIT, release it. Savepoints disappear anyway at the end of the unit of work, but holding them has a practical cost: you cannot use three-part names to reach a remote location while any savepoint is active. Distributed SQL in the same UR needs a clean savepoint list first.

If a utility, SQL statement, or command issues intermediate COMMIT statements while a savepoint exists, the savepoint is implicitly released. Do not assume a savepoint survives someone else’s commit.

When to use a savepoint

• Retry a failing inner step without undoing a successful outer step in the same UR
• Multi-step batch inside one COMMIT interval when only the last step is speculative
• Not a substitute for COMMIT frequency — long URs still hold locks and block utilities

Savepoints do not make a bad lock design safe. They only shrink how much SQL you undo when a business rule fails.

Explain It Like I'm Five

Imagine building with blocks. COMMIT is gluing the whole tower to the table. ROLLBACK is knocking the whole unglued tower down. A savepoint is a colored flag you stick in the tower after the first storey. If the second storey falls, you take blocks off down to the flag and try again. The first storey is still there. Taking the flag out (RELEASE SAVEPOINT) means you cannot go back to that exact height anymore. UNIQUE means you promised not to plant another flag with the same color.

Exercises

1. Write SAVEPOINT TRY_HOTEL UNIQUE ON ROLLBACK RETAIN CURSORS, then ROLLBACK TO SAVEPOINT TRY_HOTEL, then RELEASE SAVEPOINT TRY_HOTEL.
2. You set A, then B, then C, then RELEASE SAVEPOINT C. Where does ROLLBACK TO SAVEPOINT (no name) go?
3. Explain the difference between SAVEPOINT A a second time (no UNIQUE) and RELEASE SAVEPOINT A.
4. Why might a CICS program still show a VSAM update after ROLLBACK TO SAVEPOINT?
5. Why does IBM tell you to release savepoints before three-part remote access?