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. To learn
more about installing the driver, see the corresponding information
based on your subsystem version:
- Ensure that the environment variables for the IBM Data Server Driver for JDBC and SQLJ are
properly set. To learn more about setting the environment
variables, see the corresponding information based on your subsystem
version:
- Ensure that the properties for the IBM Data Server Driver for JDBC and SQLJ are
properly configured. To learn more about setting the driver
properties, see the corresponding information based on your subsystem
version:
- 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. To learn more about how to
set the WLM application environment, see the corresponding information
based on your subsystem version:
- 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)