db2 tip -- Cancel the auto commit in CLP

In DB2 CLP, the auto commit is on by default. In fact we often need commit or rollback by manual therefore canceling the auto commit is very meaning.

How to update the option?

1. Come into CLP

[db2inst1@dxzq10 ~]$ db2

@DB2INST1 =>

2. Update CLP command option

@DB2INST1 =>update command options using c off;
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.

3. list the options and validate the modification.

@DB2INST1 =>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 ON
-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

The auot commit is an option reside the CLP client. It is not same as the DB2Prompt, which is a option of local register repository. That means if we quit DB2 CLP the option will be restore the default. And the auto commit will be on again while we come into db2 CLP next time.