Configuring a DB2 for z/OS subsystem for the InfoSphere Optim Query Tuner client from the server

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

Before you begin

Procedure

To configure a DB2 for z/OS subsystem for query tuning from the server:

  1. If you did not perform the SMP/E tape installation to extract the files for configuring the subsystem, upload the required JCL job files to the subsystem from the client system.
    Tip: The AOCDDL files are installed with the InfoSphere Optim™ Query Tuner client. You can find the 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. If you did not migrate from a previous version of the Optim Query Tuner products, you must create the query tuning tables for Version 3.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
    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
    DB2 for z/OS Version 8 compatibility mode AOCDDL8C
  3. Optional: Enable the SYSPROC.OPT_EXECUTE_TASK stored procedure to gather EXPLAIN information for a query workload from the database server.
  4. Optional: Enable the SYSPROC.OPT_RUNSQL stored procedure to capture query workloads from statement caches in the database server.
  5. Optional: Set up the administrative scheduler.
    You can use the administrative scheduler to perform tasks on query workloads, such as workload capture and monitoring.
    Restriction: Without the administrative scheduler, you can schedule tasks, but you must keep the client application running on your workstation and connected to the DB2 for z/OS subsystem for the operations to run when scheduled. If the connection to the subsystem is dropped while scheduled tasks are running, those tasks cannot complete.
  6. If you upgrade your DB2 subsystem to a higher version, you must migrate the data objects in the query tuner tables to handle the DB2 subsystem version-to-version upgrade changes. To migrate, modify and submit the corresponding AOCTIJ JCL job. The following table lists the AOCTIJ JCL job files for each environment.
    Migrating from Migrating to JCL job for migrating data objects for query tuning
    DB2 for z/OS Version 8 new-function mode DB2 Version 10 for z/OS conversion mode from DB2 for z/OS Version 8 AOCTIJC8
    DB2 Version 10 for z/OS conversion mode from DB2 for z/OS Version 8 DB2 Version 10 for z/OS new-function mode AOCTIJ8X
    DB2 Version 9 for z/OS DB2 Version 10 for z/OS conversion mode from DB2 Version 9 for z/OS AOCTIJC9
    DB2 Version 10 for z/OS conversion mode from DB2 Version 9 for z/OS DB2 Version 10 for z/OS new-function mode AOCTIJ9X

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