SAVEPOINT savepoint-name UNIQUE
ON ROLLBACK RETAIN CURSORS
ON ROLLBACK RETAIN LOCKS;
You can define a savepoint in your application program.
a milestone you can rollback to (not a LUW)
Then later, you can ROLLBACK to that savepoint.
you may do more things, then ROLLBACK to that savepoint again
Changes made since that savepoint are rolled back.
No Commit is done.
A savepoint-name may be up to 128 bytes in length.
not a host variable
When defining savepoints, you can specify (either or both or none)
donít close cursors that are opened after the savepoint is set
donít close cursors when ROLLBACK is done
donít release locks that are taken after the savepoint is set
donít release those locks when ROLLBACK is done
Warning - if your ROLLBACK rolls back a row that was inserted, and the cursor is positioned on that row,
an SQL error will be raised.
UNIQUE is optional. If you specify it with a savepoint-name DB2 will give an error
if the savepoint-name was already used within the unit of recovery.
Omitting UNIQUE allows you to reuse (clobber) a savepoint-name.
ROLLBACK to a Savepoint
ROLLBACK TO SAVEPOINT savepoint-name;
This reverses all database changes
except to created temporary tables ,
but including declared temporary tables (new with V7).
Prepared statements are not affected
Connections are not affected
If you ROLLBACK to savepoint-1 but there was a savepoint-2 taken after savepoint-1, savepoint-2 is lost.
ROLLBACK without a savepoint-name uses the most recent savepoint that was not already rolled back.
No new unit of recovery is created
Done in SQL, not in request to transaction manager (CICS, IMS/DC)