Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: Turn off autocommit

    How to turn off auto commit for that session on CLP.

    I know I can use db2 +C "select ............" to issue a command without commiting. I am trying to know the use of - "update command options" command.


    I tried issuing db2 "update command options using c OFF". It doesn't turn the auto commit off.

    See below.


    $ db2 "update command options using c OFF"
    DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
    (db2user@host1)/db2udb/db2user:
    $ 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
    -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


    Am I doing something wrong ?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The options are only valid during the "db2" command. Every time you issue it, the options reset. Each invocation is essentially its own running of the CLP. The options do not continue when it terminates.

    Andy

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I usually use db2 +c "....". If there are multiple statements/commands I need to execute with auto-commit turned off, I put them in a file and then use db2 +c -tvf <file>

  4. #4
    Join Date
    Jun 2009
    Posts
    272
    Yep Andy thats right. I just figured out that the db2 "update command options" doesn't apply for an OS shell. It only works in db2 interactive session.

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

    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

    So the only options to use on os shell are db2 +c " select ....." OR db2 +c -tvf filename

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you want to get this setting global for all sessions, then use

    db2set DB2OPTIONS=+c

    or

    set the environment variable in the .profile

    export DB2OPTIONS=+c

    If you want autocommit disabled one-off only, then set the environment variable in the current shell.

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jun 2009
    Posts
    272
    Thanks for the reply sathyaram.

  7. #7
    Join Date
    Apr 2013
    Posts
    4
    If I invoked a script via db2 +c -tvf (auto-commit off) and I issue a manual commit in the script in order to commit at specified intervals during the script, will the commits work?

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by thejav View Post
    If I invoked a script via db2 +c -tvf (auto-commit off) and I issue a manual commit in the script in order to commit at specified intervals during the script, will the commits work?
    I think first commit in the script will re-enable autocommit.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by db2girl View Post
    I think first commit in the script will re-enable autocommit.
    No it will not. It will only commit the uncomitted work.

    Andy

  10. #10
    Join Date
    Apr 2013
    Posts
    4

    MQT and auto-commit

    What I would like to do in the script that is invoked via db2 -tvf, is do some work and commit, do more work and commit. One of the reasons for turning off auto commit is that i execute the following commands for a group of MQT's:

    alter table xxx activate not logged initially;
    refresh xxx;
    commit;

    The goal is for the refresh to not generate any transaction log entries. My approach is to invoke the script with auto-commit off and then refresh an MQT at a time and commit its changes. Thus not generating any logs and at the same time keeping my locks for a brief period since it is just during the refresh of one MQT. If there is another way of realizing this objective, please tell me.

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by ARWinner View Post
    No it will not. It will only commit the uncomitted work.
    script:

    db2 alter table t1 activate not logged initially
    db2 insert into t1 values ('a')
    db2 commit
    db2 alter table t1 activate not logged initially
    db2 insert into t1 values ('b')
    db2 commit

    Execute script: db2 +c -tvf script

    Value 'a' won't be logged. Are you saying value 'b' won't be logged as well?

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by db2girl View Post
    I think first commit in the script will re-enable autocommit.
    I think I see my mistake. I should have said "re-enable logging" instead of "re-enable autocommit"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •