DB2 Stored Procedure transaction control and error handling.

ArticleThe main content is: how to control the entire transaction when an error occurs in the stored procedure, to ensure data integrity, and to match your expectations. (Because I have not used DB2 for a long time, please give me some advice on anything wrong in this article. Thank you)

1: First prepare the test environment: Table TAA and table TBB:

 

Code

Create   Table TBB
(ID Integer Not   Null Generated By   Default
As   Identity (Start With   1 , Increment By   1 , Cache 20 , Minvalue 1 , Maxvalue 2147483647 , No cycle, no Order ),
C1 Varchar ( 128 ) Not   Null
)
Data capture none
In Userspace1;

Alter TableTBB
Locksize row
AppendOff
NotVolatile;

Create   Table TAA
(ID Integer Not   Null Generated By   Default
As   Identity (Start With   1 , Increment By   1 , Cache 20 , Minvalue 1 , Maxvalue 2147483647 , No cycle, no Order ),
C1 Varchar ( 128 ) Not   Null
)
Data capture none
In Userspace1;

Alter TableTAA
Locksize row
AppendOff
NotVolatile;

 

 

Cause of error: the null value cannot be inserted into the C1 field.

2: Create a test stored procedure. In general, people who have never used the stored procedure will write it like this:

 

Create   Procedure Test_zhaogw ()
Begin
Insert   Into TAA (C1) Values ( ' Aaaaaaa ' );
Insert   Into TBB (C1) Values ( Null );
End ;

 

The result is: the error message is displayed on the console, and the TAA table has'Aaaaaaa'. The TBB table is empty.

Many people think that the stored procedure will be automatically processed. If an error occurs in the stored procedure, it will automatically roll back the transactions executed in the stored procedure. Let's take a look at the following paragraph:

SQL procedure body is the subject of a stored procedure. Its core is a composite statement. Compound statements are surrounded by the keywords begin and end. These statements can be atomic or not atomic. By default, they are not atomic. SQL procedures requires that the statements in the composite statement and executable statements comply with specific sequence.

3: The above paragraph is a good explanation of the result of the execution of the stored procedure. Based on this section, modify the stored procedure:

 

Create   Procedure Test_zhaogw ()
Begin
P1: Begin Atomic
Insert   Into TAA (C1) Values ( ' Aaaaaaa ' );
Insert   Into TBB (C1) Values ( Null );
End P1;
End ;

 

Here, P1 can be avoided. For example, the line starting with "begin atomic" can be "end ;". If I do not understand it, this P1 should be the name of the compound statement.

The result is that the error message is displayed on the console, and the table TAA and TBB are empty. (The transaction is rolled back by the entire compound statement block ).

4: Next, let's take a look at how errors are handled in the stored procedure:

Code

Create   Procedure Test_zhaogw ()
Begin
Declare Continue Handler For Sqlexception
P1: Begin Atomic
Insert   Into TAA (C1) Values ( ' Aaaaaaa ' );
Insert   Into TBB (C1) Values ( Null );
Insert   Into TAA (C1) Values ( ' Bbbbbbbbb ' );
End P1;
End ;

 

Literally, the error continues.

The result is: the console prompts that the execution is successful. However, neither table TAA nor table TBB inserts data.

5: Remove begin atomic to see what results (the error-defined processing method ):

 

Code

Create   Procedure Test_zhaogw ()
Begin
Declare Continue Handler For Sqlexception

Insert   Into TAA (C1) Values ( ' Aaaaaaa ' );
Insert   Into TBB (C1) Values ( Null );
Insert   Into TAA (C1) Values ( ' Bbbbbbbbb ' );

End;

 

The result is: the console prompts that the execution is successful. However, the TAA table is inserted'Aaaaaaa',TBB table inserted'Bbbbbbbbb'.

I think this setting is used in a stored procedure. The subsequent business is not associated with the previous business, and everyone's operations are used when they are independent of each other, for example, you can set a scheduled data restoration procedure.

Composite statement: in the composite Statement (between begin and end)CodeAs an SQL statement. So we have:

 

Code

Create   Procedure Test_zhaogw ()
Begin
Insert   Into TAA (C1) Values ( ' Aaaaaaa ' );
P1: Begin Atomic
Insert   Into TAA (C1) Values ( ' Bbbbbbbbb ' );
Insert   Into TBB (C1) Values ( Null );
End P1;
End ;

 

The result is: the error message is displayed on the console, and the TAA table has'Aaaaaaa'. The TBB table is empty. It only rolls back

The statement block of the entire stored procedure is not atomic by default. If you have any questions, continue the test and share some special findings.

The following are some references for your reference:

Http://doc.chinaunix.net/db2/200812/207691.shtml

Http://weiruan85.javaeye.com/blog/312478