Each database in DB2® has a separate set of configuration settings. The database configuration settings that affect performance define log file sizes, memory sizes, asynchronous cleaner options, locklist sizes, and the maximum number of file handlers.
Setting | Starting value | Purpose |
---|---|---|
CHNGPGS_THRESH | 40 | Specifies the percentage of changed pages after which the asynchronous page cleaners are started. |
DFT_QUERYOPT | 5 | Provides significant query optimization with heuristics to limit the effort used to select an access plan. |
LOGBUFSZ | 1024 | Specifies the amount, in 4-KB pages, of the database heap to use as a buffer for log records before the records are written to disk. |
LOGFILSIZ | 8096 | Defines the size of the log files, in 4-KB pages. |
LOGPRIMARY | 20 | Sets the number of primary log files, which establish a fixed amount of storage that is allocated to the recovery log files. |
LOGSECONDARY | 100 | Sets the number of secondary log files that are created and used for recovery log files when needed. |
LOCKLIST | AUTOMATIC | Specifies the amount of storage that is allocated to the lock list, which contains the locks held by all applications concurrently connected to the database. The AUTOMATIC value means that when the workload requirements change, the memory tuner is able to dynamically size the storage for the lock list. |
LOCKTIMEOUT | 300 | Specifies the number of seconds that the database waits before locking. |
NUM_IOCLEANERS | AUTOMATIC | Sets the number of asynchronous page cleaners for the database. The AUTOMATIC value means that the number of page cleaners is based on the number of processors configured on the current server and the number of local logical database partitions in a partitioned database environment. |
NUM_IOSERVERS | AUTOMATIC | Sets the number of I/O servers, which runs the prefetching operation and utilities. The AUTOMATIC value means that the number of prefetchers is calculated at database activation time. |
SOFTMAX | 1000 | Determines the frequency of soft checkpoints and the recovery range. This setting is measured in the percentage of the size of one primary log file. |
STMTHEAP | 20000 | Sets the size of the statement heap, which is used as the workspace for the SQL compiler. |
MAXFILOP | For UNIX or Linux operating
systems: 61440 For Windows operating system: 65535 |
Sets the maximum number of file handles that can be open per application. |
CUR_COMMIT | ON | Controls the behavior of cursor stability scans. |
AUTO_REVAL | DEFERRED | Controls the revalidation and invalidation semantics. The DEFERRED setting means that all dependent objects are revalidated at the time of next access. |
DEC_TO_CHAR_FMT | NEW | Controls the result of the CHAR scalar function and the CAST specification for converting decimal to character values. The NEW setting means that leading zeros and trailing decimal characters are not included in the result of the CHAR function. |
STMT_CONC | LITERALS | Enables and sets the default statement concentrator behavior.
The LITERALS setting means that SQL statements that
are identical, except for the values of literals in the statements,
can share package cache entries. After you enable the statement concentrator,
do not use the VARGRAPHICS function. To assist the query optimizer to select an efficient access plan, enable the REOPT(ONCE) bind option. |
PCKCACHESZ | 524288 | Specifies the amount of database shared memory, measured in 4-KB pages, that caches sections for static and dynamic SQL and XQuery statements on a database. |
STAT_HEAP_SZ | 51200 | Sets the maximum size of the heap, measured in 4-KB pages, that is used to collect statistics when the RUNSTATS command is run. |