Collecting statistics by using the OPT_STAT_ADVICE stored procedure

Call this stored procedure to get recommendations for the collection of statistics on tables in a DB2 11 for z/OS new-function mode subsystem. The stored procedure can either populate the SYSIBM.DSN_STAT_FEEDBACK catalog table with recommendations or update RUNSTATS profiles.

Before you begin

About this task

To collect statistics by using the OPT_STAT_ADVICE stored procedure, ensure prerequisites are met, set up the WLM Java environment, upload the .jar file that contains the stored procedure, upload a DBRM, and then customize and run a sample JCL job.

Procedure

  1. Set up the WLM Java environment, specifying a value for CLASSPATH that points to the location where you want to upload the stored procedure to. The stored procedure can be run on Java 1.5 or above.

    Here is an example of setting up the WLM Java environment:

    ENVAR("CLASSPATH=/u/admf001/aocstatssp.jar",
    "TZ=PST08",
    "JAVA_HOME=/usr/lpp/java160/J6.0",
    "WORK_DIR=/u/oeusr05/JavaSP",
    "JCC_HOME=/usr/lpp/db2/devbase/jdbc",
    "DB2_BASE=/usr/lpp/db2/devbase/base"),
    MSGFILE(JSPDEBUG),
    XPLINK(ON)
  2. Upload the load module jar file aocstatssp.jar.
    1. On your workstation, open a command window.
    2. Change to the folder \QueryTunerServerConfig\all_features\ZOS\SCA in the installation directory for the IBM Data Studio client.
    3. FTP to the subsystem.
    4. Change to the directory that the WLM CLASSPATH variable points to.
    5. Change to binary mode.
    6. Put the JAR file in the directory.
  3. Upload the DBRM AOC5OSCM to the subsystem. This DBRM is in the installation directory for the IBM Data Studio client at \QueryTunerServerConfig\all_features\ZOS\V11.
  4. Customize and run the AOCDDL11 sample JCL job. This sample job is in the installation directory for the IBM Data Studio client at \QueryTunerServerConfig\all_features\ZOS\V11.
    1. Customize the step AOCBIND. This step binds packages that InfoSphere Optim Query Workload Tuner uses.
    2. Customize the step AOCSP. This step creates the SYSPROC.OPT_STAT_ADVICE stored procedure. You can comment out the lines for creating the SYSPROC.OPT_RUNSQL and SYSPROC.OPT_EXECUTE_TASK stored procedures.
  5. Create and populate a table that lists the tables that you want recommendations for. This table can be a declared global temporary table (DGTT) or a regular table. It must contain two columns:
    Table 1. Columns of the table for listing the tables that you want recommendations for

    Column

    Data type

    Description

    CREATOR VARCHAR(128) The schema of a table that you want recommendations for.
    NAME VARCHAR(128) The name of the corresponding table.
  6. Ensure that the CALL statement supplies and accepts values for these parameters.
    Table 2. Parameters of the OPT_STAT_ADVICE stored procedure

    Mode

    Parameter

    Data Type

    Nullable? (Y/N)

    Description

    IN TABLECREATOR VARCHAR(128) Y Schema of the table that lists the tables for which you want recommendations for statistics collection .

    If the value is NULL, the stored procedure gets recommendation s for all of the tables , corresponding indexes, and columns across the entire connected subsystem.

    IN TABLENAME VARCHAR(128) Y Table that lists the tables for which you want recommendations for statistics collection .

    If TABLECREATOR is NULL, then this parameter must also be NULL.

    If TABLECREATOR is not NULL , but TABLENAME is NULL, the stored procedure returns an error message in the OUTPUT_MESSAGE parameter .

    IN OUTPUT_METHOD INTEGER N Output method for recommendations for statistics collection.

    Possible values:

    1: Populate the SYSIBM.DSN_STAT_FEEDBACK catalog table with in-memory statistics.

    2: Update RUNSTATS profiles directly.

    The DSNACCOX stored procedure detects changes to RUNSTATS profiles and then starts the collection of statistics according to a schedule that you can set.

    If you want to use the OPT_STAT_ADVICE stored procedure together with DSNACCOX, specify “2” as the value for the input parameter OUTPUT_METHOD when you call OPT_STAT_ADVICE. After this stored procedure updates RUNSTATS profiles, DSNACCOX will detect the updates and begin statistics collection according to its schedule.

    IN RESERVED INTEGER Y Reserved
    OUT RETURN_CODE INTEGER   Returned value of the stored procedure

    Possible values:

    0: Completed successfully.

    4: Completed with warnings. The warning messages are in OUTPUT_MESSAGE parameter.

    8: Failed with errors . The error message s are in the OUTPUT_MESSAGE parameter .

    OUT RESERVED BLOB(64k)  

    Reserved

    OUT OUTPUT_MESSAGE VARCHAR(4096)   Messages returned by the stored procedure.

    The value is NULL if the return code is 0 . Otherwise, the parameter lists error or warning message that were generated by the stored procedure.

Example

This example of calling the stored procedure uses a DGTT .

  1. Create a DGTT to contain a list of tables for which to generate recommendations for the collection of statistics .
    DECLARE GLOBAL TEMPORARY TABLE SESSION.STAT_COLL_TABLE (
            CREATOR VARCHAR(128),
            NAME VARCHAR(128)
    ) ON COMMIT PRESERVE ROWS;
  2. Populate the DGTT with data.
    INSERT INTO SESSION.STAT_COLL_TABLE (CREATOR, NAME) VALUES (…...);
  3. Call the stored procedure.
    CALL DB2OE.OPT_STAT_ADVICE (
       'SESSION',
       'STAT_COLL_TABLE', 
       '2',
        ?,
        ?,
        ?
    );

The sample JCL below shows how to call this interface with DSNTEP2:

//STEP01 EXEC TSOBATCH,DB2LEV=DB2A
//SYSTSIN DD *
DSN S(VA1A) R(1) T(1)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) PARM('NOMIXED')
END
//SYSIN DD *
  DECLARE GLOBAL TEMPORARY TABLE SESSION.STAT_COLL_TABLE (
        CREATOR VARCHAR(128),
        NAME VARCHAR(128)
   ) ON COMMIT PRESERVE ROWS;

  INSERT INTO SESSION.STAT_COLL_TABLE (CREATOR, NAME) VALUES (…...);

  CALL DB2OE.OPT_STAT_ADVICE (
   'SESSION', 'STAT_COLL_TABLE',
   '2',
   ?,
   ?,
   ?
  );

What to do next

If the stored procedure reports an error, define directories for the JavaOut and JavaErr parameters. Ensure that your authorization ID has the privilege to read and write to those directories. Then, start a trace. Call the stored procedure again. If the error occurs, collect the files that were generated in the JavaOut and JavaErr directories. Contact IBM Support and send the files according to the instructions that your support representative gives you.

Feedback