IBM Support

Disabling Index Probing

Technote (FAQ)


How can I direct DB2 to avoid index probing?


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.


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'
    Change   WHERE variable >= 3 to  WHERE variable >= 3+0

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: 17 December 2016

Translate this page: