Typically, when SQL statements are added, removed, or modified
in a source program, you must rebind the related DB2® package by running the BIND command
with the ACTION(REPLACE) suboption. Running this
command might change the access plan for the SQL statements and affect
performance.
About this task
In this procedure, you create and explain a query workload
that contains the SQL statements in an application. Then, you make
the required changes to the SQL statements in your application. Next,
you create and explain another query workload that contains the SQL
statements in your application. Finally, you compare the EXPLAIN snapshot
for the first query workload to the EXPLAIN snapshot for the second
query workload.
An EXPLAIN snapshot in InfoSphere® Optim™ Query Workload Tuner is a set of EXPLAIN
information that is collected at a point in time.
Procedure
To identify and address changes to access plans due to
adding, removing, or modifying SQL statements:
- Create a query workload of the SQL statements that are
in your application.
- Capture the SQL statements.
- After capturing SQL statements, click the Create Query
Workload from All Statements 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.
- Explain the query workload.
Note: If
you captured SQL statements from the following sources and you chose
to save EXPLAIN information when creating the query workload, you
do not need to explain the query workload.
- DB2 for Linux, UNIX,
and Windows: ACTIVITIES
event monitors, EXPLAIN tables, packages, the package cache
- DB2 for z/OS: Catalog plans and packages, the dynamic
statement cache
- On the Manage Workloads page, select
the query workload and select Explain Selected Workload in
the More actions field.
- In the Collect EXPLAIN Information window, specify optional
values for the runtime environment of the SQL statements that are
in the query workload.
- Add, remove, or modify SQL statements in your application. After you are finished, if the statements
are static, rebind the packages with the EXPLAIN option set to YES
and the ACTION option set to REPLACE.
- Create another query workload of the SQL statements that
are in your application.
- Capture the SQL statements.
- After capturing SQL statements, click the Create Query
Workload from All Statements button.
- In
the Input Workload Name and Description window, specify information for
identifying the new query workload.
- Explain the query workload.
Note: If
you captured SQL statements from the following sources and you chose
to save EXPLAIN information when creating the query workload, you
do not need to explain the query workload.
- DB2 for Linux, UNIX,
and Windows: ACTIVITIES
event monitors, EXPLAIN tables, packages, the package cache
- DB2 for z/OS: Catalog plans and packages, the dynamic
statement cache
- On the Manage Workloads page, select
the query workload and select Explain Selected Workload in
the More actions field.
- In the Collect EXPLAIN Information window, specify optional
values for the runtime environment of the SQL statements that are
in the query workload.
- Compare the two query workloads.
- Click the Manage tab on the left
side of the workflow assistant.
- On the Manage Workloads page, select
the query workload that you explained in step 2. Then, click the Compare button.
- In the Select to compare with another workload
or snapshot window, select the option Compare
the access plans for the selected workload with the access plans for
another workload and click OK.
- Follow the steps in the Compare Access Plans
with Access Plans in Another Workload wizard.
- When the comparison is finished, look for changes to
access plans and to estimated costs. If there are no changes
that require action, then you are finished with the comparison. However,
if there are changes that require action, proceed to the next step.
- If you want to create and tune a
query workload that contains the SQL statements with performance problems,
select Create a new query workload for tuning in
the More actions field of the View
Comparison Results by Workload EXPLAIN Snapshots and SQL Statements page
of the Compare section. The Generate
New Query Workload wizard opens. In this wizard, you can
set criteria for including SQL statements in the query workload. After
the query workload is created, you can refer to these general steps
for tuning it.
- If you want to create optimization
hints or optimization profiles for one or more problematic statements,
follow either of these steps:
- Select a statement in the list of statements for which
you compared the access plans.
- Click the View Detail button
at the top of the list.
- At the top of the View Comparison Results by SQL Statement
Access Plan page, click the Create an optimization hint button
or the Create an optimization profile button.
The button that appears depends on the database connection that you
are working with.
- If the SQL statements are static and you tuned
a query workload of the problematic statements, or created optimization
hints or optimization profiles, rebind the packages. Set the EXPLAIN
option to YES, if you want to be able to use the bind-time EXPLAIN
snapshot in future comparisons.