IBM Data Management magazine Issue 2, 2009

Figure 2: The SQL outline view allows you to tune queries based on frequency or elapsed time.

With the formatting capability provided by Query nnotation, you can expand and collapse sections of the

SQL query. If you click on a table in the FROM clause, it will highlight related columns in the SELECT and WHERE clauses. Conversely, when you select a column in the SELECT or WHERE clause, it will highlight the table the column is related to. Additionally, if your statement references a view, Query Annotation provides the ability to drill down into the SQL query in the view.

Those with more experience will find the Access Plan raph and Access Path Advisor helpful. The Access Plan

T k o

Graph is the same graph as DB2 Visual Explain but provides additional statistical or costing information. The Access Path Advisor identifies potential access path issues and provides an explanation and recommendations for dealing with those issues.

If you are a DBA, or if you are the virtual DBA for your wn development database, you will also be interested in the Statistics Advisor and Index Advisor. As mentioned earlier, the DB2 optimizer is only as good as the catalog statistics are current. The Statistics Advisor will let you know when it’s time to update those catalog statistics and generate the command to do so—all you have to do is click a button. Even if you are running auto-stats, the Statistics Advisor can provide additional information about column distribution.

The Index Advisor will analyze the query and let you now if the use of indexes on the columns used in the query may help improve the performance of the query. And yes, it will generate the index Data Definition Language (DDL) for you, so all you have to do is click a button to create the index.

When using the Index Advisor within Optim Query uner, remember that the advice is only relevant to the single SQL statement at hand; be sure to consider how this index will perform within the overall workload. The IBM DB2 Design Advisor can provide that analysis.

Using Optim Query Tuner with Optim Development Studio Because Query Tuner can be installed into the same Eclipse instance as Optim Development Studio and there are integration points between these two products, Java developers now have natural points in their work where they can invoke the query tuning capability and get advice on writing better queries without slowing down the development process. There is even a way for DBAs or developers who don’t have access to source code to use Optim Development Studio to replace poorly performing queries with ones that have been tuned using Optim Query Tuner. The following three scenarios illustrate these points.

I dentifying and tuning performance hotspots during Java development Optim Development Studio lets you capture SQL statements and related performance metrics for any new or existing Java application, including those created using a framework such as Hibernate (see “Optimize your existing JDBC applications using pureQuery” in the “Resources” sidebar for more detail). These captured statements and metrics are displayed in the Optim Development Studio “SQL outline” view (previously known as the “pureQuery outline”).