You can control whether Db2 invalidates statements in the dynamic statement cache when you
collect statistics for objects that are referenced in the statements. In a data sharing environment,
the relevant statements are also invalidated in the cache of other members in the group. Statistics
collection does not invalidate cached statements by default.
About this task
The invalidation of the cached statements ensures that the next invocations of those statements
are fully prepared and use the latest access path changes.
The following actions,
among others, require that you invalidate cached dynamic statements to get new access paths:
- Statistics collection.
- Online subsystem parameter changes.
- Index changes.
- Changes to
profile tables for optimization parameters, production system modeling, or query acceleration
thresholds.
This invalidation affects only future PREPARE operations. The next time that one of the
invalidated statements is prepared, a new statement is built for the dynamic statement cache.
However, any Db2 threads that already retrieved a copy of the
statement from the dynamic statement cache before RUNSTATS completes continue to use that copy of
the statement.
Important: If you received SQLCODE -904 with reason code 00E70081, this procedure for
invalidating statements in the dynamic statement cache does not solve the problem.
Procedure
To invalidate
statements in the dynamic statement cache, take any of the following actions:
- Run RUNSTATS on objects that are referenced by the statements that you want to invalidate, and specify any of the following options:
Option |
Description |
INVALDATECACHE YES |
When Db2 generates new access paths for invalidated statements, it might use newly collected statistics, existing statistics, or default statistics to generate the new access paths, depending on the other RUNSTATS options specified. |
UPDATE NONE and REPORT NO |
RUNSTATS does not collect new statistics when you specify these options. When Db2 generates new access paths for the invalidated statements, it uses the existing statistics unchanged, unless a separate RUNSTATS job collects new statistics in the interim. |
RESET ACCESSPATH |
RUNSTATS resets the statistics to default values when you specify this option. When Db2 generates new access paths for the invalidated statements, it uses the default statistics, unless a separate RUNSTATS job collected new statistics in the interim. Access path regression is possible if Db2 uses default statistics to generate the new access paths. For more information, see Resetting access path statistics. |
-
Specify the STATISTICS keyword and include the INVALIDATECACHE YES option when you run any of
the following utilities on objects referenced by the statement:
- LOAD
- REBUILD INDEX
- REORG INDEX
- REORG TABLESPACE