Intelligent Enterprises of the 21st Century
|
| A save point is a server side mechanism that can give you greater control over transactions. Save points enable you to start a transaction and keep portions of a transaction while rolling back other portions, without committing the overall unit of work. In the following sections, you will see how to employ them in your stored procedures. Introduction to Application Save Points
An application save point is a mechanism to control transactions within an application program such as an SQL procedure. There are three kinds of save points:
By default, a save point is internally created before the execution of each SQL statement. If an SQL statement fails with an error, DB2 will roll back to the internal save point. Statement save points are used internally by DB2. If you use an atomic compound statement, DB2 performs a block of SQL statements as one statement. If any substatement fails, all actions within the compound SQL block are rolled back. Atomic compound SQL actually uses an internal compound SQL save point. See Chapter 4, "Using Flow of Control Statements," for a discussion of atomic compound SQL. The third kind of save point, an external save point, is the focus of this section. From this point on, any reference to the term "save point" implies external save point. An external save point is similar to an atomic compound block but with more granular transaction control. After establishing a save point within a transaction, you have the option to roll back substatements of a transaction to a save point without affecting other operations in the transaction. Syntax diagrams for creating, releasing, and rolling back to a save point are shown in Figures 10.18, 10.19, and 10.20, respectively. Figure 10.18. Syntax diagram of creating a save point.
>>-SAVEPOINT--savepoint-name----+---------+---------------------> '-UNIQUE--' .-ON ROLLBACK RETAIN LOCKS--. >----ON ROLLBACK RETAIN CURSORS--+---------------------------+-->< Figure 10.19. Syntax diagram of releasing a save point.
.-TO-. >>-RELEASE--+----+--SAVEPOINT--savepoint-name------------------><
Figure 10.20. Syntax diagram of rolling back to a save point.
.-WORK-. >>-ROLLBACK--+------+--+----------------------------------+---->< '-TO SAVEPOINT--+----------------+-' '-savepoint-name-'
The keyword UNIQUE indicates that this save point name will not be reused in the transaction. If you want to establish another save point with the same name within the same transaction, you need to explicitly release the save point or end the whole transaction with COMMIT or ROLLBACK statements. ON ROLLBACK RETAIN CURSORS is a mandatory clause that describes cursor behavior within the save point. If a cursor is opened or referenced in a save point, rolling back to the save point will keep the cursor open and positioned at the next logical row of the result set. At the completion of a transaction, DB2 by default releases all database locks acquired in it. This is also the default behavior when you roll back to a save point. With the ON ROLLBACK RETAIN LOCKS option, DB2 will not release locks obtained within the save point. After the last substatement of the block, you may choose to roll back to the save point or release the save point. Once the save point is released, rolling back to the save point is no longer possible. A save point is implicitly released when the transaction is completed. Rolling back to a save point is not considered to be the end of a transaction so the save point is not released. Using Save Points in SQL Procedures
In Figure 10.21, an SQL procedure bonus_incr is illustrated. It is used to automate bonus increases for employees in a company. Company ABC is doing so well that every employee is receiving a 10 percent bonus increase. Given that the total amount of bonuses for some departments is already quite high, management has decided that if the total bonus of a department is greater than $3,000, its employees will not receive the increase immediately and will be evaluated individually. Figure 10.21. SQL procedure BONUS_INCR.
CREATE PROCEDURE bonus_incr () LANGUAGE SQL SPECIFIC bonus_incr -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries bi: BEGIN -- DECLARE VARIABLES DECLARE v_dept, v_actdept CHAR(3); DECLARE v_bonus, v_deptbonus, v_newbonus DECIMAL(9,2); DECLARE v_empno CHAR(6); DECLARE v_atend SMALLINT DEFAULT 0; -- DECLARE CURSORS DECLARE c_sales CURSOR WITH HOLD FOR -- (1) SELECT workdept, bonus, empno FROM employee ORDER BY workdept; -- DECLARE HANDLERS DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_atend=1; DECLARE EXIT HANDLER FOR SQLEXCEPTION -- (2) SET v_atend=1; -- Procedure body OPEN c_sales; FETCH c_sales INTO v_dept, v_bonus, v_empno; WHILE (v_atend = 0) DO SAVEPOINT svpt_bonus_incr ON ROLLBACK RETAIN CURSORS; -- (3) SET v_actdept = v_dept; SET v_deptbonus = 0; WHILE ( v_actdept = v_dept ) AND ( v_atend = 0 ) DO SET v_newbonus = v_bonus * 1.1; UPDATE employee SET bonus = v_newbonus WHERE empno = v_empno; SET v_deptbonus = v_deptbonus + v_newbonus; FETCH c_sales INTO v_dept, v_bonus, v_empno; END WHILE; IF v_deptbonus <= 3000.00 THEN COMMIT; -- (4) ELSE ROLLBACK TO SAVEPOINT svpt_bonus_incr; -- (5) RELEASE SAVEPOINT svpt_bonus_incr; -- (6) END IF; END WHILE; END bi
The statement on Line (1) displays a cursor that is declared to retrieve a result set of every employee sorted by department number. The statement on Line (3) defines a save point svpt_bonus_incr. This is the point to which the application can roll back. The WHILE loop iterates through the employee table for a given department and performs the bonus increase. If the total amount of bonus for a department is 3000 or less, changes made since the save point are committed on Line (4). In order to keep the cursor open so that other departments can be processed, the cursor is declared on Line (1) with the WITH HOLD option. On the other hand, if the total bonus for a department is greater than 3000, updates to the database since the last save point will be rolled back on Line (5). The save point is also released on Line (6) so that it can be created again on Line (4) before processing the next department. What happens if a statement used in the save point fails? Does DB2 roll back only the statement, the save point block, or the whole SQL procedure? The answer depends on whether a HANDLER is defined to catch the error. Without a HANDLER declared for the error, any statement failure raised in a save point will cause all changes made in the SQL procedure to be rolled back regardless of whether they are defined inside or outside of the save point. Conversely, if a HANDLER is defined for the error, only the failed statement is rolled back. This is the expected behavior when a save point is used. To make the example in Figure 10.21 more complete, an EXIT HANDLER is defined on Line (2). This makes sure that only the statement in error is rolled back and exits the stored procedure call. Nested save points are also supported for more granular transaction control. Figure 10.22 illustrates usage of nested save points. Figure 10.22. Nested save point example.
CREATE PROCEDURE nested_savepoint ( ) LANGUAGE SQL SPECIFIC nested_savepoint -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries nsp: BEGIN CREATE TABLE SP_TABLE (c1 INT); COMMIT; SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS; INSERT INTO sp_table VALUES (1); SAVEPOINT sp2 ON ROLLBACK RETAIN CURSORS; INSERT INTO sp_table VALUES (2); savepoint sp3 on rollback retain cursors; INSERT INTO sp_table VALUES (3); ROLLBACK TO SAVEPOINT sp2; COMMIT; DROP TABLE SP_TABLE; COMMIT; END nsp
The procedure shown in Figure 10.22 demonstrates the use of nested save points. The procedure contains three transactions: one to create the table SP_TABLE, another to perform some inserts, and a third transaction to drop the table. The procedure does nothing useful except to demonstrate the use of nested save points. As you can see, three save points are set in the second transaction. Before the end of transaction, a ROLLBACK TO SAVEPOINT is used to roll back changes up to the save point sp2. That is, the following statements are rolled back: INSERT INTO sp_table VALUES (2); INSERT INTO sp_table VALUES (3);
The resulting table at the end of the second transaction is a single row with a value of 1. Another observation is that you can roll back to any save point within a transaction. |
|