Despite choosing logging option (either Circular logging or retain) – every “standard” data and index change is made to the log buffer. The log buffer is then written to disk by the logger process.

In the situation of COMMIT the query processing has to wait for the data to be written to disk – this is a potential performance factor.

The process is based on maximum throughput, and on analysis you can see that most of the time will be spent on the COMMIT phase and related I\O delays. This is compounded in an OLTP process – characterised by many short transactions- and by implication, COMMIT.

One tactic to minimise this problem is to use mincommit > 1. The purpose of mincommit is to delay the write to disk of the log records. The count is the number of commits.

Depending on your app, you may have to consider increasing logbufsz

Test thoroughly prior to implementing on a Production server. I’ve had some good results implementing this for Webspher Porcess Server interactions with DB2.

Republished from http://www.dba-db2.com/.