Recommended settings for IBM DB2 registry variables
IBM® DB2® registry values include recommended settings for IBM DB2 performance with Sterling B2B Integrator.
| Variable | Recommended value |
|---|---|
| DB2_USE_ALTERNATE_PAGE_CLEANING | ON |
| DB2_EVALUNCOMMITTED | ON Enabling this variable can reduce the amount of unnecessary lock contention from Read Share and Next Key Share. By default, DB2 requests share locks on the index or record before verifying if the record satisfies the query predicate. Queries that scan a set of records in tables with high frequency of inserts or updates can unnecessarily block records that do not belong to its result set. When you set DB2_EVALUNCOMMITTED to ON, DB2 performs an uncommitted read on the record to perform the predicate verification. If the record satisfies the predicate, DB2 requests a share lock on that record. |
| DB2_PARALLEL_IO | Changes the way in which DB2 calculates
I/O parallelism to the tablespace. By default, DB2 sets I/O
parallelism to a tablespace equal to 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. Set the DB2_PARALLEL_IO variable if you have implemented containers on stripped devices (for example, RAID-5, RAID-10, or RAID-01). If you set DB2_PARALLEL_IO=ON or DB2_PARALLEL_IO=*, DB2 assumes that containers are implemented on a RAID 5 (6+1) configuration: six data disks plus one parity disk. In this example, prefetches to the four-container tablespace mentioned above are performed in 24 extent-sized prefetch requests. To assess the effectiveness of your prefetch parallel I/O settings, monitor the unread_prefetch_pages and prefetch_wait_time monitor element with the snapshot_database monitor. 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 indicates that the prefetch requests are too large, either because the prefetch size is larger than the pages needed or 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. If you have high prefetch_wait_time values, the application might be waiting for pages. |
| DB2_NUM_CKPW_DAEMONS | 0 Set this on IBM AIX® 5.3 only if you observe a memory leak during connect authentication within DB2's authentication daemons (db2ckpwd processes) as a result of calling the AIX loginsuccess() API. Symptoms may include excessive memory usage, an instance crash due to a trap in the db2ckpwd process, or general authentication failures. Verify by monitoring the SZ and RSS values for db2ckpwd processes. Use the ps aux | grep db2ckpwd command and look at the fifth and sixth columns of output. DB2 workaround exists for this problem. Set the following registry variable, and recycle this instance: db2set DB2_NUM_CKPW_DAEMONS=0 Also see IBM APAR IY78341. |
| DB2LOCK_TO_RB | STATEMENT |