- IBM DB2 9.7 Advanced Application Developer Cookbook
- Sanjay Kumar Mohankumar Saraswatipura
- 544字
- 2021-08-20 15:42:20
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.
- 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 tableeNoLog
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))@
- 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@
- 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 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.
- Visual C++程序設計教程
- 自然語言處理實戰:預訓練模型應用及其產品化
- Visual FoxPro程序設計教程(第3版)
- GraphQL學習指南
- Raspberry Pi Networking Cookbook(Second Edition)
- ASP.NET Core 2 Fundamentals
- Active Directory with PowerShell
- 蘋果的產品設計之道:創建優秀產品、服務和用戶體驗的七個原則
- 軟件測試綜合技術
- 零基礎學Java第2版
- Building Apple Watch Projects
- 絕密原型檔案:看看專業產品經理的原型是什么樣
- MATLAB從入門到精通
- Scala實用指南
- R統計應用開發實戰