Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Posts
    88

    Unanswered: auto-commit set off

    Hi All,

    I wanted to ask a basic question. What is the role of auto-commit. Does it automatically commit after every SQL run or it does commit when the UOW(consisting of many SQL) gets over. what is the performance impact and

    how to set it off..

    I have tried using
    db2 update command options using c off
    but when I am doing the "list command options" its not reflecting there.

    Do I need to do something else.??

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    autocommit means a commit after each statement
    you could also do : db2 -tcvf infile
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2008
    Posts
    88
    but there are soo many SQLs to run so i wanted to set it on the system level only. there is something on systme/database level where i can set it for every SQL to run

  4. #4
    Join Date
    Jun 2006
    Posts
    471
    do you want it on or off
    on is default - off means rollback if any error and locks are held
    you could modify the db2setcp.bat file that opens the db2 command window and have the setting modified when opening the window
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Every serious database application turns auto-commit off because it has a few nasty implications:
    • every commit puts some work on the database server because DB2 must at least flush the log records for the committed transaction to disc (unless you can use group commit)
    • cursors must be opened as holdable - more resources are needed in DB2 to keep cursor state and implicit cursor-closing and EOT is not available
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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