Migrating data objects for query tuning on a DB2 for z/OS subsystem

If your subsystem was previously configured for query tuning with Optim Query Tuner for DB2 for z/OS or Optim Query Workload Tuner for DB2 for z/OS Version 2.2.1 or Version 2.2.1 fix pack 1, you must migrate the data objects to enable query tuning with Version 3.1.

Before you begin

You must have the authorities and privileges for migrating the EXPLAIN and Query Tuner tables.

About this task

To support table space and index name format changes that are introduced by Version 3.1, you must perform the migration in the following order on the subsystem:
  1. Unload data from the existing Version 2.2.1 or Version 2.2.1 fix pack 1 query tuning tables.
  2. Drop the databases that contain the existing Version 2.2.1 or Version 2.2.1 fix pack 1 query tuning tables.
  3. Create the databases and query tuning tables for Version 3.1.
  4. Load data to the query tuning tables for Version 3.1.

Procedure

To migrate the data objects for query tuning on a subsystem:

  1. If you did not perform the SMP/E tape installation to extract the files for migrating the data objects for query tuning, upload the required JCL job files to the subsystem from the client system.
    Tip: The files are installed with the InfoSphere Optim™ Query Tuner client. You can find the AOCM JCL files in the \serverconfig\ZOS\migration subdirectory of the installation directory. You can find the AOCDDL JCL files in the \serverconfig\ZOS\z/OS_version_number_and_mode subdirectory of the installation directory. The default installation directory is C:\Program Files\IBM\OQT3.1.
  2. On the subsystem, modify and run the corresponding migration JCL jobs based on your environment.
    1. 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
    2. Modify the corresponding AOCDDL JCL job file to conform to your environment, then submit the job. The JCL job drops the databases that contain the existing Version 2.2.1 or Version 2.2.1 fix pack 1 query tuning tables. The job also creates the databases and query tuning tables for Version 3.1. The following table lists the AOCDDL JCL job files for each environment.
      DB2 subsystem JCL job to drop existing databases and create new Version 3.1 databases and query tuning tables
      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
      DB2 for z/OS Version 8 new-function mode AOCDDL8N
    3. Modify and submit the corresponding AOCM2F JCL job to load data to the query tuning tables for Version 3.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

What to do next

To start tuning SQL statements:
  1. Open the InfoSphere Optim Query Tuner client.
  2. In the Data Source Explorer, connect to the subsystem.
  3. Right-click the connection and click Analyze and Tune > Start Tuning.

Feedback