Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009

    Unanswered: DB2 9.7 performance vs DB2 9.5

    DB2 9.7 table scanning is changed from previous versions. Now it doesn't block reders from writers.

    Does anybody know how much cost with this approach compare to DB2 9.5?

    I am thinking there will be some performance effect on this approach. I am curious to know how much percentage?


  2. #2
    Join Date
    May 2003
    The default isolation level of CS is modified in 9.7 by something called Currently Committed. This is controlled by the cur_commit db config parm which is set to ON for new databases in 9.7. If the database is upgraded to 9.7 from a previously release it is set to OFF, and of course you can change it if you want for new or upgraded databases.

    If cur_commit is ON, and a select statement encounters a locked row and puts the transaction in lockwait mode, DB2 will immediately go to transaction log to find the previous value of the data before it was locked instead of waiting for the lock to be released. IBM claims that most of the time the data is still in the log buffer, so there is very little performance hit. However, I personally believe that is only true for well-written applications that don't need cur_commit to begin with, and for a poorly written application (with lockwait problems) DB2 will have to go to the log files on disk, which could impact performance.

    Even if I were to use cur_commit ON in production, I would probably set cur_commit OFF in development environments in order to flush out poor coding techniques (lack of commits or delay in closing transactions).
    Last edited by Marcus_A; 09-14-09 at 02:19.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2002
    Toronto, Canada
    From personal experience,

    1. I agree that you must use care when using this parameter. Should not be used in development and maybe even testing so that the problems can be identified.
    2. My personal experience was that it impacted performance on my badly written app quite a lot. Other than fixing code, you need to increase 'buffpage' value to compensate.
    3. keep an eye on 'log pages read' and 'log data found in buffer' to see how things are going. If you have a log of log pages read, you will be getting into trouble area.
    4. increase 'buffpage' if you find to many log pages read.
    5. make sure your logs are on the fastest device to improve performance

    ..... imho

    Good luck

  4. #4
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Quote Originally Posted by rpm2203 View Post
    4. increase 'buffpage' if you find to many log pages read.
    You probably meant LOGBUFSZ

  5. #5
    Join Date
    Oct 2007
    db2 9.7 currently committed = Oracle undo tablespaces ??


Posting Permissions

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