Ensuring that the local cache of the system catalog is current

When the workflow assistant collects EXPLAIN information for SQL statements, the workflow assistant collects that information from a local cache of the catalog for the connected database or subsystem. Therefore, when the catalog changes, the local cache of the catalog must be updated before the workflow assistant collects EXPLAIN information again for the same SQL statements.

About this task

For example, suppose that you ran the Statistics Advisor on an SQL statement and ran the RUNSTATS commands that the advisor recommended. Now that the relevant statistics are updated, you want to run the Index Advisor on the SQL statement . However, if the local cache of the system catalog is not updated, the Index Advisor will use the outdated statistics, even if you specify to re-explain the statement.

Procedure

To ensure that the local cache of the system catalog is always updated after updates to the catalog occur, follow either of these steps:

What to do next

You can run advisors and tools on the current SQL statement after ensuring that the Re-explain the query option is selected on the Run Single-Query Advisors and Tools page of the Invoke section.

Feedback