Database configuration tuning

IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Installation Guides > Installation Guide > Performance tuning > Tivoli Data Warehouse > Relational database design and performance tuning for DB2 Database servers > Tuning
IBM Tivoli Monitoring, Version 6.3 Fix Pack 2

Database configuration tuning

Each database has a set of the database configuration parameters, which are also known as database parameters. These parameters affect the amount of system resources that are allocated to that database. Furthermore, some database configuration parameters provide descriptive information only and cannot be changed, and others are flags that indicate the status of the database.
You can use the DB2 Control Center or the UPDATE DATABASE CONFIG FOR dbname USING keyword command to change those parameters. For more information about the numerous database configuration parameters, see the DB2 Administration Guide: Performance.
The following data configuration parameters have a high impact on performance:

DBHEAP

Contains control block information for tables, indexes, table spaces, and buffer pools, and space for the log buffer (LOGBUFSZ) and temporary memory that the utilities use. Each database has only one database heap, and the database manager uses it on behalf of all applications connected to the database. The size of the heap is dependent on a large number of variables. The control block information is kept in the heap until all applications disconnect from the database. The DB2 default value is typically too low, particularly for the Tivoli Data Warehouse. Start with a value between 2000 and 8000.

DFT_DEGREE

Specifies the default value for the CURRENT DEGREE special register and the DEGREE bind option. The default value is 1, which means no intra-partition parallelism. A value of -1 means that the optimizer determines the degree of intra-partition parallelism based on the number of processors and the type of query. The degree of intra-partition parallelism for an SQL statement is specified at statement compilation time using the CURRENT DEGREE special register or the DEGREE bind option. The maximum runtime degree of intra-partition parallelism for an active application is specified using the SET RUNTIME DEGREE command. The Maximum Query Degree of Parallelism (max_querydegree) configuration parameter specifies the maximum query degree of intra-partition parallelism for all SQL queries. The actual runtime degree that is used is the lowest of one of the following:

The max_querydegree configuration parameter

Application runtime degree

SQL statement compilation degree
For a multi-processor machine, set this to –1 (ANY), to allow intra-partition parallelism for this database.

CHNGPGS_THRESH

Improves overall performance of the database applications. Asynchronous page cleaners write changed pages from the buffer pool or the buffer pools to disk before a database agent requires the space in the buffer pool. As a result, database agents should not have to wait for changed pages to be written out so that they might use the space in the buffer pool. Usually, you can start out with the default value.

LOCKLIST

Indicates the amount of storage that is allocated to the lock list. This parameter has a high impact on performance if frequent lock escalations occur. The DB2 default value is typically too low, particularly for the Tivoli Data Warehouse. Start with a value of between 500 and 800.

MAXLOCKS

Maximum percent of lock list before escalation. Use this parameter with the LOCKLIST parameter to control lock escalations. Increasing the LOCKLIST parameter augments the number of available locks.

LOGBUFSZ

Specifies the amount of the database heap (defined by the dbheap parameter) to use as a buffer for log records before writing these records to disk. Buffering the log records supports more efficient logging file I/O because the log records are written to disk less frequently, and more log records are written at each time. The DB2 default value is typically too low, particularly for the Tivoli Data Warehouse. Start with a value of between 256 and 768.

NUM_IOCLEANERS

Specifies the number of asynchronous page cleaners for a database. These page cleaners write changed pages from the buffer pool to disk before a database agent requires the space in the buffer pool. As a result, database agents do not wait for changed pages to be written out so that they can use the space in the buffer pool. This parameter improves overall performance of the database applications. The DB2 default value is typically too low. Set this parameter equal to the number of physical disk drive devices that you have. The default value is 1.

NUM_IOSERVERS

Specifies the number of I/O servers for a database. I/O servers perform prefetch I/O and asynchronous I/O by utilities such as backup and restore on behalf of the database agents. Specify a value that is one or two more than the number of physical devices on which the database is located. The DB2 default value is typically too low. Set this parameter equal to the number of physical disk drive devices that you have, and add two to that number. The default value is 3.

PCKCACHESZ

The package cache is used for caching sections for static and dynamic SQL statements on a database. Caching packages and statements eliminates the requirement to access system catalogs when reloading a package so that the database manager can reduce its internal overhead. If you are using dynamic SQL, caching removes the need for compilation.

SORTHEAP

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. Each sort has a separate sort heap that is allocated as needed by the database manager. This sort heap is the area where data is sorted. Increase the size of this parameter when frequent large sorts are required. The DB2 default value might be too low, particularly for the Tivoli Data Warehouse. Start with a value of 256 - 1024. When changing this parameter, you might want to change the SHEAPTHRES database manager parameter too.

LOGFILSIZ

Defines the size of each primary and secondary log file. The size of these log files limits the number of log records that can be written to them before they become full, which requires a new log file. The DB2 default value is too low, particularly for the Tivoli Data Warehouse. Start with a value of 4096 - 8192.

LOGPRIMARY

Specifies the number of primary log files to be pre-allocated. The primary log files establish a fixed amount of storage that is allocated to the recovery log files. The DB2 default value might be too low, particularly for the Tivoli Data Warehouse. Start with a value of 6 - 10.

Parent topic:
Tuning

+
Search Tips | Advanced Search