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. |
