IBM Data Management magazine Issue 2, 2009

i fr

As seen in Figure 2, the SQL outline view shows you how equently SQL statements are run and how long they run.

It provides a visual display of elapsed time for statements, including total, minimum, maximum, and average time. In addition, it tells you how many times each statement runs. You can easily identify the SQL hot spots and launch Optim Query Tuner directly from this view.

From the SQL outline view, you can also generate EXPLAIN nformation, then sort by cost to quickly find the most costly queries. Simply right-click on the query and launch Optim Query Tuner to tune that statement. With the combination of cost, elapsed time, and number of times executed, you can more easily decide which queries to focus your tuning efforts on.

Optimizing queries during new Java development Let’s assume you’re developing a new Java application using the Optim Development Studio Java editor. Not only do you get SQL assistance to help you formulate queries correctly, you can now right-click on any SQL statement in the Java editor to be launched directly into Optim Query Tuner (see Figure 3). You will see your query formatted and annotated, as well as the access plan and advice on how to fix potential problems.

Optimizing queries for existing Java database applications This scenario is for DBAs or developers who are working without the ability to change the underlying source code or who need to make a quick fix for a critical performance problem before a source code change can be tested and rolled out. In this scenario, you run the application to capture the SQL statements, invoke Optim Query Tuner directly from the captured SQL file, and use the Optim Development Studio capture file editor to replace the poorly performing statement with one that you have tuned—all without changing source code. When you set a driver property for the application, it will pick up that improved SQL statement the next time it runs. For more information about SQL replacement, see the “What’s new and exciting in IBM Data Studio Developer 2. 1” article in the “Resources” sidebar.

Improving the development process We hope that this article has given you some things to think about in terms of roles and responsibilities, skill building, and overall development process improvements. If you are a DBA who manages DB2 for Linux, UNIX, and Windows systems, you may also be interested to know that IBM is

Figure 3: You can tune queries directly from the Optim Development Studio Java editor by right-clicking on any SQL statement.

actively working on another capability for these systems that exists today for DB2 for z/OS: query workload tuning and advisory capabilities to help identify, isolate, and tune active queries in a production workload.

The authors are part of the IBM Integrated Data Management development team based in the Silicon Valley lab in San Jose, California. Cliff Leung and Tony Leung are software architects. Ray Willoughby is the technical product manager for query tuning offerings. Kathryn Zeidenstein is a member of the technical enablement team, focused on community efforts and communication.