DB2 How to turn off or on auto-commit?

Note: The default is ON.
The "db2 list command options" can be used to see if ON or OFF.

Example 1

$ # the current default
$ db2 list command options | egrep 'Auto-Commit|DB2OPTIONS'
 Command options                           (DB2OPTIONS) =
   -c    Auto-Commit                               ON
$ # turn off auto commit
$ db2set DB2OPTIONS=+c                                    
$ db2 list command options | egrep 'Auto-Commit|DB2OPTIONS'
 Command options                           (DB2OPTIONS) = +c
   -c    Auto-Commit                               OFF
$ # trun on auto commit
$ db2set DB2OPTIONS=                                      
$ db2 list command options | egrep 'Auto-Commit|DB2OPTIONS'
 Command options                           (DB2OPTIONS) =
   -c    Auto-Commit                               ON

Example 2

$ db2 list command options | egrep 'Auto-Commit|DB2OPTIONS'
 Command options                           (DB2OPTIONS) =
   -c    Auto-Commit                               ON
$ export DB2OPTIONS=+c                                    
$ db2 list command options | egrep 'Auto-Commit|DB2OPTIONS'
 Command options                           (DB2OPTIONS) = +c
   -c    Auto-Commit                               OFF
$ export DB2OPTIONS=                                      
$ db2 list command options | egrep 'Auto-Commit|DB2OPTIONS'
 Command options                           (DB2OPTIONS) =
   -c    Auto-Commit                               ON

Example 3 [Valid for interactive session only]

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

Note: Not showing OFF if under OS command line
$ db2 list command options | egrep 'Auto-Commit|DB2OPTIONS'
 Command options                           (DB2OPTIONS) =
   -c    Auto-Commit                               ON
$ db2 update command options using C off                  
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.
$ db2 list command options | egrep 'Auto-Commit|DB2OPTIONS'
 Command options                           (DB2OPTIONS) =
   -c    Auto-Commit                               ON

Example 4
=========
directly use "db2 +c ..."

Example 5
=========
See IBM doc regarding db2cli.ini (below)
 
Reference
=========
https://www-304.ibm.com/support/docview.wss?uid=swg21297813
Methods for enabling or disabling auto-commit in DB2 UDB 9.x

Technote (FAQ)
Question What DB2 UDB 9.x command turns auto-commit off or on?

Answer

Method 1:

Using “+c” option turns off auto-commit for the current command ; rollback will undo this uncommitted change

[db2inst1@frodo ~]$ db2 +c "update tec_t_evt_rep set source=‘changeme'"
DB20000I The SQL command completed successfully.
[db2inst1@frodo ~]$ db2 rollback
DB20000I The SQL command completed successfully.
[db2inst1@frodo ~]$ db2 terminate
DB20000I The TERMINATE command completed successfully.

Method 2:
Example: using DB2OPTIONS env variable with +c (off) or -c (on)

[db2inst1@frodo ~]$ export DB2OPTIONS='+c -a'
[db2inst1@frodo ~]$ 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) = +c -a

Option Description Current Setting
------ ---------------------------------------- ---------------
-a Display SQLCA ON
-c Auto-Commit OFF

Method 3:
Example: Turning off/on auto-commit for session only

Turning off:
C:SQLLIBBIN>db2 update command options using c OFF

Turning on:
C:SQLLIBBIN>db2 update command options using c ON

Note: Method 3 only used for temporary change during interactive mode
(The db2=> prompt). It affects the current interactive session only. With
c ON, other users will see the changes made; with c OFF, other users will
be locked out of seeing the change until the commit is made..

Method 4: Using db2cli.ini configuration file

[mydatabase_alias]
autocommit=0

Note:
Env Variable Overrides to db2cli.ini are:
SQL_ATTR_AUTOCOMMIT= 1 or 0 (on or off)