Query Tuner tables and views that are used for query-workload tuning are missing or are in an unknown format

Follow these steps when an error message requires you to create missing Query Tuner tables and views or to recreate them.

To follow these steps, you must be using InfoSphere® Optim™ Query Workload Tuner for DB2® for z/OS®, Version 4.1.1.

To create missing Query Tuner tables and views:

  1. In the Data Source Explorer, expand the connection to the subsystem.
  2. Right-click the subsystem and select Analyze and Tune > Configure for Tuning > Advanced Configuration and Privilege Management.
  3. Click Detailed Report.
  4. Review the DDL statements that are in the section DDL for missing tables.
  5. Save the DDL to a file and run it in the tool of your choice.

To recreate Query Tuner tables and views that are in an unknown format by using the IBM® Data Studio client:

  1. In the Data Source Explorer, expand the connection to the subsystem.
  2. Right-click the subsystem and select Analyze and Tune > Configure for Tuning > Advanced Configuration and Privilege Management.
  3. In the Query Tuner section, click Drop.
  4. After the tables are dropped, click Create.

To recreate Query Tuner tables and views that are in an unknown format by running JCL:

  1. Locate the JCL job file for the version of DB2 for z/OS that you are running.

    The JCL files are located in 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.
    Table 1. JCL files for the supported versions of DB2 fo z/OS
    Name of subfolder V9 V10CM9 V10 V11
    JCL file AOCDDL9 AOCDDLX9 AOCDDL10 AOCDDL11
  2. Upload the JCL job file to the subsystem from the client system in ASCII format.
  3. Comment out all of the job steps except for those listed here:
    //*    AOCSA     Step     Create QT SA profile objects
    //*    AOCAPC    Step     Create QT Plan Comparision objects
    //*    AOCWCC    Step     Create QT WCC objects
    //*    AOCWSA    Step     Create QT WSA objects
    //*    AOCWIA    Step     Create QT WIA objects
    //*    AOCWAPC   Step     Create QT WAPC objects
    …
    //*    AOCGRT    Step     Grant access on objects created by this job,
    //*                       add grant package
    //*    AOCGRT2   Step     Grant access on workload tables to individual
    //*                       authorization ID. Not required if secondary
    //*                       authorization ID is supported.
  4. Modify the JCL job file to conform to your environment, then submit the job.

Feedback