To gather EXPLAIN information for a query workload from
the database server, you must enable the SYSPROC.OPT_EXECUTE_TASK
stored procedure.
Before you begin
- 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.
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:
- Upload the file aocwccsp.jar in binary
format to the subsystem. This file is installed with the
IBM Data Studio full client, Version 3.1.1 in the QueryTunerServerConfig\all_features\ZOS\WCC subdirectory
of the installation directory.
- Place the file in a location that is specified by CLASSPATH for
the WLM environment.