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:
- 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.
- In the Data Source Explorerr, right-click the currently
connected database or subsystem and select . If you need to open the Data Source Explorer,
from the main menu select . Under Data Management, select Data
Source Explorer. Then, click OK.
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.