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

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.

主站蜘蛛池模板: 轮台县| 靖宇县| 固阳县| 蓬溪县| 集贤县| 吉木萨尔县| 庆元县| 牙克石市| 海原县| 庆云县| 灌阳县| 永定县| 宁陕县| 安达市| 清镇市| 宝坻区| 乌鲁木齐市| 古丈县| 汽车| 霍林郭勒市| 黔东| 登封市| 类乌齐县| 东莞市| 五指山市| 巩留县| 元朗区| 彩票| 留坝县| 玉田县| 西盟| 象山县| 无锡市| 屯昌县| 金湖县| 额济纳旗| 芒康县| 灵武市| 荆州市| 宜兴市| 工布江达县|