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. 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 Workload Statistics Advisor on a query workload and ran the RUNSTATS commands that the advisor recommended. You want to run the Workload Index Advisor on the query workload, now that the relevant statistics are updated. Unless the local cache of the database catalog is updated, however, the Workload 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 query workload after ensuring that the Re-collect EXPLAIN information before running workload advisors option is selected on the Run Workload Advisors page of the Invoke section.

Feedback