DB2 database configuration settings

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.

The following values are provided for tuning the database configuration settings in DB2:
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.


Feedback