Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2007
    Posts
    49

    Unanswered: Difference between Using BEGIN ATOMIC and BEGIN

    What is the difference between using BEGIN ATOMIC - END and BEGIN - END ????

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •