An IBM DB2 Universal Database "Stinger" Feature Preview: Enhanced Savepoints

Wednesday Jun 2nd 2004 by DatabaseJournal.com Staff
Share:

This third article in the 'Stinger' preview series introduces enhanced savepoint support and includes some 'work-through' examples that can be used with the open beta.

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:

 savepoint A;
 do some work;
  savepoint B;
  do more work;
   savepoint C;
   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).

by Roman B. Melnyk and Paul C. Zikopoulos



A Nested Savepoint Example



Let's develop a very simple scenario that makes use of nested savepoints. We will use a connection to the SAMPLE database (which comes with DB2 UDB) as a starting point. We will create a table called INVENTORY and a table called ORDERS. We will also create an SQL stored procedure whose job it is to update the INVENTORY table when an order is processed. The following code is taken from a command script that you can create and run yourself by issuing the following command from a DB2 command prompt:



db2 -td@ -vf <script-name>.db2


This db2 command specifies the following flags: the -td option flag, which tells the command line processor (CLP) to define and to use @ as the statement termination character (because the semicolon is already being used as a statement termination character inside the procedure body); the -v option flag, which tells the CLP to echo command text to standard output; and the -f option flag, which tells the CLP to read command input from the specified file instead of from standard input.



connect to sample@

create table inventory (partno integer not null, quantity integer)@

insert into inventory values (123, 40)@

insert into inventory values (321, 7)@

create procedure update_inventory
(in pn integer, in qty integer)
dynamic result sets 1
language sql
begin
  update inventory set quantity = quantity - qty where partno = pn;
end@

connect reset@

Next, we will create and populate the ORDERS table. The following code is taken from a command script that you can create and run yourself by issuing the following command from a DB2 command prompt:

db2 -t -v +c -f <script-name>.db2

This db2 command specifies the following flags: the -t option flag, which tells the CLP to use a semicolon (;) as the statement termination character; the -v option flag, which tells the CLP to echo command text to standard output; the +c option flag, which tells the CLP not to automatically commit SQL statements; and the -f option flag, which tells the CLP to read command input from the specified file instead of from standard input. (Highlighted text is not part of the script.)

connect to sample;

create table orders (custno integer not null, partno integer not null,
 quantity integer);

select * from inventory;

PARTNO      QUANTITY
----------- -----------
        123          40
        321           7

  2 record(s) selected.


savepoint spt1 on rollback retain cursors;

insert into orders values (99, 123, 12);

call update_inventory (123, 12);

savepoint spt2 on rollback retain cursors;

insert into orders values (99, 123, 20);

call update_inventory (123, 20);

rollback to savepoint spt2;

select * from orders;

CUSTNO      PARTNO      QUANTITY
----------- ----------- -----------
         99         123          12

  1 record(s) selected.


select * from inventory;

PARTNO      QUANTITY
----------- -----------
        123          28
        321           7

  2 record(s) selected.


commit work;

connect reset;

Customer 99 places an order for 12 units of part 123, and subsequently places another order for 20 more units of the same part. Both of these insert operations into the ORDERS table, and calls to the stored procedure that updates the INVENTORY table, are preceded by the explicit creation of a named savepoint. When the customer cancels the second order, a rollback operation to savepoint sp2 ensures that the ORDERS table records only the first order, and that the INVENTORY table reflects only a reduction in inventory that is related to that first order.

Now, suppose we want to refine the stored procedure so that it never generates negative inventory numbers:

create procedure update_inventory
(in pn integer, in qty integer)
dynamic result sets 1
new savepoint level
language sql
begin
  declare new_quantity integer;
  select quantity - qty into new_quantity from inventory
   where partno = pn;
  savepoint spt99 on rollback retain cursors;
  update inventory set quantity = new_quantity where partno = pn;
  if new_quantity <= 0 then
   rollback to savepoint;
  end if;
end @

We can drop and then recreate the UPDATE_INVENTORY procedure with a new procedure body that includes a savepoint before the statement that updates the INVENTORY table. If an order were to exhaust the available inventory completely, a rollback to this savepoint ensures that the inventory table does not show negative quantities, but the scope of this rollback operation does not extend beyond the scope of the stored procedure. The ORDERS table continues to reflect the order, which has become, in this case, a back order. The NEW SAVEPOINT LEVEL clause specifies that the procedure is to establish a new savepoint level for savepoint names and effects. You could, of course, test for negative quantities before updating the INVENTORY table, but then we would not have this simple way of showing how savepoint levels work within stored procedures!

After dropping the ORDERS table (to restore initial conditions for the sake of simplicity) and removing the rollback to savepoint spt2 statement from the script, we can run the script again. The first part (up to and including the second procedure call) runs exactly as before. This time, however, the ORDERS table shows both orders (because the second order is not rolled back), but the INVENTORY table shows an adjustment related only to the first order; this is because the update pertaining to the second order (for 20 units) is rolled back within the procedure call.

...

select * from orders;

CUSTNO      PARTNO      QUANTITY
----------- ----------- -----------
         99         123          12
         99         123          20

  2 record(s) selected.


select * from inventory;

PARTNO      QUANTITY
----------- -----------
        123          16
        321           7

  2 record(s) selected.


commit work;

connect reset;

Here are some important points to remember about savepoint levels:

  • Savepoints can only be referenced within the level in which they are established. You cannot release, destroy or roll back to a savepoint established outside of the current level.
  • All active savepoints established within the current savepoint level are automatically released when the savepoint level ends.
  • The uniqueness of savepoint names is only enforced within the current savepoint level. The names of savepoints that are active in surrounding savepoint levels can be reused in the current level without affecting those other savepoints.

Wrap-up

As you can see, enhanced savepoint support in "Stinger" increases the degree of control that you can exercise over the way that more complex transactions are processed. This feature can be a valuable addition to your programming repertoire and the way you implement your business logic. We recommend that you consider exploiting savepoint-level processing as an effective approach to solving more complex real-world business problems.

About the Authors

Roman B. Melnyk, PhD, is with IBM Canada Ltd., specializing in database administration, DB2 utilities, and SQL. Roman has written numerous DB2 books, articles, and other related materials. Roman co-authored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. You can reach him at roman_b_melnyk at hotmail.com.

Paul C. Zikopoulos, BA, MBA, is with IBM Canada Ltd. Paul has written numerous magazine articles and books about DB2. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at: paulz_ibm at msn.com.

Disclaimers

The contents of this article represent those features that may or may not be available in the technology preview or beta. IBM reserves the right to include or exclude any functionality mentioned in this article for the "Stinger", or a subsequent release. As well, any performance claims made in this article are not official communications by IBM; rather the result observed by the authors is un-audited testing. The views expressed in this article are those of the authors and not necessarily those of IBM Canada Ltd. or the IBM Corporation.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved