Running a Selectivity Override tuning job

You can improve query access plans for statements with parameter markers and host variables in their predicates, using the Selectivity Override option, found in the IBM® Db2® Query Workload Tuner for z/OS®(QWTz) Tuning Results page. This option is available for tuning statements running on Db2 for z/OS Version 11NFM, or newer versions.

About this task

Because Db2 for z/OS has difficulty estimating filter factor values for some predicates (parameter markers and host variables, specifically), users can add selectivity override values to statements, which can be used by Db2 for z/OS to improve the access plans. For example, with these types of predicates, Db2 for z/OS can only do its best to provide estimated or default filter factor values. By including a selectivity profile for the statement, these estimated or default values are overwritten with input selectivity values, and Db2 for z/OS can produce more accurate access plans.

See Overriding predicate sensitivities at the statement level in the Db2 for z/OS Knowledge Center to learn more.

Before you begin

The ability to run this feature from the DSM Tuning Results page is dependent on the statement being tuned having its parameter markers (dynamic statements) or host variables (static statements) collected properly, using the Query Monitor for Db2 for z/OS (QM).

Before attempting to tune a query using the Selectivity Override feature, ensure you have completed the following tasks, using QM:

Procedure

To run a selectivity override tuning job using QWTz:

  1. From the QM web console, open the workload browser and, from the Application menu, select Query Tuning > Manage Staging Tables > Manage Workloads.
  2. Select the workload containing the statements that you want to tune.
  3. Click Tune.
    This opens the Tune Workload dialog box
  4. Select the following options:
    • Choose Data Server Manager (DSM) as your tuner client.
      Note: The Query Workload Tuner option refers to the Eclipse-based Optim Query Workload Tuner application.
    • Ensure Existing Workload is selected for Scope
    • Ensure Delete Sharing Data is NOT checked, as this will protect the host variables/parameter markers that you collected from being deleted.
    • Select appropriate values for the remaining options.
  5. Click OK.
  6. From the Workload Sent to DSM page, click Check workload status in DSM
    The QWTz web console opens in a new browser window, with the Tuning Jobs page displayed.
  7. When the status of your tuning job reads Succeeded, click View Results.
  8. From the View workload statements page, select your workload from the grid and then click Host Variables.
  9. Click the check box to the left of each host variable that you want to include in your tuning job and then click Selectivity Override.
    A message box notifies you that a new tuning job has started. Click Close.
  10. When the tuning job has completed successfully, select it from the grid and click View Results.
    By default, the scripts to generate a selectivity profile are displayed. You will see the Estimated performance improvement if this selectivity profile is deployed.
  11. Click View Revised Access Plan Graph to see how the access plan would change if the selectivity profile were deployed.
  12. If desired, you can click Compare Access Plan to compare the access plans created before and after the selectivity override recommendation. Click Run Scripts to deploy the selectivity profile using the Job Manager.
  13. For dynamic statements with parameter markers, the access plan for the query has to first be invalidated in the Dynamic Statement Cache for the new execution plan to be effective. See RUNSTATS TABLESPACE syntax and options in the Db2 for z/OS Knowledge Center to learn more.