by Roman B. Melnyk and Paul C. Zikopoulos
The technology preview of the next release of IBM DB2 Universal Database (DB2 UDB) for Linux, UNIX , and Windows is codenamed “Stinger”. As of the publication date of this article, it is available as an open beta for download at: http://www.ibm.com/data/stinger.
This article is another in a series that introduces some of the key features in DB2 UDB “Stinger,” and includes “work-through” examples that you can use with the open beta today. In this article, we will introduce enhanced savepoint support.
A One Minute Overview of Savepoints
DB2 Version 7.1 gave application developers more control over rollback processing with support for savepoints, a feature that allows you to write application logic to perform a unit of work (UOW, or a transaction), and to selectively roll back a subset of that work without losing the entire transaction. In other words, savepoints let you avoid having to roll back an entire unit of work if there is a problem with part of that UOW.
For example, suppose that you were purchasing a book over the Web. While completing your transaction and checking out, your shopping cart application identifies a good cross-sell opportunity and presents you with a pop-up ad, offering the book at a promotional price. “Under the covers,” the application has completed all of the preliminary processing for this order, as well as the original order; you are simply presented with a Confirm or Cancel choice. If you choose to reject the promotional offer, that order will be rolled back without affecting the work that has been done on the original order.
Enhanced Savepoint Support
Before “Stinger,” savepoints were limited to one level. Savepoint support in “Stinger” has been expanded, and includes:
- The ability to nest savepoints within another savepoint
- The ability to use savepoint-related statements (SAVEPOINT and RELEASE SAVEPOINT) within atomic compound SQL statements
- The ability to use atomic compound SQL statements within an active savepoint
- The ability to create a new savepoint level within a call to a stored procedure
- The automatic creation of a new savepoint level within each call to a user-defined function (UDF)
The ability to nest savepoints allows an application to have several levels of savepoints active at the same time, and allows the application to roll back to any active savepoint. A rollback to a specific savepoint also releases any active savepoints that are nested within the savepoint that is being rolled back.
The concept of nested savepoints is illustrated by the following pseudocode:
do some work;
do more work;
do even more work;
roll back to savepoint B;
With the rollback to savepoint B, savepoint C is automatically released, but savepoint A and B remain active.
With “Stinger,” you can create savepoint levels, and you can specify when and where new savepoint levels are to be created. A savepoint level refers to the scope of reference for any savepoint-related statement. When a new savepoint level is started, a savepoint-related statement cannot refer to a savepoint created prior to the new savepoint level. Moreover, references to savepoints are resolved within the current savepoint level only.
A savepoint level ends when the event that caused its creation finishes. When a savepoint level ends, all savepoints created within it are explicitly released, and any open cursors are inherited by the parent savepoint level (that is, the savepoint level within which the level that just ended was created).