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)