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

June 2, 2004

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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers