Results 1 to 3 of 3

Thread: Commit

  1. #1
    Join Date
    Feb 2005

    Unanswered: Commit

    Hi All !

    let's say there is a piece of java code that performs 100,000 INSERTs (into TABLE tab1) in a loop. The db2 connection of the java program does a autocomitt. Therefore, under the current execution, it does a 100,000 commits.
    In order to tune this insert program. I proposed to turn off the auto commit and commit the inserts on a frequency as defined in the code. Which I know will show a definite gain in performance.
    upon further investigation, I found that the table tab1 is always going to be empty at the begining of each run. So my question to you is will I be even better off if
    - I perform a CREATE TABLE with the NOT LOGGED INITIALLY clause, for every run.
    - turn off auto commit
    - perform one commit at the end of all INSERTs

    1. Will this be the equivalent of doing a LOAD REPLACE with LOG NO ?
    2. Will I be better off doing a commit at the end of all inserts (one a table created for every run using CREATE TABLE with NOT LOGGED INITIALLY) visa-vis doing only one CREATE TABLE and performing COMMITs at a regular frequency ?


    Also, could someone send me a piece of java code that does turns off the autocommit and performs commit within the program. That would save our developer's effort and trying to figure out 'how to turn off autocommit ? '

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1

    You may want also to take a look at this:

  3. #3
    Join Date
    May 2003
    If you don't care about the table if your transaction fails, then using NOT LOGGED INITIALLY with inserts in the same UOW should work fine. Keep in mind that you will need a backup after the inserts if you want to recover the table due to system crash or other reason when using NOT LOGGED INITIALLY.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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