DB2 MinCommit

This parameter allows delayed writing of log records to a disk until a minimum number of commits have been performed, reducing the database manager overhead associated with writing log records. For example, if MinCommit is set to 2, a second commit would cause output to the transaction log for the first and second commits. The exception occurs when a one-second timeout forces the first commit to be output if a second commit does not come along within one second. In test applications, up to 90% of the disk input and output was related to the DB2 transaction log. Changing MinCommit from 1 to 2 reduced the results to 45%.

Try to adjust this parameter if the disk input/output wait is more than 5% and there is DB2 transaction log activity from multiple sources. When a lot of activity occurs from multiple sources, it is less likely that a single commit will have to wait for another commit (or the one-second timeout).

Do not adjust this parameter if you have an application with a single thread performing a series of commits (each commit could hit the one-second delay).

To view the current value for a particular database follow these steps: Issue the DB2 command get db cfg for <dbname> (where <dbname> is the name of the application database) to list database configuration parameters. Look for "Group commit count (MINCOMMIT)". Set a new value by issuing the DB2 command update db cfg for <dbname> using mincommit n (where n is a value between 1 and 25 inclusive).

The new setting takes effect immediately.

The following are several metrics that are related to DB2 MinCommit: The disk input/output wait can be observed on AIX with the command vmstat 5. This shows statistics every 5 seconds. Look for the wa column under the CPU area. The percentage of time a disk is active can be observed on AIX with the command iostat 5. This shows statistics every 5 seconds. Look for the %tm_act column. The DB2 command get snapshot for db on <dbname> (where <dbname> is the name of the application database) shows counters for log pages read and log pages written.

The default value is 1. It is recommended that you set MinCommit to 1 or 2 (if the circumstance permits).

  Prev | Home | Next

 

WebSphere is a trademark of the IBM Corporation in the United States, other countries, or both.

 

IBM is a trademark of the IBM Corporation in the United States, other countries, or both.