DB2 Tuning and Performance

Tuning should begin with the DB2 UDB registry variables, DB2 Database Manager instance configuration parameters, and database configuration parameters that can have the biggest impact on performance. From there, look at how buffer pools are being used and determine whether additional buffer pools or different buffer pool sizes would help. Choosing the proper tablespace type, extent size, and prefetch size — as well as keeping system catalog statistics up-to-date — round out the basics of performance tuning



The following registry variable recommendations apply to Linux, Unix, and Windows platforms.

DB2_APM_PERFORMANCE. OFF is the default value for this registry variable, which specifies whether or not performance-related changes in the access plan manager (APM) that will affect the behavior of the SQL cache (package cache) are to be made. It also specifies whether the global SQL cache will operate without the use of package locks, which are internal system locks that prevent cached package entries from being inadvertently removed.

This registry variable should only be set to ON in a nonproduction environment. When ON, you may see Out of package cache errors, and memory usage may increase. And PRECOMPILE, BIND, and REBIND operations can't be performed, nor can operations that invalidate packages or make them inoperable.

DB2_AVOID_PREFETCH. This registry variable specifies whether or not prefetching should be performed during crash recovery. The default is OFF; if set to ON, prefetching isn't performed.

DB2BPVARS. Supported parameters for DB2BPVARS, which specifies the location of a file that contains parameter values to be used when tuning buffer pools, include:

NO_NT_SCATTER
NT_SCATTER_DMSFILE
NT_SCATTER_DMSDEVICE
NT_SCATTER_SMS
NUMPREFETCHQUEUES
PREFETCHQUEUESIZE

more 081126-0035205.log | grep -i ^insert | wc -l

For each of the _SCATTER parameters, the default value is 0 (or OFF) and the possible values are: 0 (OFF) and 1 (ON). For NUMPREFETCHQUEUES, the default value is 1; the range of values is 1 to NUM_IOSERVERS. And for PREFETCHQUEUESIZE, the default value is whichever is the largest: 100 or 2 * NUM_IOSERVERS. The range is 1 to 32,767.

Each _SCATTER parameter is used to turn scatter read on or off for the respective type of tablespace containers used (or to turn scatter read off for all containers). The remaining parameters can be used to improve buffer pool data prefetching.

Note: The A _SCATTER parameter can only be set to ON if DB2NTNOCACHE is set to ON and the Windows operating system is being used.

DB2CHKPTR. This registry variable specifies whether or not pointer checking for input will be performed; the default value is OFF.

DB2_ENABLE_BUFPD. The default value is OFF for this registry variable, which specifies whether or not DB2 is to use intermediate buffering to improve query performance.

DB2_EXTENDED_OPTIMIZATION. This registry variable specifies whether or not the query optimizer will use optimization extensions to improve query performance; the default is OFF.

DB2MAXFSCRSEARCH. This variable can be set to -1, or a value from 1 to 33554 in order to specify the number of free-space control records to search when adding a record to a table. It allows you to balance insert speed with space reuse (small values optimize for insert speed, large values optimize for space reuse). If the registry variable is set to -1, the DB2 Database Manager will search all free-space control records. The default value is 5.

DB2MEMMAXFREE. This variable specifies the amount of free memory that each DB2 agent will retain; values range from 0 to 2.0e+32 bytes. The default is 8,388,608 bytes.

DB2_OVERRIDE_BPF. This registry variable, which can be set to a positive number of 4k pages, specifies the size of the buffer pool (in pages) that will be created at database activation or the first time a connection is established. DB2_OVERRIDE_BPF is useful when failures resulting from memory constraints occur during database activation or the first time a connection is established. Such a memory constraint could arise either because of a real memory shortage (which is rare) or because of an attempt by the DB2 Database Manager to allocate large, inaccurately configured buffer pools. The default value is null.

DB2PRIORITIES. The values for this registry variable are platform-dependent. DB2PRIORITIES controls the priorities of DB2 processes and threads.

DB2_SORT_AFTER_TQ. DB2_SORT_AFTER_TQ specifies how the DB2 optimizer works with directed table queues in a partitioned database environment when the receiving end requires the data to be sorted and the number of receiving nodes is equal to the number of sending nodes. When set to NO (which is the default), the DB2 optimizer tends to sort at the sending end and merge the rows at the receiving end. When set to YES, the optimizer transmits the unsorted rows and sorts them at the receiving end after receiving all the rows.

DB2_STPROC_LOOKUP_FIRST. This registry variable specifies whether or not the DB2 UDB server will perform a catalog lookup for all DARIs and stored procedures before looking in the function subdirectory of the sqllib subdirectory and in the unfenced subdirectory of the function subdirectory of the sqllib subdirectory. The default is OFF.

DB2_HASH_JOIN. A YES or NO value specifies whether or not a hash join can be used when compiling a data access plan. The default is NO.

DB2_PARALLEL_IO. Possible values include * and Null (the default) for this registry variable, which specifies whether or not DB2 can use parallel I/O when reading or writing data to and from tablespace containers (even in situations where the tablespace contains only one container).

DB2_STRIPED_CONTAINERS. This variable is set to ON or Null (the default) to specify whether or not the tablespace container ID tag will take up a partial or full RAID disk stripe. When using RAID devices, the tablespace should be created with an extent size that is equal to, or a multiple of, the RAID stripe size. However, because of the one-page container tag, the extents will not line up with the RAID stripes. It may be necessary to access more physical disks than would be optimal during an I/O request unless this registry variable is set to ON.