Scared of [auto]commitment?

I agree that auto-commitment is not a good thing – not unless you are hard-core.  Sure, Chuck Norris would not like manual commit mode.  If Chuck Norris was submitting a SQL statement to a database he wants his changes permanently etched into the disks with a chisel!  When Chuck Norris deletes or updates data, he means all of the consequences.

I am not Chuck Norris.

I love manual commit mode.  I like being able to tell the database, yes – happy with 1000 rows being deleted / updated…  Instead of trying to get them back later.  SQLServer is auto commit [is it a real database yet?], oracle is manual and db2 [both as400 and UDB/DB2] is auto.  This post is going to show you how to make your DB2/UDB session manual commit:

 

List command options:
db2> LIST COMMAND OPTIONS

Auto commit off
db2> UPDATE COMMAND OPTIONS USING c OFF

Auto commit on
db2> UPDATE COMMAND OPTIONS USING c ON

Example turning it off for the current session:

db2 => update command options using c OFF
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.
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                               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       OFF
   -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

Example in use with update statements:

db2 => select lmuser from sy811.f00941

LMUSER
--------------------

PSFT
PSFT
2 record(s) selected.

db2 => update sy811.f00941 set lmuser = 'SRM' where lmuser = 'PSFT'
DB20000I  The SQL command completed successfully.
db2 => select lmuser from sy811.f00941

LMUSER
--------------------

SRM
SRM
2 record(s) selected.

db2 => rollback
DB20000I  The SQL command completed successfully.
db2 => select lmuser from sy811.f00941

LMUSER
--------------------

PSFT
PSFT
2 record(s) selected.

db2 =>

If I were you, I’d test it on something innocuous first (Chuck wouldn’t).