Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Unanswered: Transaction Control

    I have a Stored Proc, which in turn calls 4 Stored Procs.

    CREATE PROCEDURE ZAVERR.MAIN_PROC
    SPECIFIC MAIN_PROC
    LANGUAGE SQL

    BEGIN

    DECLARE YEST TIMESTAMP;

    IF (YEST NOT IN (SELECT DATE FROM HOLIDAYTABLE)) THEN

    INSERT INTO ZAVERR.MYTABLE (REPORTDATE, UPDATETIME)
    VALUES (TIMESTAMP(CURRENT DATE, '00:00:00'), TIMESTAMP(CURRENT TIMESTAMP));

    CALL ZAVERR.PROC_1;

    CALL ZAVERR.PROC_2;

    CALL ZAVERR.PROC_3;

    CALL ZAVERR.PROC_4;

    END IF;


    I want to ensure that if any of the underlying procs fail, my main proc needs to rollback completely. i.e., if PROC_1 and 2 succeed and then PROC_3 fails, all the changes done in PROC_1 and 2 must be rolled back.

    Each of the PROCS 1 through 4 have a few Insert statements inside them.

    Can someone help with this.

    Thanks.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    This is the default behaviour ... (of course, assuming that there are no commit stmts in any of the procs).

    You may want to consider using exception handlers to give a graceful exit message

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2006
    Posts
    3
    Thanks Sathyaram.

    Just wondering. Aren't Stored Procs in UDB Auto-committed. Once PROC_1 is executed succesfully, will UDB not commit the changes when exiting the PROC_1?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    AFAIK, They are not auto-committing ...

    And in many cases (like, when using a transaction manager, eg. websphere), not encouraged to commit inside a SP

    Thanks
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Sep 2006
    Posts
    3
    I tried this without any explicit commits. I introduced an error in PROC_1.

    I would have expected that when PROC_1 throws an error, the insert statement in my MAIN_PROC should be rolled back.

    But that did NOT happen.

    Any ideas?

    Thanks.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Any idea which platform and version you are running on ,,

    Can you post a copy of the called procedures ?

    Thanks
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Feb 2005
    Location
    Bangalore,India
    Posts
    39
    Statements inside the SP are auto commitable by default.

    If you want to make all the call statements as a single transaction,declare the compound statement with AUTOMIC(by default compound statement is NOT AUTOMIC).
    CREATE PROCEDURE ZAVERR.MAIN_PROC
    SPECIFIC MAIN_PROC
    LANGUAGE SQL
    BEGIN AUTOMIC
    ...
    CALL ..
    CALL ..
    ...
    END
    One more option is,use commits and rollbacks from your application side after calling the main procedure.

    CALL ZAVERR.MAIN_PROC
    IF RETURN_STATUS = 0 THEN
    ROLLBACK
    ELSE
    COMMIT
    END IF
    You can depend on the RETURN_STATUS of the stored proc or on SQLCODE, SQLSTATUS values.

    Thanks,

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The SP's I have written in DB2 for LUW (Linux, UNIX, Windows) do not auto-commit by default, and I have coded commits and rollbacks as appropriate in the SP. But I am not using Websphere with these SP's.

    I understand that commits are not allowed in z/OS SP's, but not sure about that. Unfortuneately, some of the posts above do not specify the OS platform and DB2 may not function exactly the same on all platforms in this regard.

    For error handling in DB2 LUW sub-routines (SP called from another SP) you may need to use exception handling and pass the error status back to the calling SP, who can commit or rollback as appropriate.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I guess I should have explained before ..On all platforms. zSeroes, LUW and iSeries, "COMMIT ON RETURN NO" is the default (LUW, this is the only option).
    Implicitly, to me this meant, the sql statements within the procedure are not committed automatically ..
    About using COMMIT in the SPs, all platforms permit them , albeit with a few restrictions, like commit is not allowed for in procedure called by a trigger .. iSeries is the most restictive in this regard.

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Feb 2005
    Location
    Bangalore,India
    Posts
    39
    I strongly believe the statements inside the SP's(DB2 for LUW ) are auto commitable by default.

    I tested this behavior with the below statements.I created a table with a Primary key and trying to insert a duplicate record and was expecting -803 error.

    CREATE TABLE TAB1(A INT NOT NULL,B CHAR(50));
    ALTER TABLE TAB1 ADD PRIMARY KEY (A );

    CREATE PROCEDURE TestSP ( )
    P1 : Begin
    INSERT INTO TAB1(A,B) VALUES (100,'sample');
    INSERT INTO TAB1(A,B) VALUES (100,'Expecting a failure'); -- Duplicate record
    End P1


    Call <schema>.TestSP();
    Error :
    SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "<schema>.TAB2" from having duplicate rows for those columns. SQLSTATE=23505

    Below is the output of SELECT * FROM TAB2;
    A B
    --- ---
    100 sample

    If the statements are not auto-commitable then the record(100,'sample') should not have inserted into the table.

    Thanks,

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Each individual statement in a SP is NOT autocommitted. What you are seeing is that the first insert statement is executing OK. The second one fails. Then, since you are using the CLP where autocommit is on by default, the call is then committed. This is what is saving the data in the first insert.
    To prove this, I took your code and just added a condition handler to the SP. Here is the results:

    create table andy.tab1(a int not null, b char(50))@
    alter table andy.tab1 add primary key (a)@

    CREATE PROCEDURE andy.TestSP ( )
    P1 : Begin
    declare continue handler for sqlexception rollback;
    INSERT INTO andy.TAB1(A,B) VALUES (100,'sample');
    INSERT INTO andy.TAB1(A,B) VALUES (100,'Expecting a failure'); -- Duplicate record
    End P1 @
    ------------------------------------------------------------------------------
    create table andy.tab1(a int not null, b char(50))
    DB20000I The SQL command completed successfully.

    alter table andy.tab1 add primary key (a)
    DB20000I The SQL command completed successfully.

    CREATE PROCEDURE andy.TestSP ( )
    P1 : Begin
    declare continue handler for sqlexception rollback;
    INSERT INTO andy.TAB1(A,B) VALUES (100,'sample');
    INSERT INTO andy.TAB1(A,B) VALUES (100,'Expecting a failure'); -- Duplicate record
    End P1
    DB20000I The SQL command completed successfully.

    ------------------------------ Commands Entered ------------------------------
    call andy.testsp()@
    ------------------------------------------------------------------------------
    call andy.testsp()

    Return Status = 0


    ------------------------------ Commands Entered ------------------------------
    select * from andy.tab1@
    ------------------------------------------------------------------------------
    select * from andy.tab1

    A B
    ----------- --------------------------------------------------

    0 record(s) selected.


    Andy

  12. #12
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    As Andy has mentioned, in your case, I think you have used db2’s command line to call the procedure, which by default is autocommit ..

    So, your call was equivalent of

    Call <schema>.TestSP();
    Commit ;

    I have approached the same issue of 'proving' the fact, from a slightly different angle ..

    The compound statement in your procedure is NOT ATOMIC, which means an error in one of the statements will NOT undo the previous changes in the same compound stmt.

    Use ‘Begin atomic’ instead of ‘’begin’ and you’ll see the difference.

    Even better, for understanding, call your original sp with autocommit off ..

    Db2 +c Call <schema>.TestSP();

    When the error is returned, do a rollback ..

    Db2 rollback

    Now you will not see any data in the table … If the SP statements have been autocommit, then your rollback would have had no impact.

    HTH

    Sathyaram
    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
  •