Unanswered: Auto commit off in db2 STORED procedures
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.
I am calling my procedure directly.
This is my call stmt.
This is the procedure:
SET STMT1= 'UPDATE '|| DBNAME ||'.'|| TBNAME ||' SET '|| CNAME ||' = ''******''';
execute immediate STMT1;
SET intLOOPStmp = intLOOPStmp + 1;
IF ( intLOOPStmp = COMMIT_COUNT )
ELSEIF (SQLCODE <> 0)
ROLLBACK TO SAVEPOINT BEFORE_UPDATES ;
END LOOP loop_over_TEMP1;
-- Cursor left open for client application
---SET intLOOPS = intLOOPStmp;
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.