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. 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. 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. 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 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.