Recommended Db2 dbset registry variables

DB2_EVALUNCOMMITTED

Enabling this variable can reduce the amount of unneeded lock contention from read share and next-key share. By default, Db2 requests share locks on the index or record before it checks if the record satisfies the query predicate. Queries that scan a set of records in tables with a high frequency of inserts or updates can unnecessarily be blocked on records that do not belong to its result set.

When you set DB2_EVALUNCOMMITTED=ON, Db2 performs an uncommitted read on the record to perform the predicate check. If the record satisfies the predicate, Db2 then requests a share lock on that record.

DB2_SKIPDELETED

Enabling this variable allows index-range or table-scan queries to skip over records that are in an uncommitted delete state. This reduces the amount of lock contention from read share and next-key share locks from range queries in tables with a high frequency of deletes.

When enabled, allows, where possible, table or index access scans to defer or avoid row locking until a data record is known to satisfy predicate evaluation. With this variable enabled, predicate evaluation may occur on uncommitted data.

It is applicable only to statements using either Cursor Stability or Read Stability isolation levels. For index scans, the index must be a type-2 index. Furthermore, deleted rows are skipped unconditionally on table scan access while deleted keys are not skipped for type-2 index scans unless the registry variable DB2_SKIPDELETED is also set.

DB2_SKIPINSERTED

Enabling this parameter allows SELECTs with cursor stability or read stability isolation levels to skip over uncommitted inserted rows. This parameter setting can reduce record lock contention on tables with heavy insert rates.

DB2_PARALLEL_IO

Enabling this variable changes the way in which Db2 calculates I/O parallelism to the tablespace. By default, Db2 sets I/O parallelism to a tablespace to be the number of containers in that tablespace. For example, if the tablespace has four containers, prefetches to that tablespace are performed as four extent-sized prefetch requests.

You should set the DB2_PARALLEL_IO variable if you implement containers on stripped devices (e.g., RAID-5, RAID-10 or RAID-01).

If you set DB2_PARALLEL_IO=*, Db2® assumes that containers are implemented on a RAID 5 (6+1) configuration - six data disks plus 1 parity disk. Using the example above, prefetches to the four-container tablespace above are performed in 24 extent-sized prefetch requests. For more information, see System environment variables.

You should monitor the unread_prefetch_pages and prefetch_wait_time monitor element from the snapshot_database monitor to assess the effectiveness of your prefetch parallel I/O settings. The unread_prefetch_pages monitor element tracks the number of prefetch pages that were evicted from the buffer pool before it was used. A continually growing number could indicate that the prefetch requests are too large either because the prefetch size is larger than the pages needed or that the prefetch activities are bringing in too many pages for the capacity of the buffer pool. In either case, you may want to consider reducing the prefetch size.

The application could be waiting for pages if you have high prefetch_wait_time values.

DB2_SELECTIVITY

Enabling this variable allows the selectivity clause to be used in the where clause. Without setting DB2_SELECTIVITY=YES, Db2only allows the selectivity clause to be used for User Defined Functions (UDFs).

DB2LOCK_TO_RB

This registry variable specifies whether lock timeouts cause an entire transaction or only the current statement to be rolled back. If DB2LOCK_TO_RB is set to STATEMENT, locked timeouts cause only the current statement to be rolled back. Any other setting results in transaction rollback.