You can get recommendations for improving the performance
of groups of SQL statements that access DB2® for Linux, UNIX, and Windows databases.
Before you begin
- Ensure that you have the authorities
and privileges that are required for analyzing and tuning query workloads
that run on DB2 for Linux, UNIX, and Windows.
- In the Data Source Explorer, connect to the DB2 database, if the connection
is not already open.
- Verify the configuration of the connected database
for query-workload tuning.
- Ensure that the local cache of the system
catalog is current.
About this task
Although there is no predetermined order in which to run
the advisors for tuning a query workload, there is a general order
on which you can base your own tuning scenarios.
Procedure
In most cases, to tune a query workload, you can follow
these steps:
- Create a workload
of the SQL statements that you want to tune.
- Capture the SQL statements that you want to tune. See Locations from which you can capture SQL statements
to create query workloads that run on DB2 for Linux, UNIX, and Windows.
- 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.
- Run the Workload Statistics Advisor to generate RUNSTATS
commands for ensuring that the DB2 optimizer
has the most current and the most needed statistics. See Collecting statistics.
- Run the Workload Statistics Advisor again to generate recommendations
for creating statistical views and modifying existing statistical
views. See Creating or modifying statistical views.
- Run the Workload Index Advisor and follow its recommendations
to ensure that the right indexes exist to help avoid unnecessary table
scans. See Creating or modifying indexes.