Definition and background of DB2 9.7 autonomous transactions

This article mainly tells you about the definition of DB2 9.7 autonomous transactions and the background of some transactions in their actual operations. We all know how to understand IBM DB2®Version 9.7 for Linux®, UNIX®, And Windows®The autonomous transaction feature introduced in. You will get an overview and examples of autonomous transaction definitions and creation methods.

Introduction

This article introduces the features of DB2 9.7 autonomous transactions. Autonomous transactions are closely related to database administrators and application developers.

This article will discuss DB2 9.7 for Linux, UNIX, and Windows. Understanding the DB2 command line processor CLP) and SQL PL knowledge will be very helpful.

To run the example in this article, you need to access the DB2 9.7 for Linux, UNIX, and Windows databases. Find the link to download the DB2 trial version from the references section.

Understand transaction background

Transactions are entities from the real world in the form of text, columns, or both) and will be processed by the database management system. These operations can be performed on databases and must be performed as a group of operations.

For example, if X amount is transferred from user A's account to user B's account, this request is A very simple transaction. This transaction can be divided into two SQL statements, as shown in Listing 1:

List 1. simple transaction example

 
 

  
  
Update table AccountInfo set CurrentBalanceCurrentBalance = CurrentBalance - X, where UserName=A   
  
  
Update table AccountInfo set CurrentBalanceCurrentBalance = CurrentBalance + X, where UserName=B  
 
 

Transactions can be successfully called only when both SQL statements can successfully update the table. To ensure that both statements take effect or do not take effect, the application runs in this way: the database will not make any changes until the COMMIT occurs. When a COMMIT occurs, all uncommitted statements since the last COMMIT statement will take effect at the same time to ensure data integrity. This is similar to disabling the auto commit action of the command line processor CLP, and then issuing a set of statements and manually completing the COMMIT operation. ROLLBACK removes all uncommitted changes. Therefore, COMMIT and ROLLBACK statements are important building blocks for transaction implementation.

Introduction to autonomous transactions

DB2 9.7 autonomous transactions have their own COMMIT and ROLLBACK scopes, ensuring that their results do not affect the uncommitted changes of the caller. In addition, the COMMITs and ROLLBACKs in the call session should not affect the final changes that occur when the autonomous transaction itself is completed.

Note that the called session will be paused until the called session returns control. The support of autonomous transactions should not be considered to support parallel execution sessions.

Create an autonomous transaction

In DB2, autonomous transactions are implemented through autonomous processes. Stored Procedures provide a natural way to bind statements to blocks. To CREATE an AUTONOMOUS process, you must specify the keyword AUTONOMOUS in the create procedure statement, as shown in listing 12.

Listing 2. create procedure statement example

 
 

  
  
CREATE OR REPLACE your_procedure_name  
  
  
LANGUAGE SQL  
  
  
AUTONOMOUS  
  
  
BEGIN   
  
  
do autonomous work ;  
  
  
END 
 
 

When calling the autonomous process, it will be executed in an independent session to provide the necessary transaction independence. Successful autonomous processes are submitted implicitly, and failed autonomous processes are rolled back. In either case, the calling transaction is not affected.