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

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. To learn more about installing the driver, see the corresponding information based on your subsystem version:
  3. 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:
  4. 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:
  5. 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:
  6. 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 InfoSphere 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.
    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)

Feedback