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.
Procedure
To configure a DB2 for z/OS subsystem
for query tuning from the server:
- 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.
- 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 |
- 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
- Enable the Java™ stored
procedure SYSPROC.OPT_EXECUTE_TASK:
- 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.
- Ensure that the IBM Data
Server Driver for JDBC and SQLJ is installed.
- Ensure that the environment variables for the IBM Data Server Driver for JDBC and SQLJ are
properly set.
- 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)
- 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.
- 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.
- 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.
- 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
- 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)
/*
- Copy the untersed load module to your user EXIT library.
- Ensure that the EXIT library is concatenated
to your DB2 start task STEPLIB.
- 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.
To learn more about how to configure and interact
with the administrative scheduler, see the following topics: