You can use IBM® InfoSphere® Optim™ Query Workload Tuner to help you manually
tune SQL statements in your Java™ applications
that are in projects with support for Data Access Development enabled.
Before you begin
InfoSphere Optim Query Workload Tuner provides
recommendations for modifying an SQL statement so that it takes less
time to run.
Ensure the following project and application requirements
are met:
- At least one Java project
in your workspace must have support for Data Access Development enabled.
- Your project must be associated with a DB2® for Linux, UNIX, and Windows database or a DB2 for z/OS® subsystem.
- If your project is associated with DB2 for Linux, UNIX, and Windows:
- The Query Tuning feature must be installed with IBM Data Studio.
- A licence for IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows must be activated on the data server.
- The data server must be configured for tuning with InfoSphere Optim Query
Workload Tuner.
- If your project is associated with DB2 for z/OS:
- The Query Tuning feature must be installed with IBM Data Studio.
- A licence for IBM InfoSphere Optim Query Tuner for DB2 for z/OS must
be activated on the data server.
- The data server must be configured for InfoSphere Optim Query
Workload Tuner.
Note: You cannot tune an SQL statement
if the SQL statement contains variables that are declared in your
application. The statement can contain data server related variables
such as DB2 host variables.
However,
after you bind or deploy the application, you can capture the SQL
statement from a DB2 package
or from the dynamic statement cache and then tune it. You can use
the query-tuning features or IBM InfoSphere Optim Performance Manager to capture the statement
after the application is deployed to a test or production environment.
About this task
If your Java application
contains more than a few SQL statements, knowing which SQL statements
to tune can increase your productivity. You can use EXPLAIN data in
the SQL Outline view to determine the underperforming SQL statements
that would benefit the most from tuning. You tune each SQL statement
separately or all of the SQL statements as a group manually through
an iterative process in the Query Tuner workflow assistant.
You
can open the Query Tuner workflow assistant from the SQL Outline view,
the Java editor, or the pureQueryXML
editor.
Procedure
To tune SQL statements with InfoSphere Optim Query
Workload Tuner:
- Optional: Find
underperforming SQL statements with EXPLAIN.
- Select the SQL statement that you want to tune and open
the Query Tuner editor.
| Option |
Description |
|---|
| In the SQL Outline view: |
- Right-click the SQL statement, and then click Open
Query Tuner.
|
| In the Java editor: |
- Right-click the SQL statement, and then click .
|
| In the pureQueryXML editor (available when pureQuery® support is enabled): |
- Right-click the SQL statement, and then click Open
Query Tuner.
|
Tip: You can skip directly to the recommendations
by right-clicking an SQL statement in any of these three locations
and selecting Get Query Tuner Report.
For information about tuning single SQL queries, see Tuning single SQL statements.
- Copy the tuned SQL statement and then close the Query Tuner
workflow assistant.
- Update the SQL statement in the workbench.
| If you opened the Query Tuner editor from |
Perform these steps |
|---|
| The SQL Outline view: |
- Return to the Java editor.
- Select the entire SQL statement that you tuned, and then paste
the copied SQL statement.
|
| The Java editor: |
- Select the entire SQL statement that you tuned, and then paste
the copied SQL statement.
|
| The pureQueryXML editor (available when pureQuery support is enabled): |
- Right-click the statement that you tuned, click Edit
Statement, and then paste the copied SQL statement.
|
What to do next
Repeat this process for each SQL statement that you want
to tune.