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

If your subsystem was not configured for any previous version of the Optim™ Query Tuner client, you can run an AOCDDL JCL job on the DB2® for z/OS® subsystem to configure the subsystem for the current version of the Optim Query Tuner client.

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 DBRM and JCL job files to the subsystem from the client system. The files are installed with the 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\OQT2.2.1.
    Note: Use binary format when you upload these DBRM files to the subsystem.
  2. On the subsystem, modify the corresponding AOCDDL JCL job file in the following table to conform to your environment, and submit the job to install the DBRM files and create EXPLAIN tables and Query Tuner tables.
    Subsystem JCL job file
    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.1 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 and SYSPROC.OPT_RUNSQL stored procedures so that the following workload tuning tasks can be performed from the server:
    • Capture workloads from statement caches
    • Gather EXPLAIN information for a workload
    • Consolidate EXPLAIN information for a workload
    • Consolidate literal values in EXPLAIN information
    1. Enable the Java™ stored procedure SYSPROC.OPT_EXECUTE_TASK:
      1. Ensure that the IBM® SDK for Java 2 Technology Edition, Version 1.4 is installed in the UNIX® System Service folder that is specified by the JAVA_HOME environment variable.
      2. Ensure that the IBM Data Server Driver for JDBC and SQLJ is installed.
      3. Ensure that the environment variables for the IBM Data Server Driver for JDBC and SQLJ are properly set.
      4. Ensure that the properties for the IBM Data Server Driver for JDBC and SQLJ are properly configured.
        In the following sample, aocwccsp.jar is the Java stored procedure JAR file. This file is installed in two locations in the product. You can use either copy. They are identical to each other.
        • One copy of this file is installed with the Optim Query Tuner client. You can find this file in the \serverconfig\WCC subdirectory of the installation directory. If you use this copy, you must upload it to the location that is specified by CLASSPATH. Use binary format when you upload this file to the subsystem.
        • The other copy of this file is installed on the subsystem during the SMP/E installation. The HFS path is /usr/lpp/oqt/v221/lib/IBM. If you use this copy, place a copy in the location that is specified by CLASSPATH.
        JCC_HOME is the home directory of the JDBC driver, and JAVA_HOME is the home directory of the Java. You need to change the value of JCC_HOME and JAVA_HOME, depending on the location of the JDBC driver and runtime environment. You also need to change the value of TZ, which represents the time zone, to your local time zone, for example:
        • PST08 for Pacific Standard Time.
        • MST07 for Mountain Standard Time.
        • CST06 for Central Standard Time.
        • CET-01 for Central European Time.
        ENVAR("CLASSPATH=/usr/lpp/db2910_base/classes/aocwccsp.jar",
        "TZ=PST08",                                                 
        "JCC_HOME=/usr/lpp/db2910_jdbc/",                           
        "JAVA_HOME=/usr/lpp/java140/J1.4"),                         
        MSGFILE(JSPDEBUG,,,,ENQ),                                   
        XPLINK(ON)
      5. Ensure that a WLM environment is set up for Java routines, and a started-task job for Java stored procedures exists in the system procedure library.
      6. Ensure that users have permission to write to the temporary z/OS UNIX directory. The SYSPROC.OPT_EXECUTE_TASK stored procedure writes log files to this directory.
    2. Enable the C stored procedure SYSPROC.OPT_RUNSQL:

      The SYSPROC.OPT_RUNSQL stored procedure is used to capture the SQL statements from the dynamic statement caches and stored these statements in the DSN_STATEMENT_CACHE_TABLE table.

      1. Upload the tersed load module to the subsystem. You can find the load module in the \serverconfig\ZOS\z/OS_version_number_and_mode\Load Module subdirectory of the installation directory. Ensure that the following transfer rules are specified.
        ftp> quote site blk=6144 lrecl=1024 recfm=fb tracks unit=sysallda primary=90
        200 SITE command was accepted
        ftp> binary
        200 Representation type is Image
      2. Use the following sample job to unterse the load module to a partition data set.
        //***************************************************************
        //* Notes =
        //*  PRIOR TO RUNNING THIS JOB, 
        //*  locate and change the string "!!" to the following suffix
        //*  for the different versions of DB2 for z/OS:
        //*  For V8 and V10CM8, change to "J". 
        //*  For V9 and V10CM9, change to "K".
        //*  For V10NFM, change to "M".
        //***************************************************************
        //UNTERSE    JOB CLASS=A,MSGLEVEL=(1,1),                        
        //        NOTIFY=&SYSUID                                        
        //UNTERSE     EXEC PGM=TRSMAIN,PARM='UNPACK'                      
        //STEPLIB   DD DISP=SHR,DSN=IBMUSER.TERSE.LOADLIB               
        //SYSPRINT  DD SYSOUT=*,DCB=(LRECL=1024,BLKSIZE=6144,RECFM=FB)  
        //INFILE    DD DISP=SHR,DSN=USER.TERSED.AOCRNSQ!!                
        //OUTFILE   DD DSN=USER.UNTERSED,                                
        //  DISP=(SHR,CATLG,DELETE),                                    
        //  UNIT=3390,VOL=SER=DK8320,SPACE=(CYL,(5,5),RLSE)             
        /*   
      3. Copy the untersed load module to your user EXIT library.
      4. Ensure that the EXIT library is concatenated to your DB2 start task STEPLIB.
  4. Optional: Set up the administrative scheduler.

    You can use the administrative scheduler to perform tasks on query workloads, such as workload capture and monitoring. Without 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.


Feedback