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.