DB2 Version 9.7 for Linux, UNIX, and Windows

mincommit - Number of commits to group configuration parameter

This parameter allows you to delay the writing of log records to disk until a minimum number of commits have been performed, helping reduce the database manager overhead associated with writing log records.

Note: As of Version 9.7 Fix Pack 3, this configuration parameter is supported by DB2® on the 64-bit version of the AIX® operating system (AIX 5.2 or later). All other supported operating systems use this parameter as normal.

Configuration type: Database
Parameter type: Configurable Online
Propagation class: Immediate
Default [range]: 1 [ 1 - 25 ]
Note: The default value is subject to change by the DB2 Configuration Advisor after initial database creation.
Unit of measure: Counter

This delay will improve performance when you have multiple applications running against a database and many commits are requested by the applications within a very short time frame.

This grouping of commits will only occur when the value of this parameter is greater than one and when the number of applications connected to the database is greater than or equal to the value of this parameter. When commit grouping is being performed, application commit requests could be held until either one second has elapsed or the number of commit requests equals the value of this parameter.

This parameter should be incremented by small amounts only; for example one (1). You should also use multi-user tests to verify that increasing the value of this parameter provides the expected results.

Changes to the value specified for this parameter take effect immediately; you do not have to wait until all applications disconnect from the database.

Recommendation: Increase this parameter from its default value if multiple read/write applications typically request concurrent database commits. This will result in more efficient logging file I/O as it will occur less frequently and write more log records each time it does occur.

You could also sample the number of transactions per second and adjust this parameter to accommodate the peak number of transactions per second (or some large percentage of it). Accommodating peak activity would minimize the overhead of writing log records during transaction intensive periods.

If you increase mincommit, you might also need to increase the logbufsz parameter to avoid having a full log buffer force a write during these transaction intensive periods. In this case, the logbufsz should be equal to:

   mincommit * (log space used, on average, by a transaction)

You can use the database system monitor to help you tune this parameter in the following ways:

Calculating the peak number of transactions per second:
Taking monitor samples throughout a typical day, you can determine your transaction intensive periods. You can calculate the total transactions by adding the following monitor elements:
- commit_sql_stmts (commit statements attempted)
- rollback_sql_stmts (rollback statements attempted)
Using this information and the available timestamps, you can calculate the number of transactions per second.
Calculating the log space used per transaction:
Using sampling techniques over a period of time and a number of transactions, you can calculate an average of the log space used with the following monitor element:
- log_space_used (unit of work log space used)