Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Auto commit off in db2 STORED procedures

    Hi,

    I need to turn off the auto commit option in my stored procedures. some updates are happening in that stored procedures. I need to write a stored procedure it should perform the update. but those changes need not to be on my tables.

    i tried with COMMIT ON RETURN NO, but after running my procedure with this option , my tables are updating.

    can some one please let me know the way to set off the auto commit option in db2 stored procedures.
    Last edited by HABBIE; 09-19-14 at 09:39.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It is not the Stored Procedure that is auto-commiting, it is the routine calling it that is doing the commit / auto-commit.

    Andy

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    can u please make this clear. I am very new to db2 .please..

  4. #4
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    some application / program which is calling the said proc might be configured for auto commit. Are you calling the procedure directly, if yes post the statement and proc body.
    ssumit

  5. #5
    Join Date
    Jul 2014
    Posts
    294
    yes,

    I am calling my procedure directly.
    This is my call stmt.
    call m_proc1(1000);

    This is the procedure:

    BEGIN TRANSACTION:

    SET STMT1= 'UPDATE '|| DBNAME ||'.'|| TBNAME ||' SET '|| CNAME ||' = ''******''';
    execute immediate STMT1;
    SET intLOOPStmp = intLOOPStmp + 1;
    IF ( intLOOPStmp = COMMIT_COUNT )
    THEN
    commit;
    ELSEIF (SQLCODE <> 0)
    THEN
    ROLLBACK TO SAVEPOINT BEFORE_UPDATES ;
    ELSE
    END IF;

    --BEGIN
    --COMMIT ;
    ---END ;

    END;
    END LOOP loop_over_TEMP1;

    -- Cursor left open for client application
    --OPEN cursor1;
    ---SET intLOOPS = intLOOPStmp;

    END P1

  6. #6
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Quote Originally Posted by HABBIE View Post
    yes,

    I am calling my procedure directly.
    This is my call stmt.
    call m_proc1(1000);

    This is the procedure:

    BEGIN TRANSACTION:

    SET STMT1= 'UPDATE '|| DBNAME ||'.'|| TBNAME ||' SET '|| CNAME ||' = ''******''';
    execute immediate STMT1;
    SET intLOOPStmp = intLOOPStmp + 1;
    IF ( intLOOPStmp = COMMIT_COUNT )
    THEN
    commit;
    ELSEIF (SQLCODE <> 0)
    THEN
    ROLLBACK TO SAVEPOINT BEFORE_UPDATES ;
    ELSE
    END IF;

    --BEGIN
    --COMMIT ;
    ---END ;

    END;
    END LOOP loop_over_TEMP1;

    -- Cursor left open for client application
    --OPEN cursor1;
    ---SET intLOOPS = intLOOPStmp;

    END P1
    Oh.. I see , So you are using DB2 Command line to execute the proc have you checked the auto commit behavior of your CLI session:

    db2 list command options

    Command Line Processor Option Settings

    Backend process wait time (seconds) (DB2BQTIME) = 1
    No. of retries to connect to backend (DB2BQTRY) = 60
    Request queue wait time (seconds) (DB2RQTIME) = 5
    Input queue wait time (seconds) (DB2IQTIME) = 5
    Command options (DB2OPTIONS) =

    Option Description Current Setting
    ------ ---------------------------------------- ---------------
    -a Display SQLCA OFF
    -b Auto-Bind ON
    -c Auto-Commit ON
    -d Retrieve and display XML declarations OFF
    -e Display SQLCODE/SQLSTATE OFF
    -f Read from input file OFF
    -i Display XML data with indentation OFF
    -j Return code for system calls OFF
    -l Log commands in history file OFF
    -m Display the number of rows affected OFF
    -n Remove new line character OFF
    -o Display output ON
    -p Display interactive input prompt ON
    -q Preserve whitespaces & linefeeds OFF
    -r Save output to report file OFF
    -s Stop execution on command error OFF
    -t Set statement termination character OFF
    -v Echo current command OFF
    -w Display FETCH/SELECT warning messages ON
    -x Suppress printing of column headings OFF
    -z Save all output to output file OFF


    posting full proc will help to understand what you want to do.
    ssumit

  7. #7
    Join Date
    Jul 2014
    Posts
    294
    Thanks Sumit,

    I can do now.

Tags for this Thread

Posting Permissions

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