With BEGIN ATOMIC, if a stored procedure call fails, all changes to the database that happened inside the procedure are rolled back before the control is given back to the caller ...
Similar to
With BEGIN NOT ATOMIC, any changes in the procedure till the failure, is available to the caller ..
Copy the code to a file - say proctest.sql and execute with autocommit off
db2 +c -td@ -f proctest.sql
Code:
DROP TABLE TEST.T1 @
CREATE TABLE TEST.T1(I varchar(30)) @
INSERT INTO TEST.T1 VALUES('SAMPLE ROW - 1 ' ) @
COMMIT @
DROP PROCEDURE TEST.P1 @
DROP PROCEDURE TEST.P1_ATOMIC @
COMMIT @
INSERT INTO TEST.T1 VALUES('BEFORE CALL - 1 ' ) @
--- There must be two rows
SELECT * FROM TEST.T1 @
CREATE PROCEDURE TEST.P1
LANGUAGE SQL
BEGIN NOT ATOMIC
INSERT INTO TEST.T1 VALUES('IN PROC - NOT ATOMIC' ) ;
SIGNAL SQLSTATE '40Z00' SET MESSAGE_TEXT='Not Atomic - Error' ;
END
@
CALL TEST.P1 @
--- In this select stmt you must see three rows
SELECT * FROM TEST.T1 @
ROLLBACK @
INSERT INTO TEST.T1 VALUES('BEFORE CALL - 2 ' ) @
--- There must be two row
SELECT * FROM TEST.T1 @
CREATE PROCEDURE TEST.P1_ATOMIC
LANGUAGE SQL
BEGIN ATOMIC
INSERT INTO TEST.T1 VALUES('IN PROC - ATOMIC' ) ;
SIGNAL SQLSTATE '40Z01' SET MESSAGE_TEXT='Atomic - Error' ;
END
@
CALL TEST.P1_ATOMIC @
-- In this select stmt you must see two rows
SELECT * FROM TEST.T1 @
ROLLBACK @
Here is the sample output:
Code:
DROP TABLE TEST.T1
DB20000I The SQL command completed successfully.
CREATE TABLE TEST.T1(I varchar(30))
DB20000I The SQL command completed successfully.
INSERT INTO TEST.T1 VALUES('SAMPLE ROW - 1 ' )
DB20000I The SQL command completed successfully.
COMMIT
DB20000I The SQL command completed successfully.
DROP PROCEDURE TEST.P1
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "TEST.P1" is an undefined name. SQLSTATE=42704
DROP PROCEDURE TEST.P1_ATOMIC
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "TEST.P1_ATOMIC" is an undefined name. SQLSTATE=42704
COMMIT
DB20000I The SQL command completed successfully.
INSERT INTO TEST.T1 VALUES('BEFORE CALL - 1 ' )
DB20000I The SQL command completed successfully.
SELECT * FROM TEST.T1
I
------------------------------
SAMPLE ROW - 1
BEFORE CALL - 1
2 record(s) selected.
CREATE PROCEDURE TEST.P1
LANGUAGE SQL
BEGIN NOT ATOMIC
INSERT INTO TEST.T1 VALUES('IN PROC - NOT ATOMIC' ) ;
SIGNAL SQLSTATE '40Z00' SET MESSAGE_TEXT='Not Atomic - Error' ;
END
DB20000I The SQL command completed successfully.
CALL TEST.P1
SQL0438N Application raised error or warning with diagnostic text: "Not
Atomic - Error". SQLSTATE=40Z00
SELECT * FROM TEST.T1
I
------------------------------
SAMPLE ROW - 1
BEFORE CALL - 1
IN PROC - NOT ATOMIC
3 record(s) selected.
ROLLBACK
DB20000I The SQL command completed successfully.
INSERT INTO TEST.T1 VALUES('BEFORE CALL - 2 ' )
DB20000I The SQL command completed successfully.
SELECT * FROM TEST.T1
I
------------------------------
SAMPLE ROW - 1
BEFORE CALL - 2
2 record(s) selected.
CREATE PROCEDURE TEST.P1_ATOMIC
LANGUAGE SQL
BEGIN ATOMIC
INSERT INTO TEST.T1 VALUES('IN PROC - ATOMIC' ) ;
SIGNAL SQLSTATE '40Z01' SET MESSAGE_TEXT='Atomic - Error' ;
END
DB20000I The SQL command completed successfully.
CALL TEST.P1_ATOMIC
SQL0438N Application raised error or warning with diagnostic text: "Atomic -
Error". SQLSTATE=40Z01
SELECT * FROM TEST.T1
I
------------------------------
SAMPLE ROW - 1
BEFORE CALL - 2
2 record(s) selected.
ROLLBACK
DB20000I The SQL command completed successfully.
Hope this helps
dBforums
>
Difference between Using BEGIN ATOMIC and BEGIN
03-17-11, 08:50
Linear Mode
