Use the Data Source Explorer to gather RUNSTATS recommendations directly from subsystems,
schemas, or tables.
Before you begin
- Ensure that the ADMIN_COMMAND_DB2 stored procedure is installed and that the EXECUTE privilege
is given to the IDs that will call the OPT_STAT_ADVICE stored procedure.
- Ensure that the DSNUTILU stored procedure is installed and that the EXECUTE privilege is given
to the IDs that will call the OPT_STAT_ADVICE stored procedure.
- Ensure that the zParm STATFDBK_SCOPE is set to one of these values: DYNAMIC,
STATIC, or ALL.
- In the Data Source Explorer, connect to a subsystem that is managed by DB2 11 for z/OS
new-function mode or later.
- Verify that the full set of tuning features for InfoSphere Optim Query
Workload Tuner for DB2 for z/OS is activated on subsystem.
- Ensure that the stored procedure SYSPROC.OPT_STAT_ADVICE is created on the subsystem. This
stored procedure is created by the AOCSP job step in the JCL for configuring the subsystem for
InfoSphere Optim Query Workload Tuner.
- Ensure that the table DSN_STAT_FEEDBACK exists in the set of EXPLAIN tables that your
authorization ID has access to.
- Ensure that your authorization ID is granted the EXECUTE privilege on the package AO5OSCM, which
is bound during the job step AOCBIND in the JCL for configuring the subsystem for InfoSphere Optim
Query Workload Tuner. If you need access to the package, the person with the authority to grant you
access can grant it by using the Data Studio client. These are the steps:
- In the Data Source Explorer, right-click the subsystem and select .
- Follow the onscreen instructions to grant access to the Statistics Collection Advisor.
Procedure
- After connecting to the subsystem in the Data Source Explorer, expand the connection to view
the subsystem icon.
- Generate RUNSTATS recommendations by following one of these steps:
| Option |
Description |
| To generate RUNSTATS recommendations for collecting statistics for all tables that are in
the subsystem |
Right-click the subsystem icon and select . |
| To generate RUNSTATS recommendations for collecting statistics for all tables that are in
one or more schemas |
Expand the subsystem until you find the schema or schemas that you want. Select them,
right-click them, and select . |
| To generate RUNSTATS recommendations for collecting statistics for one or more
tables |
Expand the subsystem until you find the table or tables that you want. Select them,
right-click them, and select . |
Results
The RUNSTATS recommendations are generated and the
Collect Statistics
window opens. You can choose to whether to remove RUNSTATS recommendations for any objects and then
run the remaining recommendations.