Db2 database configuration parameters

IBM® Product Master requires certain DB2® configuration parameters to be set before you install and use the product.

The following table shows the database configuration parameters that must be set for use with Product Master:

Table 1. Db2 database configuration parameters
Parameter Description Value
DFT_QUERYOPT The query optimization class is used to direct the optimizer to use different degrees of optimization when you compile SQL queries. This parameter provides more flexibility by setting the default query optimization class.
  • 5
DBHEAP There is one database heap per database, and the database manager uses it on behalf of all instances of Product Master connected to the database. It contains control block information for tables, indexes, table spaces, and buffer pools. It also contains space for the log buffer (logbufsz), and the catalog cache (catalogcache_sz). Therefore, the size of the heap depends on the number of control blocks that are stored in the heap at a specific time. The control block information is kept in the heap until all instances of Product Master disconnect from the database.

The minimum amount the database manager needs to get started is allocated at the first connection. The data area is expanded as needed up to the maximum specified by dbheap.
  • Automatic
CATALOGCACHE_SZ This parameter indicates the maximum amount of space that the catalog cache can use from the database heap (dbheap).
  • 5120
LOGBUFSZ This parameter enables you to specify the amount of the database heap (defined by the dbheap parameter) to use as a buffer for log records before it writes these records to disk. This parameter must also be less than or equal to the dbheap parameter.
  • 4096
UTIL_HEAP_SZ This parameter indicates the maximum amount of memory that can be used simultaneously by the BACKUP, RESTORE, and LOAD and load recovery utilities.
  • 5120
LOCKLIST This parameter indicates the amount of storage that is allocated to the lock list. There is one lock list per database and it contains the locks that are held by all instances of Product Master concurrently connected to the database. Depending on the size of the database, this parameter might require an increase.
  • Automatic
SORTHEAP This parameter defines the maximum number of private memory pages to be used for private sorts, or the maximum number of shared memory pages to be used for shared sorts.
  • Automatic
STMTHEAP The statement heap is used as a workspace for the SQL compiler during compilation of an SQL statement. This parameter specifies the size of this workspace.
  • Automatic
APPLHEAPSZ This parameter defines the number of private memory pages available to be used by the database manager on behalf of a specific agent or subagent.
  • Automatic
STAT_HEAP_SZ This parameter indicates the maximum size of the heap that is used in collecting statistics from running the RUNSTATS command.
  • Automatic
MAXLOCKS Lock escalation is the process of replacing row locks with table locks, reducing the number of locks in the list. This parameter defines a percentage of the lock list that is held by an application that must be filled before the database manager performs escalation.
  • Automatic
LOCKTIMEOUT This parameter specifies the number of seconds that Product Master waits to obtain a lock.
  • 60
NUM_IOCLEANERS This parameter enables you to specify the number of asynchronous page cleaners for a database. The page cleaners write changed pages from the buffer pool to disk before a database agent requires the space in the buffer pool.
  • Automatic
NUM_IOSERVERS I/O servers are used on behalf of the database agents to perform prefetch I/O and asynchronous I/O by utilities such as back up and restore. This parameter specifies the number of I/O servers for a database.
  • Automatic
MAXAPPLS This parameter specifies the maximum number of concurrent instances of Product Master that can be connected (both local and remote) to a database.
  • Automatic
AVG_APPLS The SQL optimizer uses this parameter to help estimate how much of the buffer pool is available at run time.
  • Automatic
MAXFILOP This parameter specifies the maximum number of file handles that can be open for each database agent.
  • 640
CUR_COMMIT This parameter controls the behavior of cursor stability (CS) scans.
  • ON
AUTO_MAINT This parameter is the parent of all the other automatic maintenance database configuration parameters.
  • ON
AUTO_TBL_MAINT This parameter is the parent of all table maintenance parameters.
  • ON
AUTO_RUNSTATS This automated table maintenance parameter enables or disables automatic table RUNSTATS operations for a database.
  • ON
AUTO_STMT_STATS This parameter enables and disables the collection of real-time statistics.
  • ON
Sample statement for updating database configurations:
update db cfg using SELF_TUNING_MEM ON
update db cfg using DFT_QUERYOPT 5
update db cfg using CATALOGCACHE_SZ 6000
update db cfg using LOGBUFSZ 4096
update db cfg using UTIL_HEAP_SZ 5120
update db cfg using BUFFPAGE 1024
update db cfg using LOCKTIMEOUT 60
update db cfg using MAXFILOP 640
update db cfg using AUTO_MAINT ON
update db cfg using AUTO_TBL_MAINT ON
update db cfg using AUTO_RUNSTATS ON
update db cfg using AUTO_STMT_STATS ON
You must not update the following parameters for new installations. The parameters are already set with correct values, by default: 
DBHEAP
LOCKLIST
MAXLOCKS
SORTHEAP
STMTHEAP 
APPLHEAPSZ 
STAT_HEAP_SZ 
NUM_IOCLEANERS 
NUM_IOSERVERS 
MAXAPPLS 
AVG_APPLS