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.
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. |