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