Mandatory settings for IBM DB2 registry variables

Following are the mandatory IBM DB2 registry values that are critical for IBM DB2 performance with Sterling B2B Integrator.

Variable	Mandatory value
DB2_SKIPDELETED	ON Allows index-range queries or table-scan queries to skip 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, DB2_SKIPDELETED allows, where possible, table or index access scans to defer or avoid row locking until a data record is known to satisfy predicate evaluation. This allows predicate evaluation to occur on uncommitted data. This variable 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. Deleted rows are skipped unconditionally on table scan access while deleted keys are not skipped for type-2 index scans unless DB2_SKIPDELETED is also set. Recommended value: ON
DB2_SKIPINSERTED	ON Allows SELECTs with Cursor Stability or Read Stability isolation levels to skip uncommitted inserted rows. This reduces record lock contention on tables with heavy insert rates.

Variable

Mandatory value

DB2_SKIPDELETED

Allows index-range queries or table-scan queries to skip 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, DB2_SKIPDELETED allows, where possible, table or index access scans to defer or avoid row locking until a data record is known to satisfy predicate evaluation. This allows predicate evaluation to occur on uncommitted data.

This variable 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. Deleted rows are skipped unconditionally on table scan access while deleted keys are not skipped for type-2 index scans unless DB2_SKIPDELETED is also set.

Recommended value: ON

DB2_SKIPINSERTED

Allows SELECTs with Cursor Stability or Read Stability isolation levels to skip uncommitted inserted rows. This reduces record lock contention on tables with heavy insert rates.