Tuning an SQL workload (tune all)

Follow these steps to perform workload tuning (tune all) for an SQL workload of interest.

Procedure

  1. Start the query workload tuning tool you plan to use to tune SQL queries.
  2. Start the CAE Browser Client.
  3. Configure tuning. For more information, see Configure tuning.
  4. From any window within the CAE Browser Client, select Activity Browser.
  5. Use the data table to navigate to the workload you want to tune.
    The Tune All option is available in Summaries SQL text drill down and when the target is a Query Monitor Subsystem monitoring only one Db2 subsystem, the drill down path contains a single Db2 subsystem, or the target is a DSGROUP.
  6. Click Application Menu > Query Tuning > Tune All.
  7. From the Tuner Client list, select the query workload tuning tool you want to use to tune your SQL queries.
  8. Specify the appropriate options for the tuning client you select.
    The options you must specify vary depending on the tuning client you select. Some of the options listed below might not be displayed or required for the tuning client you select.
    Scope
    The SQL queries you want to tune. Select Selected Rows to tune only the rows you select. Select Current Page to tune all SQL statements on the current data table page. Select All Rows to tune all SQL statements in the data table.
    Ignore SQL Text Retrieval Errors
    Select this check box to ignore SQL text retrieval errors.
    SQL Statements
    The number of SQL statements that are to be tuned.
    Staging Table Connection
    The staging table connection to use for the storing SQL statements for tuning.
    Delete Sharing Data
    Select this check box to delete sharing data.
    Workload Prefix Name
    The workload prefix name.
    Monitor Database Connection in DSM
    The Db2® connection profile in DSM.
    Staging Table Database Connection in DSM
    The staging table connection profile in DSM.
    SQLID
    (DSM Tuner Client only) (Optional) The SQLID you want DSM to use when locating the EXPLAIN tables for tuning. If you do not type a value in this field, DSM sets the SQLID to the userid that is saved in the Database connection in DSM field.
  9. Click OK.
  10. Click Check Workload Status.
  11. Tune the activity as appropriate.