Running JCL to create EXPLAIN tables, create Query Tuner tables, and activate the product license

You can create EXPLAIN tables, create Query Tuner tables, and activate the product license by customizing and submitting JCL jobs.

Before you begin

Follow steps 1 through 9 in Checklist for configuring DB2 for z/OS for use with InfoSphere Optim Query Workload Tuner.

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:

  1. 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 new-function mode, and Version 11 conversion mode of DB2 for z/OS
    Name of subfolder V9 V10CM9 V10 V11 NFM 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
  2. Create the objects for InfoSphere Optim Query Workload Tuner. Version 4.1.1.
    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 new-function mode 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 as secondary authorization IDs. 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 user must select Window > Preferences. Then, expand Data Management and select Query Tuner. The preference to deselect is labeled 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.
    2. Submit the job.
  3. 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.
    • DB2OSCA
    • DB2OE
  4. Activate the license for InfoSphere Optim Query Workload Tuner on the subsystem.
    1. 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
    2. 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.
    3. Replace the variables $$DSN and $$USER with values for your system environment and submit the job.

What to do next

Return to Checklist for configuring DB2 for z/OS for use with InfoSphere Optim Query Workload Tuner and continue at step 12.

Feedback