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:
- Set the workflow assistant to refresh the local cache automatically
after you run RUNSTATS commands or DDL statements from the workflow
assistant.
- In the main menu, select .
- In the Preferences
window, expand .
- On the EXPLAIN Options page, select
the Always refresh catalog information from the data server option,
if it not already selected.
- Click Apply, and then click OK.
- Close any open instances of the workflow assistant.
When prompted, save your work. You can reopen those instances of the
workflow assistant from the Project Explorer.
- Refresh the local cache manually.
- If the workflow assistant is maximized, double-click
its tab. The Data Source Explorer appears, unless you closed it earlier.
To open the Data Source Explorer again, from the main menu select . Under Data Management, select Data
Source Explorer and click OK.
- Right-click the currently connected database or subsystem
and select .
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.