Invalidating statements in the dynamic statement cache

Start of changeYou 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.End of change

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

Start of changeTo invalidate statements in the dynamic statement cache, take any of the following actions:End of change

  • Start of change Run RUNSTATS on objects that are referenced by the statements that you want to invalidate, and specify any of the following options:
    Option Description
    Start of changeINVALDATECACHE YESEnd of change Start of changeWhen 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.End of change
    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.
    Start of changeRESET ACCESSPATHEnd of change Start of changeRUNSTATS 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.End of change
    End of change
  • Start of change 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
    End of change