Enabling the SYSPROC.OPT_EXECUTE_TASK stored procedure on DB2 for z/OS subsystems

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:

  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, 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®.
  3. 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.
  4. 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.
  • One copy of this file is installed with the IBM Data Studio 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.
    Tip: 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/v31/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 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)