Procedure
- Create a workload
of the SQL statements that you want to tune.
- In the Data Source Explorer, right-click
the database where the SQL statements are located and select . The workflow assistant opens to the Capture section.
- On the left side of the Capture section,
select the location of SQL statements that you want to tune together
as a workload. A page opens from which you can capture SQL statements
from that location.
For example, if you selected Statement Cache under DB2
for z/OS Sources, the Capture SQL from Statement
Cache page opens.
- Follow
the instructions in the page that opens. You can click the Help icon
to the right of the title for information about the page.
- After capturing SQL statements, click
the Save All to Workload button.
- In the Input Workload Name
and Description window, specify information for identifying
the new query workload.
The Manage
and Tune Workloads page of the Manage section opens. The
new query workload appears in the table on this page. The status of
the query workload is CAPTURED.
- Collect EXPLAIN information about
the SQL statements that are in the query workload. The
workflow assistant must run the EXPLAIN statement for each of the
SQL statements that are in the query workload. The Workload Statistics
Advisor requires up-to-date information about the SQL statements.
- On the Manage and Tune Workloads page,
click on the query workload.
- Click the Tasks icon in the toolbar
above the table. The Tasks page opens. This page
displays the tasks that are scheduled to run on the query workload.
- Click the Explain icon. The Schedule
Collection of EXPLAIN Information window opens.
- Schedule a task for explaining the query workload. For help, click the Help icon in the lower-left corner of the
window. If you want the workflow assistant to alert you when the task
is done, select the Receive notification when the scheduled
task is finished check box.
The task starts at the scheduled time. After it is finished,
proceed to step 3.
- Click the Invoke Advisors and
Tools icon, which is on the left end of the toolbar above
the list of query workloads. The Run Workload Advisors page
of the Invoke section opens.
- Modify values for the options for the Workload Statistics
Advisor and Workload Index Advisor. Click Set
Advisor Options under Workload on
the left side of the Invoke section. Then,
click the Statistics and Indexes tabs
to display the options that you can modify. For help with the options,
click the Help icons in the Statistics and Indexes sections.
After you are finished modifying the values for options, click Run
Workload Advisors on the left side of the Review section.
- Click the Select What to Run button.
- In the Select Activities window, select
the types of recommendations that you want. Also, select Summarize
and report recommendations. Then, click OK.
Results
The report is generated in HTML and displayed in the
Open
Workload Summary Report page of the
Review section.
To open this page, click
Open Workload Summary Report on
the left side of the workflow assistant. If you want to save the report
to your filesystem, click the
Save Report button
at the top of the report. You can also right-click the report and
select the option for printing it.