To gather EXPLAIN information for a query workload from
the database server, you must enable the SYSPROC.OPT_EXECUTE_TASK
stored procedure.
About this task
By enabling the SYSPROC.OPT_EXECUTE_TASK stored procedure,
you can gather and consolidate EXPLAIN information for a query workload
from the database server. The stored procedure must also be enabled
to consolidate literal values in EXPLAIN information.
Procedure
To 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, the environment variables
are correctly set, and the properties are correctly configured. See the section "Installing the IBM Data
Server Driver for JDBC and SQLJ as part of a DB2® installation" in DB2 for z/OS Application
Programming Guide and Reference for Java for
your version of DB2 for z/OS for your version of DB2 for z/OS®.
- 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. See the section "Setting
up the WLM application environment for Java routines"
in DB2 for z/OS Application Programming Guide and Reference
for Java for your version of DB2 for z/OS for your version of DB2 for z/OS.
- 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.
Example
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.
JCC_HOME is the home directory
of the JDBC driver, and
JAVA_HOME is the home
directory of the Java. You must
change the value of
JCC_HOME and
JAVA_HOME,
depending on the location of the JDBC driver and runtime environment.
You must also 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)