Adjust IBM DB2 settings

For optimal performance, some values in the IBM® DB2® registry may need to be adjusted.

To adjust IBM DB2 settings:

If you are using an IBM DB2 database, consider changing your registry values.

The following table describes the DB2 registry values that are either recommended or are critical for IBM DB2 for LUW (Linux®, UNIX, and Windows) performance with Sterling File Gateway:

Variable	Mandatory or Recommended	Recommended Value
DB2_EVALUNCOMMITTED	Recommended	Enabling this variable can reduce the amount of unnecessary lock contention from Read Share and Next Key Share. By default, UDB 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, UDB performs an uncommitted read on the record to perform the predicate verification. If the record satisfies the predicate, UDB requests a share lock on that record. Recommended value: ON
DB2_SKIPDELETED	Recommended	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	Recommended	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. Recommended value: ON

Variable

Mandatory or Recommended

Recommended Value

DB2_EVALUNCOMMITTED

Recommended

Enabling this variable can reduce the amount of unnecessary lock contention from Read Share and Next Key Share. By default, UDB 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, UDB performs an uncommitted read on the record to perform the predicate verification. If the record satisfies the predicate, UDB requests a share lock on that record.

Recommended value: ON

DB2_SKIPDELETED

Recommended

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

Recommended

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.

Recommended value: ON

For additional information regarding IBM DB2 registry variable settings, see Recommended settings for IBM DB2 registry variables.