Disabling Index Probing

Technote (FAQ)


Question

How can I direct DB2 to avoid index probing?

Cause

DB2 uses index probing during the query optimization process to obtain statistics directly from the in-memory real-time statistics tables and from an index that is a candidate for use as part of an access path. It was introduced in DB2 10 for z/OS. You might sometimes need to avoid index probing because of the processing costs or locking contention that are associated with the index probe.

Answer

You can use the following methods to disable index probing:

1. Have current, up-to-date statistics (run RUNSTATS)
2. Do not use the VOLATILE table attribute
3. Do not use REOPT(ALWAYS)
4. Use HOST variables instead of literal values.
5. Use complex predicates. For example:

    Change WHERE variable = '1234' to  WHERE variable = '123' || 4'
    or
    Change   WHERE variable >= 3 to  WHERE variable >= 3+0

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

10.0

Operating system(s):

z/OS

Software edition:

All Editions

Reference #:

1639250

Modified date:

2013-06-24

Translate my page

Machine Translation

Content navigation