You can use IBM® InfoSphere® Optim™ Query Tuner to help you manually tune
SQL statements in your Java applications
that use pureQuery.
Before you begin
InfoSphere Optim Query 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 pureQuery enabled. Your application
must have either of these characteristics:
- The application must contain at least one of the following objects:
- A call to one of the methods in the pureQuery Data interface
- An interface that declares at least one annotated method that
uses the pureQuery API
- A pureQueryXML file that contains captured SQL statements
- The application must use the Java Persistence
API or Hibernate, an object-relational mapping library for Java.
- Your project must be associated with either of the following databases:
- DB2® for Linux, UNIX,
and Windows
- DB2 for z/OS®
- If your project is associated with DB2 for Linux, UNIX, and Windows:
- IBM InfoSphere Optim Query
Tuner for DB2 for Linux, UNIX,
and Windows must be installed
into the same package group as IBM Data
Studio.
- IBM InfoSphere Optim Query
Tuner for DB2 for Linux, UNIX,
and Windows must be activated
on the data server.
- The data server must be configured for the query tuning client.
- If your project is associated with DB2 for z/OS:
- IBM InfoSphere Optim Query
Tuner for DB2 for z/OS must be installed into the same package
group as IBM Data Studio.
- IBM InfoSphere Optim Query
Tuner for DB2 for z/OS must be activated on the data server.
- The data server must be configured for the query tuning client
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
You tune each SQL statement manually through an iterative
process in the Query Tuner editor. 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 can open the Query Tuner editor from the
SQL Outline view, the Java editor,
or the pureQueryXML editor.
Procedure
To use the Query Tuner editor to tune SQL statements
with InfoSphere Optim Query 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 Open
Query Tuner.
|
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.
See The Query Tuner editor for help using the
editor.
- Copy the tuned SQL statement and then close the Query Tuner
editor.
- 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.