DB2 tuning - Portal v6.1

DB2 tuning

+
Search Tips | Advanced Search

See also...

IBM WebSphere Portal Web Content Manager and DB2 Tuning Guide

DB2 registry variables

The following registry variables should be set (by using the db2set command) at the instance level:

Registry variable Description
DB2_RR_TO_RS This parameter is deprecated since DB2 v8.2. If you don't get an error when trying to set the parameter in DB2 higher than version 8.2, it is okay to have it set. If you get an error, never mind. The next two variables are the replacement for it.
When DB2_RR_TO_RS is on, RR behavior cannot be guaranteed for scans on user tables because next key locking is not done during index key insertion and deletion. Catalog tables are not affected by this option. The other change in behavior is that with DB2_RR_TO_RS on, scans will skip over rows that have been deleted but not committed, even though the row may have qualified for the scan.
DB2_EVALUNCOMMITTED When enabled, this variable allows, where possible, table or index access scans to defer or avoid row locking until a data record is known to satisfy predicate evaluation. DB2_EVALUNCOMMITTED 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_SKIPDELETED When enabled, this variable allows statements using either Cursor Stability or Read Stability isolation levels to unconditionally skip deleted keys during index access and deleted rows during table access. With DB2_EVALUNCOMMITTED enabled, deleted rows are automatically skipped, but uncommitted pseudo-deleted keys in type-2 indexes are not skipped unless DB2_SKIPDELETED is also enabled.
DB2_INLIST_TO_NLJN Sometimes the optimizer does not have accurate information to determine the best join method for the rewritten version of the query. This can occur if the IN list contains parameter markers or host variables which prevent the optimizer from using catalog statistics to determine the selectivity. This registry variable causes the optimizer to favor nested loop joins to join the list of values, using the table that contributes the IN list as the inner table in the join.
DB2_MINIMIZE_LISTPREFETCH Necessary to avoid an inefficient access plan for a common query on one of the tables in the JCR database.

As the instance user, enter the following commands to set the DB2 registry variables:
db2set DB2_RR_TO_RS=YES
db2set DB2_EVALUNCOMMITTED=YES
db2set DB2_SKIPDELETED=ON
db2set DB2_INLIST_TO_NLJN=YES
db2set DB2_MINIMIZE_LISTPREFETCH=ON

Registry variable	Description
DB2_RR_TO_RS	This parameter is deprecated since DB2 v8.2. If you don't get an error when trying to set the parameter in DB2 higher than version 8.2, it is okay to have it set. If you get an error, never mind. The next two variables are the replacement for it. When DB2_RR_TO_RS is on, RR behavior cannot be guaranteed for scans on user tables because next key locking is not done during index key insertion and deletion. Catalog tables are not affected by this option. The other change in behavior is that with DB2_RR_TO_RS on, scans will skip over rows that have been deleted but not committed, even though the row may have qualified for the scan.
DB2_EVALUNCOMMITTED	When enabled, this variable allows, where possible, table or index access scans to defer or avoid row locking until a data record is known to satisfy predicate evaluation. DB2_EVALUNCOMMITTED 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_SKIPDELETED	When enabled, this variable allows statements using either Cursor Stability or Read Stability isolation levels to unconditionally skip deleted keys during index access and deleted rows during table access. With DB2_EVALUNCOMMITTED enabled, deleted rows are automatically skipped, but uncommitted pseudo-deleted keys in type-2 indexes are not skipped unless DB2_SKIPDELETED is also enabled.
DB2_INLIST_TO_NLJN	Sometimes the optimizer does not have accurate information to determine the best join method for the rewritten version of the query. This can occur if the IN list contains parameter markers or host variables which prevent the optimizer from using catalog statistics to determine the selectivity. This registry variable causes the optimizer to favor nested loop joins to join the list of values, using the table that contributes the IN list as the inner table in the join.
DB2_MINIMIZE_LISTPREFETCH	Necessary to avoid an inefficient access plan for a common query on one of the tables in the JCR database.