Methods for enabling or disabling auto-commit in DB2 UDB 9.x

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:\SQLLIB\BIN>db2 update command options using c OFF 

Turning on:
C:\SQLLIB\BIN>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)

https://www-304.ibm.com/support/docview.wss?uid=swg21297813