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 to reduce the processing time the database manager requires when writing log records.

Important: This parameter is deprecated in version 10.1 and might be removed in a future release. This parameter can still be used in releases before version 10.1. In version 10.1 and later releases, the value specified for this configuration parameter is ignored on AIX®.
Configuration type
Database
Parameter type
  • Configurable online
  • Configurable by member in a Db2® pureScale® environment
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 might 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. Setting this parameter too high can negatively impact application response time.

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: It is recommended that this parameter is set to the default of 1.

You could 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 processing time 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)