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?

Thanks,

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

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

You probably meant LOGBUFSZ

db2 9.7 currently committed = Oracle undo tablespaces ??

regds
Paul