Options db2 command line – Disable autocommit


DB2 command line utility has different options. In this post we show how to disable the autocommit of DML statements that by default is active. In case you want to do some test, for example, simulating the use of locks could be helpful to disable this feature.

Options from DB2 command line can be obtained with the following sentences:


db2 => ? options
db2 [option ...] [db2-command | sql-statement |
[? [phrase | message | sqlstate | class-code]]]
option: -a, -c, -d, -e{c|s}, -finfile, -i, -lhistfile, -m, -n, -o,
-p, -q, -rreport, -s, -t, -td;, -v, -w, -x, -zoutputfile.

Option Description Default 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
......
-x Suppress printing of column headings OFF
-z Save all output to output file OFF

 


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
........
-x Suppress printing of column headings OFF
-z Save all output to output file OFF

While the first sentence shows only the information about the options with their default values ​​(attention! not actual values), the second one shows the active values​.

The default options can be determined by DB2OPTIONS environment variable, indicating a ‘+’ or a ‘-‘ to activate/deactivate an option.

The methods to change the value of an option are:

1. From the DB2 command line call using the option as argument:


> db2 +c

2. With the command UPDATE COMMAND at any time. For example, to disable the AUTOCOMMIT feature:


db2 => UPDATE COMMAND OPTIONS USING c OFF
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.

I hope you find it useful.

If you like this post write a comment or subscribe you to our feed to obtain future posts in your feed reader.

Comments

[…] Is autocommit ON in your DB? If it is then that is the reason why the changes get permanently stored in your DB irrespective of whether or not you commit the transaction from your application. In production the autocommit is generally set OFF because it hampers the performance/response time of the DB, that is why developers are generally encouraged to control the commit or rollback of a transaction from their application. The link details the command to handle autocommit in db2: http://www.db2util.com/administration/options-db2-command-line-disable-autocommit/ […]

Add a comment

(required)

(required)