Optimizing new SQL queries

You can use IBM® Z Database Assistant to evaluate and optimize new SQL queries as you are creating them to ensure that they perform optimally before you run or deploy them.

1. From the SQL editor page, enter an SQL query or queries into the editing tab, which is titled query1.sql by default.
2. Select Explore from the action menu. The SQL queries that are available for exploration are displayed in the Explore query optimization window.
3. Optional: Deselect any queries that you want to exclude from the exploration.
4. Click Explore. The query or queries are evaluated. When the exploration process completes, the types of recommendations for each query are displayed in the Explored SQL statements tab.
   During exploration, IBM Z Database Assistant evaluates the index design and checks for a query rewrite that would improve the performance of the evaluated queries.
5. Click View details to display more information about the recommendations.
   Depending on the type of recommendations for a particular statement, details are provided for indexes and query rewriting.

   You can see what type of recommendations are generated for explored queries in the Explored SQL statements table. If a recommendation is available, click View details in the Actions column to view the recommendation details.

6. Click Index recommendations to view and evaluate index recommendations.
   The index recommendation tab provides a summary of the index changes that would improve the performance of a query including the estimated CPU cost savings if all the recommendations were applied. This tab includes the following sections:

   Index recommendation summary

   Expand this section to display information about the recommendations, including the estimated CPU savings, the minimum number of times the query needs to run to trade off the query performance benefit and the index maintenance cost, and the DDL for implementing the index recommendations.

   Index recommendation details

   Expand this section to view details about the tables and columns involved in the index recommendations, as well as other performance-related data.

   You can edit the Insert rate (times/day), Update rate (times/day), and Delete rate (times/day) columns to get a new evaluation of query frequency. Changing the insert rate, update rate, or delete rate updates the minimum number of times the query needs to run to gain an overall performance improvements.

   To edit a column, click the edit icon (), then type in the desired rate or use the up and down arrows, and click the confirm icon (). After confirming the change, the minimum execution frequency in the Index recommendation summary section is updated.

   Existing indexes

   Expand this section to view details about the indexes that are defined on the tables referenced in the query.

   To implement the index recommendations, copy the DDL from the Index recommendation summary section and run it from the Command processor.

7. Click Query rewriting to view and evaluate recommendations for rewriting the query.

   Whenever possible, IBM Z Database Assistant recommends a semantically equivalent but more efficient version of a query, which enables Db2 to process the query faster while consuming fewer system resources.

   An estimated CPU cost-saving is displayed on the top tile. The estimated CPU cost-saving is a comparison of the estimated CPU cost between the query before and after the recommended rewrite.

   If the Assistant is enabled, this tab provides a explanation for rewriting the query to perform more optimally, and it provides a side-by-side comparison of the existing query and the rewritten query. You can validate the semantics of the recommended query, copy the recommended query, and run it from the SQL editor.

   If the Assistant is not enabled, suggestions, explanations, and examples for improving the structure of the query on your own are displayed.