Running JCL to configure DB2 for z/OS subsystems for use with InfoSphere Optim Query Workload Tuner

You can configure your DB2® for z/OS® subsystem for query and query-workload tuning by submitting a JCL job.

Before you begin

Ensure that the IBM Data Studio full client, Version 3.1.1 is installed on a workstation that you have access to.

About this task

If your subsystem was configured for InfoSphere® Optim™ Query Workload Tuner, Version 3.1, then you do not need to follow the steps in this procedure.

For subsystems configured for earlier versions of InfoSphere Optim Query Tuner or InfoSphere Optim Query Workload Tuner, this procedure also describes how you can perform either of these two actions:
  • For versions 2.2.1.1 and earlier: Drop both the data objects from those earlier versions and the data in those objects
  • For version 2.2.1.1 and 2.2.1: Migrate the data objects from those earlier versions and keep the data in those objects. For the migration, you must have the required authorities and privileges.

Procedure

To configure a DB2 for z/OS subsystem for query tuning and query-workload tuning:

  1. Upload the required JCL job files and DBRMs to the subsystem from the client system.
    The JCL files and DBRMs are located inside the installation directory for the IBM® Data Studio full client.
    • If you installed the IBM Data Studio full 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 full 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, and Version 10 of DB2 for z/OS.
    Name of subfolder V9 V10CM9 V10
    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
  2. If your subsystem was configured for earlier versions of InfoSphere Optim Query Tuner or InfoSphere Optim Query Workload Tuner, and before configuring for version 3.1.1 you want to drop both the data objects from those earlier versions and the data in those objects, drop the following databases.
    Important: This step is not required. Follow it only if you are sure that you want to delete the data that is in these databases. If the subsystem is configured for versions 2.2.1 or 2.2.1.1, you can migrate the data objects to version 3.1.1 by following the steps here: Migrating data objects that were created for InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 2.2.1 or 2.2.1.1
    Option Description
    If your subsystem is configured for versions 2.2.1 or 2.2.1.1: Drop databases DB2OSC, DSNOSCDB, and AOCOEDB.
    If your subsystem is configured for versions that are earlier than 2.2.1: Drop databases DB2OSC, DSNOSCDB, and DB2OE.
  3. If your subsystem was configured for IBM Optimization Service Center, and before configuring for version 3.1.1 you want to drop both the data objects from those earlier versions and the data in those objects, drop databases DB2OSC and DSNOSCDB.
    Important: This step is not required. Follow it only if you are sure that you want to delete the data that is in these databases.
  4. Create the tables for Version 3.1.1. On the subsystem, modify the corresponding AOCDDL JCL job file to conform to your environment, then submit the job. The following table lists the AOCDDL JCL job files for each environment.
    DB2 subsystem JCL job to create the query tuning tables for Version 3.1.1
    DB2 Version 10 for z/OS new-function mode AOCDDL10
    DB2 Version 10 for z/OS conversion mode from Version 9 AOCDDLX9
    DB2 Version 10 for z/OS conversion mode from Version 8 AOCDDLX8
    DB2 Version 9 for z/OS AOCDDL9

Feedback