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:
-
From the QM web console, open the workload browser and, from the Application menu, select .
-
Select the workload containing the statements that you want to tune.
-
Click Tune.
This opens the Tune Workload dialog box
-
Select the following options:
-
Click OK.
-
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.
-
When the status of your tuning job reads Succeeded, click View Results.
-
From the View workload statements page, select your workload from the grid and then
click Host Variables.
-
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.
-
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.
-
Click View Revised Access Plan Graph to see how the access plan would change if the
selectivity profile were deployed.
-
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.
-
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.