官术网_书友最值得收藏!

Using AUTONOMOUS transactions

DB2 9.7 provides ways to execute and commit a block of SQL statements independent of the outcome of invoking a transaction. For example, if transaction A invokes transaction B, which is AUTONOMOUS in nature, transaction B commits its work even if transaction A fails.

This feature enables application portability from any RDBMS that supports AUTONOMOUS transactions to DB2 9.7.

How to do it...

Let's understand the concept and the usage part of the AUTONOMOUS transaction with an example.

In an organization, the HR director wants to make sure all the salary updates are captured for audit purposes. To fulfill this request, the application developer provides an AUTONOMOUS -based code to capture the salary updates and the HR director who performs the change.

The salary, which is greater than 400,000 should only be updated by the HR director after the executive committee's approval is received, but the attempt should be captured in case anyone other than the director tries to update it.

To implement an autonomous transaction, use the AUTONOMOUS keyword while creating the procedure. The AUTONOMOUS procedure runs in its own session independent of the calling procedure. A successful AUTONOMOUS procedure commits implicitly at the end of the execution and an unsuccessful one will roll back the changes.

  1. Create two new tables to capture the update activity on an employee's salary. The table eLogData is to log the autonomous transaction activity and the table eNoLog is to log the non-autonomous transaction activity. This is explained in the following code:
    CREATE TABLE eLogData (LOGINID VARCHAR(10), EMPCODE VARCHAR(6), QUERYTIME TIMESTAMP, OLDSALARY DECIMAL(9,2), NEWSALARY DECIMAL(9,2))@ CREATE TABLE eNoLog (LOGINID VARCHAR(10), EMPCODE VARCHAR(6), QUERYTIME TIMESTAMP, OLDSALARY DECIMAL(9,2), NEWSALARY DECIMAL(9,2))@ 
    
  2. Create an AUTONOMOUS transaction procedure, logData, and a non-autonomous transaction procedure, noLog, as follows:
    CREATE OR REPLACE PROCEDURE logData (IN hrLogin varchar(10), IN empNo VARCHAR(6), IN queryTime TIMESTAMP, IN oldSalary DECIMAL(9,2), IN newSalary DECIMAL(9,2)) LANGUAGE SQL AUTONOMOUS BEGIN INSERT INTO eLogData VALUES (HRLOGIN, EMPNO, QUERYTIME, OLDSALARY, NEWSALARY); END@ CREATE OR REPLACE PROCEDURE noLog (IN hrLogin varchar(10), IN empNo VARCHAR(6), IN queryTime TIMESTAMP, IN oldSalary DECIMAL(9,2), IN newSalary DECIMAL(9,2)) LANGUAGE SQL BEGIN INSERT INTO eNoLog VALUES (HRLOGIN, EMPNO, QUERYTIME, OLDSALARY, NEWSALARY); END@ 
    
  3. Create a procedure to update the salary, and if the salary is more than 400,000, the update would roll back, as this needs an approval from the executive committee.
CREATE OR REPLACE PROCEDURE UpdateSalary (IN empCode VARCHAR(6), IN newSalary DECIMAL (9,2)) LANGUAGE SQL BEGIN DECLARE oldSalary DECIMAL(9,2); DECLARE eSal DECIMAL(9,2); DECLARE QueryTime TIMESTAMP; SET QueryTime= CURRENT TIMESTAMP; SELECT salary INTO eSal FROM EMPLOYEE WHERE empNo=empCode; SET oldSalary=eSal; CALL logData ('Tim Wilc', empCode, QueryTime, oldSalary, newSalary ); CALL noLog ('Tim Wilc', empCode, QueryTime, oldSalary, newSalary ); UPDATE EMPLOYEE SET SALARY=newSalary WHERE EMPNO=empcode; IF newSalary > 400000 THEN ROLLBACK; ELSE COMMIT; END IF; END@ 
  • The sample output of the preceding example looks similar to the following screenshot:
How to do it...

How it works…

This sample demonstrates how an AUTONOMOUS transaction differs from the standard stored procedure transaction. When Tim Wilc updates the salary of CHRISTINE to 280000 in the employee table of the sample database, both the procedures caught the change. However, while updating the salary of CHRISTINE to 480000, this activity is only caught in the AUTONOMOUS transaction, as it executes the procedure, independent of the invoking procedure.

主站蜘蛛池模板: 蓬莱市| 府谷县| 方城县| 阳信县| 雅安市| 离岛区| 阜宁县| 尼玛县| 建水县| 宝应县| 延安市| 云龙县| 古丈县| 赞皇县| 玛纳斯县| 呈贡县| 明水县| 六枝特区| 诸城市| 白朗县| 长沙市| 华池县| 枣庄市| 遵化市| 伊吾县| 调兵山市| 平江县| 区。| 长春市| 虹口区| 莆田市| 常德市| 峨眉山市| 古交市| 钟祥市| 长宁县| 石嘴山市| 涿鹿县| 漯河市| 扶沟县| 来安县|