Tuning individual SQL statements

Edit online

This topic describes how to use the tuning features that can be integrated into Db2® Administration Foundation to tune a single SQL query. When you tune a single SQL query, the use of a tuning profile is optional. Instructions are provided for both the tuning-profile-method and the non-tuning-profile method.

Before you begin

  • Make sure that SQL Tuning Services is installed and configured, that the SQL Tuning Services repository database has been created, and that the SQL Tuning Services user ID that you are using has permission to access the repository database.
  • Make sure that EXPLAIN tables have been created.
  • If you intend to use a tuning profile, make sure that one has been created and that you are authorized to use it.

About this task

This task illustrates the general process for using the tuning features in Db2 Administration Foundation. It does not provide details for running each individual tuning feature because the process is similar for all tuning features. This task focuses on Visual Explain, Statistics Advisor, and Capture Query Environment.

Procedure

  1. From the navigation menu, click Run > SQL.
  2. From the SQL page, select a subsystem from the drop-down menu.
  3. Paste an SQL statement into the New SQL statements tab, or type a simple SELECT statement: 
    SELECT * FROM SYSIBM.SYSTABLES;
  4. Click Tune to open the Query tuning page.
    If you are using a tuning profile
    1. Set the Enable tuning profile toggle to On.
    2. Select a tuning profile. Only the profiles that are owned by you or that have been shared with you and that are available in the registered subsystem that you selected in step 2 are available.
    3. Click Next to display the Select tuning actions tab.
    If you are not using a tuning profile
    1. Set the Enable tuning profile toggle to Off.
    2. Specify a subsystem and authentication credentials or accept the default settings:
      • By default, the Registered subsystems field is populated based on the subsystem that you previously selected, if any. To tune SQL that resides on a different registered Db2 subsystem, select one from the drop-down menu.
      • For the User ID and Password fields, if you leave these fields blank, the Db2 credentials that are associated with your UMS user ID are used. Otherwise, specify a user ID and password that has the privileges that you need to tune SQL on the specified subsystem.

      You can also specify Additional JDBC parameters to authenticate to Db2 by using your own authentication method.

      For more information about supported authentication methods and example configuration settings, see Example POST requests for running tuning actions in the SQL Tuning Services documentation.

    3. Click Next to display the Select tuning actions tab.
  5. Select individual tuning actions from the Choose actions drop-down or click Select all to run all available tuning actions for the statement. The actions that are available depend on which SQL Tuning Services offering you are using: IBM® Database Services Expansion Pack or Db2 Query Workload Tuner.

    For the purposes of this task, select Statistics Advisor and Visual Explain, then click Next to display the Parameters tab.

  6. Specify tuning parameters, then click Next to run the tuning actions.
    The tuning jobs that you selected are displayed in the Output tab.
    If you are using a tuning profile
    Each tuning action that you selected is assigned a corresponding job name that ends with a suffix that indicates the type of tuning action (SA for Statistics Advisor, VE for Visual Explain, and so on).
    If you are not using a tuning profile
    All tuning actions that you selected are assigned a single job ID and name.
  7. Click View results for the Visual Explain job to display the graphical representation of the access paths used by the SQL statement. From the Visual Explain results, you can display additional information about the SQL statement by using the actions in the upper-left corner of the Visual Explain output:
    1. Click SQL Statement to review the SQL that has been explained.
    2. Click Warnings to display any warnings associated with the statement.
    3. Click Environment & Explain Options to display additional details about the SQL statement.
  8. Click Related tuning actions in the upper right corner of the Visual Explain results, then click View results for the Statistics Advisor job to display recommendations for collecting additional statistics.
  9. Click Capture query environment to start a job that collects relevant information about the query and saves it in a downloadable file. This information is a useful resource to provide to IBM Software Support if you need help with diagnosing a problem with an SQL statement.

What to do next

  • To further familiarize yourself with how to tune SQL statements, you can repeat the steps in this task for the other SQL tuning features that are available to you. If you have a valid Db2 Query Workload Tuner license, you can explore the enhanced tuning features such as Index Advisor, Query Rewrite Advisor, SQL Annotator, and so on.
  • To see the status of all the SQL query tuning jobs, from the navigation menu, select Db2 SQL Tuning > Tuning jobs.