Configuration Parameters

Database manager configuration parameters are those which apply for all databases managed by the current instance. You can update database manager configuration parameters using DB2 CLP or Control Center. New database manager configuration parameters take effect after DB2 UDB is stopped and restarted using DB2 UDB commands, db2stop and db2start, successively.

Most database configuration parameter changes take effect immediately. Some take effect only after all current users disconnect from a database, or after you forcefully disconnect them with the db2 force application all command, then execute the db2 terminate command to flush the database’s directory cache and remove the db2bp (backend process).

Useful DB2 CLP commands for Database Manager configuration:

• get dbm cfg.

  Lists the current setting of database manager config parms.

• update dbm cfg using parm_name new_value.

  Updates the configuration parameter parm_name For example:

  update dbm cfg using numdb 4

Useful DB2 CLP commands for Database configuration:

• get db cfg for db_name.

  Lists the database config parms for db_name database.

• update db cfg for db_name using parm_name new_value.

  Updates the parm parm_name for database db_name. For example:

  db2 update db cfg for hr800dmo using  locktimeout  60 

Following is an overview of the more important configuration parameters with tips for tuning them. Fine tuning these parameters for optimal performance gain on your system requires careful benchmarking techniques.

• BUFFPAGE

  This database-level parameter controls the database buffer pool (cache) size. It is allocated for the respective database and shared among all the connected client applications. The rule for setting this parameter is: the more the better. Too high a value can cause the system to page (check with vmstat). Database system monitor can also give you hints about the "hit-ratio" and I/O elapsed times, that can help you set a proper value. This is a database level parm. The maximum BUFFPAGE for each database without using ESTORE is about 1.5 gigabytes. However, the total BUFFPAGEs allocated for all the databases running on the same computer should not exceed 75% of the amount of real memory available for the computer.

  Note: The BUFFPAGE is related to the SYSCAT.BUFFERPOOLS table entry. DB2 LUW has the capability to attach individual buffer pool for each tablespaces. For simplicity, the user can also attach a generic buffer pool named IBMDEFAULTBP for all the tablespaces use. To set up the size of the IBMDEFAULTBP to use the DBM configuration parameter, BUFFPAGE, the user can run the given script ALTRDB.SQL or the following command:

  db2 alter bufferpool ibmdefaultbp size -1

• SORTHEAP

  This database-level parameter is the capacity of in-memory sort. If a sort size exceeds this, the sort has to be written to a temp tables and merged. Make sure this and the SHEAPTHRES parameter (see next item) are large before any large index-creation, since it presorts the data. This parameter is also important for batch jobs, such as payroll, that do large sorts.

• SHEAPTHRES

  This is a database manager level parm. If all the sort-heaps in the system at any given time exceed this value, the available memory to any further sorts is reduced. Make sure it's greater than SORTHEAP*N, where N is the number of sorts you expect to occur at any given time.

• LOCKLIST

  This database-level parameter, along with MAXLOCKS, determines the maximum memory used for database locks. When this runs out (due to a large number of row locks acquired by transactions), lock escalation will occur to minimize the lock usage. Many Row locks will be escalated to one Table lock. The drawback of Table level lock is the reduction of concurrency among multiple applications, which need access to the same tables. Check the database event monitor for lock escalations.

• NUM_IOSERVERS

  This database-level parameter stores the Number of Processes/threads used for prefetch/parallel I/O, as well as for backup/restore. A good value is the number of physical disk drives uses to house the database plus 2.

• NUM_IOCLEANERS

  This database-level parameter stores the number of page-cleaners that do "write-behind" of dirty pages. The recommended value for this parameters is to match the number of CPUs inside the computer.

• RQRIOBLK

  This database-level parameter stores the size of the cache used for row blocking for remote, cursor-based applications. Rows are placed in this cache in anticipation of their use and retrieved from here for the next FETCH request.

• ASLHEAP

  Size of the cache used for row blocking for local applications.

• LOGPRIMARY

  This database-level parameter controls the number of DB2 UDB Log files that will be pre-allocated for regular database transaction logging use. Setting this number to a high value will minimize the need to allocate log files on demand, which will improve runtime performance.

  Note: You should separate log files on separate disks from the actual database data. You can use the database configuration parameter NEWLOGPATH to do this.

• DFT_QUERYOPT

  This database-level parameter determines the query optimization class used for the SQL compilation. The higher the level represents a more detailed study of the potential access path. Since PeopleSoft applications comprises dynamic SQLs only, setting this parameter to a high value will affect the compilation time for all SQLs. The recommended value for this parameter is the default value 5.

  Note: For SQL that is complex and can benefit from the use of higher optimization class, users can alter the optimization class for that SQL alone with the following SQL construct:

  SET CURRENT QUERY OPTIMIZATION = 7 [ complex SQL ] SET CURRENT QUERY OPTIMIZATION = 5

  Most of these memory-related parameters are allocated out of one of the many heaps. You may need to adjust the heap parameters accordingly.