n
t
d
h
Recently, the need for highly optimized SQL crashed
eadlong into new advances in application programming.
Frameworks that automatically handle database operations, such as Hibernate and Java Persistence Architecture
(JPA), are becoming more popular with Java developers.
Development frameworks can make developers more productive, but these frameworks and the DBAs who use them
are unable to see or control the generated SQL. In fact, the
frameworks may generate SQL “under the covers” that would
make experienced DBAs tear out their hair.
Add to this the fact that these SQL statements are
ynamic, and it becomes even more difficult to track down
the originating code that produced the SQL. The result: SQL
performance issues that may not be detected until the application is under a full load in the production environment.
What if it were easier for developers to do some SQL
uning during development? Could tuning become a
routine task that every developer does easily and effectively
before deploying the application? With IBM Optim
Development Studio (formerly Data Studio Developer), the
answer can be “yes.”
With Optim Development Studio, DBAs and developers
ow have the ability to see and manipulate the actual SQL
that is being used by the Java program in the context of
the actual line of source code that produced the SQL. In
addition, Optim Development Studio provides visualization capabilities that allow developers to see which SQL
statements cost the most and, maybe even more important, how often they are run. Finally, the new query tuning
offerings provide tuning advice designed to help developers
and DBAs collaborate effectively to produce fast, less costly
enterprise-ready database applications.
b
Introducing IBM Optim Query Tuner
It’s understandable why developers don’t make query tuning
a high priority. First of all, developers tend to focus on the
results a query returns, asking only, “Am I getting the data I
need?” Second, developers are often under tight deadlines
and don’t have time to truly examine how queries perform—
and they may not be able to unit test under a significant load
to uncover problems. Finally, understanding how the EXPLAIN
tables relate and how to interpret the information contained
in them can be intimidating and cryptic.
Even if a developer spots a SQL issue, there is still a gap
etween knowing you have a problem and knowing what to
do about it. IBM DB2 Visual Explain can provide a picture
of what the database is doing, but that picture can be pretty
mysterious to a developer, or even to a new DBA. Plus, DB2
Visual Explain only describes what the database is doing; it
si
t
doesn’t provide insight into the performance ramifications
of that choice. Experienced DBAs who may be able to solve
the problem are often so busy with other high-value activities that they may not be able to help until the problem lands
in their lap on the production system.
Optim Query Tuner can help both novice and expert da-abase developers write better-performing SQL. Its easy-to-use advisors and query visualizations can help cut costs and
improve performance by providing expert advice on writing
high-quality queries and improving database design.
As the name suggests, Optim Query Tuner provides
ngle-query tuning assistance. You can invoke Optim Query
Tuner for SQL statements from:
Packages, SQL stored procedures, triggers, and user- ` `
defined functions (UDFs)—all from within the Data
Source Explorer in the Eclipse environment
The integrated query editor and routine editor within ` `
IBM Data Studio or other Optim development offerings
The SQL Outline and the Java editor within Optim ` `
Development Studio
The DB2 catalog, for database packages and stored ` `
procedures
This single-query tuning capability is announced for IBM
Optim Query Tuner 2. 2. You can get combined single query
and workload tuning today for IBM DB2 for z/OS with DB2
Optimization Expert for z/OS (which will soon be renamed
to IBM Optim Query Workload Tuner for DB2 for z/OS).
b
t
Visualization and advisor options
Once you select a statement for tuning, there are several
visualizations and advisors available to help you.
Developers with less SQL experience may rely heavily on
he Query Advisor. This advisor makes recommendations on
how to rewrite a query to enhance efficiency based on best-practice rules. When you select a recommendation, the line
of SQL is highlighted, and you are presented with a description of how to rewrite the query, as well as an explanation of
the recommendation (see Figure 1).
Note that recommendations for changing queries are
ased on the assumption that the DB2 catalog statistics are
accurate. With Query Annotation, you can format the SQL
statement for improved readability and navigation and see
which statistics are being used at the table and column level.
If there are no statistics, then you’ll need to update the DB2
catalog statistics (the Query Tuner Statistics Advisor can
help you do this).
