How to turn off auto commit for that session on CLP.

I know I can use db2 +C "select ............" to issue a command without commiting. I am trying to know the use of - "update command options" command.


I tried issuing db2 "update command options using c OFF". It doesn't turn the auto commit off.

See below.


$ db2 "update command options using c OFF"
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
(db2user@host1)/db2udb/db2user:
$ 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
-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
-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


Am I doing something wrong ?

The options are only valid during the "db2" command. Every time you issue it, the options reset. Each invocation is essentially its own running of the CLP. The options do not continue when it terminates.

Andy

I usually use db2 +c "....". If there are multiple statements/commands I need to execute with auto-commit turned off, I put them in a file and then use db2 +c -tvf <file>

Yep Andy thats right. I just figured out that the db2 "update command options" doesn't apply for an OS shell. It only works in db2 interactive 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
-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
-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

db2 => update command options using c off
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
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
-c Auto-Commit OFF
-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
-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

So the only options to use on os shell are db2 +c " select ....." OR db2 +c -tvf filename

If you want to get this setting global for all sessions, then use

db2set DB2OPTIONS=+c

or

set the environment variable in the .profile

export DB2OPTIONS=+c

If you want autocommit disabled one-off only, then set the environment variable in the current shell.

HTH

Sathyaram

Thanks for the reply sathyaram.