Back

[USflag] The American Programmer [USflag]
Home Books for Computer Professionals Privacy Terms
           Home   > Programming   > SQL Book   > Embedded SQL for Version 7

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)




[Books Computer]

Home Books for Computer Professionals Privacy Terms |
Site Map and Site Search Programming Manuals and Tutorials The REXX Files Top of Page |