Generating query recommendations to improve query performance (Query Rewrite Advisor)

You can use the Query Rewrite Advisor feature to evaluate how efficiently a query is written and to generate best-practice recommendations for rewriting it to improve its performance.

Note: This feature requires a Db2 Query Workload Tuner for z/OS (QWTz) license. Make sure you have a valid license installed before you use this feature. See Software requirements for using the SQL tuning features for more information.

  1. Open an SQL file and click on the connection in the Status Bar. Select a Db2 connection from the list that’s displayed. If you are using a tuning profile, make sure the connection that you select already has a tuning profile associated with it. If not, see Creating a tuning profile.

    Note: The message “No connection” is displayed in the Status Bar if a Db2 connection hasn’t been associated with the profile.

  2. Right-click in the .sql file and select SQL Tuning Options. The Customize Tuning Options dialog is displayed.

  3. Verify that the tuning options, such as SQL ID, schema, and current path, are correct for the environment in which you want to tune SQL queries.

  4. If you changed any tuning options, click Save. Otherwise, close the Customize Tuning Options dialog. The SQL statement is redisplayed.

    Running tuning actions

  5. Select the entire statement, right-click, and select Tune Selected SQL. The Tuning Actions dialog opens in the right side of the editor, and the statement is displayed at the top of the dialog.

  6. Select the Query Rewrite Advisor checkbox and click Tune. The Tuning Summary window opens, which shows that the status for Query Rewrite Advisor is Pending. When the job completes, the status changes to Success, and the TUNING HISTORY view is populated with the result.

  7. Click Query Rewrite Advisor in the TUNING HISTORY view. The summary and recommendations for how to rewrite the query to perform more efficiently are displayed.

    Query Rewrite Advisor recommendations

  8. Evaluate the recommendations and consider implementing them in order of severity. You might want to apply the high severity recommendations first, and then rerun the query to see if performance has improved. Continue applying additional recommendations until the query performs at an acceptable level.