You can create EXPLAIN tables, create Query Tuner tables,
and activate the product license by customizing and submitting JCL
jobs.
About this task
If your subsystem was configured
for InfoSphere Optim Query Workload Tuner, Version
3.1 or 3.1.1, then you do not need to follow the steps in this procedure.
Procedure
To create EXPLAIN tables, create Query Tuner tables,
and activate the product license:
- Upload the required JCL job files and DBRMs to the subsystem
from the client system.
The JCL files and DBRMs are inside the installation directory for the IBM® Data Studio client.
- If you installed the IBM Data Studio client on a Windows system, the JCL is in the
installation_directory\QueryTunerServerConfig\all_features\ZOS\version_number_and_mode
directory.
- If you installed the IBM Data Studio client on a Linux system, the JCL is in the
.installation_directory/QueryTunerServerConfig/all_features/ZOS/version_number_and_mode
directory.
Upload the JCL files in ASCII format and the DBRMs in
binary format.
Table 1. JCL and DBRMs for Version 9.1, Version 10 conversion mode from Version 9.1, Version 10 new
function mode, Version 11, and Version 11 conversion mode of DB2 for z/OS| Name of subfolder |
V9 |
V10CM9 |
V10 |
V11 |
V11 CM |
| List of files |
JCL: AOCDDL9
DBRMs: AOC5OADM
AOC5OAPC AOC5OEPK AOC5OEXK AOC5OFMJ
AOC5OIA1 AOC5OIA2 AOC5OIA3 AOC5OIA4
AOC5OIA5 AOC5OIA6 AOC5OIA7 AOC5OIA8
AOC5OIA9 AOC5OIAA AOC5OIAK AOC5OIAL
AOC5ONPT AOC5OPKG AOC5OQA AOC5OQIA
AOC5OSA AOC5OWCK AOC5OWQA AOC5OWSA
AOC5OWSK |
JCL: AOCDDLX9
DBRMs: AOC5OADM
AOC5OAPC AOC5OEPK AOC5OEXK AOC5OFMM
AOC5OIA1 AOC5OIA2 AOC5OIA3 AOC5OIA4
AOC5OIA5 AOC5OIA6 AOC5OIA7 AOC5OIA8
AOC5OIA9 AOC5OIAA AOC5OIAK AOC5OIAL
AOC5ONPT AOC5OPKG AOC5OQA AOC5OQIA
AOC5OSA AOC5OWCK AOC5OWQA AOC5OWSA
AOC5OWSK |
JCL: AOCDDL10
DBRMs: AOC5OADM
AOC5OAPC AOC5OEPK AOC5OEXM AOC5OFMM
AOC5OIA1 AOC5OIA2 AOC5OIA3 AOC5OIA4
AOC5OIA5 AOC5OIA6 AOC5OIA7 AOC5OIA8
AOC5OIA9 AOC5OIAA AOC5OIAK AOC5OIAL
AOC5ONPT AOC5OPKG AOC5OQA AOC5OQIA
AOC5OSA AOC5OWCM AOC5OWQA AOC5OWSA
AOC5OWSM |
JCL: AOCDDL11
DBRMs: AOC5OADM
AOC5OAPC AOC5OEPL AOC5OEXM
AOC5OFMM AOC5OIA1 AOC5OIA2 AOC5OIA3 AOC5OIA4 AOC5OIA5 AOC5OIA7 AOC5OIA8 AOC5OIAA AOC5OIAK AOC5OIAL AOC5ONPT AOC5OPKG AOC5OQA AOC5OQIA AOC5OSA AOC5OSCM AOC5OWCC AOC5OWCM AOC5OWSA AOC5OWSM |
JCL: AOCDDLBC
DBRMs: AOC5OADM AOC5OEPL
AOC5OEXM AOC5OFMM AOC5OIA1 AOC5OIA2
AOC5OIA3 AOC5OIA4 AOC5OIA5 AOC5OIA7
AOC5OIA8 AOC5OIAA AOC5OIAK AOC5OIAL
AOC5ONPT AOC5OPKG AOC5OQA AOC5OQIA
AOC5OSA AOC5OWCC AOC5OWCN AOC5OWSA AOC5OWSM
AOC5OWTA |
- Create the objects for InfoSphere Optim Query Workload Tuner. Version 4.1.0.1.
- Modify the corresponding AOCDDL JCL job file to conform to your environment.
- Optional: AOCICU
- This step drops the database in which InfoSphere Optim Query Workload Tuner creates the objects
that it requires. Do not drop the database if you tuned query workloads and want to keep the
information that InfoSphere Optim Query Workload Tuner stored about them.
- Required: AOCIAS
- This step creates the database in which InfoSphere Optim Query Workload Tuner creates the
objects that it requires.
- Required: AOCSA
- This step defines a table that allows users to save RUNSTATS statements that are recommended by
the Statistics Advisor and Workload Statistics Advisor. Users can retrieve saved RUNSTATS
recommendations when they need to do so.
- Required: AOCWCC
- This step creates objects that are used to store information about query workloads.
- Required: AOCWSA
- This step creates objects that are used by the Workload Statistics Advisor.
- Required: AOCWIA
- This step creates objects that are required by the Workload Index Advisor.
- Required: AOCWAPC
- This step creates objects that are required for comparisons of query workloads.
- Required: AOCSCA
- This step creates objects that are required by the Statistics Collection Advisor, which runs on
DB2 for z/OS, Version 11 or later.
- Required: AOCSP
- This step creates optional stored procedures.
- SYSPROC.OPT_EXECUTE_TASK
- For information about how this stored procedure is used, see Enabling the
SYSPROC.OPT_EXECUTE_TASK stored procedure on DB2 for z/OS subsystems.
- SYSPROC.OPT_RUNSQL
- For information about how this stored procedure is used, see Enabling the
SYSPROC.OPT_RUNSQL stored procedure.
- SYSPROC.OPT_STAT_ADVICE
- Call this stored procedure from an application to get recommendations for the collection of
statistics on tables in a DB2 Version 11 for z/OS subsystem. The stored procedure can either
populate the SYSIBM.DSN_STAT_FEEDBACK catalog table with recommendations or update RUNSTATS
profiles.
- Required: AOCBIND
- This step binds packages that the components of InfoSphere Optim Query Workload Tuner use to
access database objects. For a list of the packages, see Packages that are required to be bound on DB2 for z/OS subsystems that are used with
InfoSphere Optim Query Workload Tuner.
- Required if you are using RACF: AOCGRT
- This step grants access to the packages that are created in the AOCBIND step.
- Required if you are not using RACF: AOCGRT2
- Uncomment the job step AOCGRT2 if you do not want to create RACF groups for InfoSphere Optim
Query Workload Tuner to use a secondary authorization IDs. The product can use the RACF IDs to
explain tables and perform other tasks. If you uncomment this job step, you must supply
authorization IDs to use in place of RACF IDs. These authorization IDs must be the same IDs with
which users of the product will connect to the subsystem from the Data Studio client.
Also, if you uncomment this job step, individual users in the Data Studio client must unselect an
option in the Data Studio client before attempting to tune SQL that runs on the subsystem. After
starting the Data Studio client, each users must select Window > Preferences. Then, expand Data
Management and select Query Tuner. The preference to deselect is labelled Allow internal processes
to use the RACF IDs DB2OE and DB2OSCA.
- Required if you run job step AOCSP: AOCTJRT
- This step creates the data set that contains the LE environment options for the WLM environment.
This environment is used to execute the stored procedures SYSPROC.OPT_EXECUTE_TASK and
SYSPROC.OPT_STAT_ADVICE
- Optional: AOCEXPCU
- If you want to drop a set of EXPLAIN tables that are associated with a particular SQLID, you can
use this step to do so.
- Optional: AOCEXP
- This step drops sets of EXPLAIN tables that are associated with SQLIDs that you specify.
- Optional: AOCCMT
- This step saves values in the DB2 catalog information such as the names of buffer pools, storage
groups, and databases. If another person wants to create a set of EXPLAIN tables by using InfoSphere
Optim Query Workload Tuner's graphical interface in the IBM Data Studio client, the interface
populates various fields with these values.
- Optional: Uncomment the job step AOCGRT2 if you do not want to create RACF groups for InfoSphere Optim
Query Workload Tuner to use a secondary authorization IDs. The product can use the RACF IDs to explain tables and perform other tasks. If you uncomment
this job step, you must supply authorization IDs to use in place of RACF IDs. These authorization
IDs must be the same IDs with which users of the product will connect to the subsystem from the Data
Studio client.
Also, if you uncomment this job step, individual users in the Data Studio client
must unselect an option in the Data Studio client before attempting to tune SQL that runs on the
subsystem. After starting the Data Studio client, each users must select . Then, expand Data Management and select Query
Tuner. The preference to deselect is labelled Allow internal processes to use
the RACF IDs DB2OE and DB2OSCA.
- Submit the job.
- If you did not comment the job step AOCGRT2 before running
the AOCDDL job, create the following two RACF groups and link your
users to them.
- Activate the license for InfoSphere Optim Query Workload
Tuner on the subsystem.
- Ensure that the authorization ID that you are using
to connect to the subsystem has one of the following authorities or
privileges that are required for activating the product license.
- DB2 Version 9.1 for z/OS:
- CREATEIN privilege on the schema DB2OE (if you created the DB2OE
RACF group in step 3)
- SYSADM or SYSCTRL authority
- DB2 10 for z/OS and later:
- CREATEIN privilege on the schema DB2OE (if you created the DB2OE
RACF group in step 3)
- SYSADM, SYSCTRL, or DBADM authority
- Upload the following file to the subsystem: qwt_license.jcl This file is in the QueryTunerServerConfig\all_features\ZOS\License\ folder
in the installation directory for the IBM Data
Studio client.
- Replace the variables $$DSN and $$USER with
values for your system environment and submit the job.