DB2 registry variables

The configuration settings that are stored in registry variables can be applied at a global or instance level. The registry variables that affect performance involve the time required to process commands, the optimization of SQL queues, the behavior of row locking, and heap sizes.

The following values are provided for tuning the registry variables in DB2®:
Table 1. DB2 values to use when you tune the registry values that affect performance
Registry variables Starting value Purpose
DB2_SKIPINSERTED ON Decreases the time required to process certain database commands because uncommitted inserted rows are skipped.
DB2_INLIST_TO_NLJN YES Optimizes the performance of SQL queries that use the IN predicate.
DB2_MINIMIZE_LISTPREFETCH YES Prevents the use of list prefetch when prefetching would not be a good access method, for example, when the catalog statistics are not available. List prefetch is a special table access method that retrieves information from the index, sorts by page number, and then prefetches the pages.
DB2_EVALUNCOMMITTED YES Minimizes row locking until the table or index access scan determines that the data record matches the query predicate.
DB2_SKIPDELETED ON Decreases the time required to process database commands because deleted rows are skipped.
DB2_USE_ALTERNATE_PAGE_CLEANING ON Speeds up the process of creating or altering large table spaces and database restore operations.
DB2_FMP_COMM_HEAPSZ 65536 Sets the size of the heap that is used for fenced routine invocations, such as stored procedures or user-defined function calls. The size is measured in 4-KB pages.


Feedback