SAVEPOINT in Application Programming with DB2 and Embedded SQL

This is about the SAVEPOINT feature of DB2 Version 7
With my compliments.

Embedding SQL in a COBOL Program

Defining Savepoints

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.
	a constant. 
	not a host variable

When defining savepoints, you can specify (either or both or none)
	RETAIN CURSORS	
		don’t close cursors that are opened after the savepoint is set
		don’t close cursors when ROLLBACK is done
	RETAIN LOCKS
		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)