Using the IBM Data Studio client to configure DB2 for z/OS subsystems for use with InfoSphere Optim Query Workload Tuner

You can connect to a DB2® for z/OS® subsystem from the IBM® Data Studio full client and configure the subsystem for query and query-workload tuning by using a wizard.

Before you begin

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.

The procedure below explains how to use the IBM Data Studio full client, Version 3.1.1 to create the data objects that are required for query tuning and query-workload tuning with IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1.

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. If you want to drop data objects that were created for earlier versions of InfoSphere Optim Query Tuner or InfoSphere Optim Query Workload Tuner and you want to delete the data that is 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: DB2OSC
    DSNOSCDB
    AOCOEDB
    If your subsystem is configured for versions that are earlier than 2.2.1: DB2OSC
    DSNOSCDB
    DB2OE
  2. If your subsystem was configured for IBM Optimization 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.
  3. If you want to migrate the data objects that were created for version 2.2.1 or 2.2.1.1, follow these steps:
    1. Upload the required JCL job files to the subsystem from the client system.

      The JCL files to upload are listed in step b below and in step 5. The JCL files are installed with the IBM Data Studio client in the \QueryTunerServerConfig\ZOS\migration subdirectory of the installation directory. The default installation directory is C:\Program Files\IBM\DS3.1.1.

    2. Modify and submit the corresponding AOCM2B JCL job to unload data from the existing Version 2.2.1 or Version 2.2.1 fix pack 1 query tuning tables. The following table lists the AOCM2B JCL job files for each environment.
      DB2 subsystem JCL job to unload data from query tuning tables
      DB2 for z/OS Version 8 new-function mode subsystem AOCM2B8N
      DB2 Version 9 for z/OS subsystem AOCM2B9
      DB2 Version 10 for z/OS conversion mode from DB2 for z/OS Version 8 subsystem AOCM2BX8
      DB2 Version 10 for z/OS conversion mode from DB2 Version 9 for z/OS subsystem AOCM2BX9
      DB2 Version 10 for z/OS subsystem AOCM2BXN
    3. Drop the DB2OSC, DSNOSCDB, and AOCOEDB databases.
  4. In the Data Source Explorer in the IBM Data Studio client, expand the connection to the subsystem. Right-click the icon for the subsystem for the subsystem and select Analyze and Tune > Configure for Tuning > Guided Configuration. The configuration wizard opens.

    If the license for InfoSphere Optim Query Workload Tuner is not yet active on the subsystem by this point, the Data Studio full client installs the license on the subsystem and activates the license, if the license activation kit is installed in the directory where the Data Studio full client is installed on your workstation. The configuration wizard allows you to configure the subsystem for query tuning and query-workload tuning.

  5. Follow the instructions in the wizard.
  6. If you are migrating the data objects that were created for version 2.2.1 or 2.2.1.1, modify and submit the corresponding AOCM2F JCL job to load data to the query tuning tables for Version 3.1.1. The following table lists the AOCM2F JCL job files for each environment.
    DB2 subsystem JCL job to load data to the query tuning tables
    DB2 for z/OS Version 8 new-function mode subsystem AOCM2F8N
    DB2 Version 9 for z/OS subsystem AOCM2F9
    DB2 Version 10 for z/OS conversion mode from DB2 for z/OS Version 8 subsystem AOCM2FX8
    DB2 Version 10 for z/OS conversion mode from DB2 Version 9 for z/OS subsystem AOCM2FX9
    DB2 Version 10 for z/OS subsystem AOCM2FXN
  7. Verify that the subsystem is configured for query or query-workload tuning. In the Data Source Explorer, right-click the subsystem that you configured, and then click Analyze and Tune > Configure for Tuning > Advanced Configuration and Privilege Management.

    You can check the status of the Query Tuner packages, Query Tuner tables, EXPLAIN tables, and the query or query-workload tuning features in this window.

What to do next

To start tuning in the Query Tuner Workflow Assistant, right-click the connection in the Data Source Explorer, then click Analyze and Tune > Start Tuning.