Call this stored procedure to get recommendations for the collection of statistics on
tables in a DB2 Version 11 for z/OS subsystem. The stored procedure can either populate the
SYSIBM.DSN_STAT_FEEDBACK catalog table with recommendations or update RUNSTATS profiles.
Before you begin
- Ensure that the ADMIN_COMMAND_DB2 stored procedure is installed and that the EXECUTE privilege
is given to the IDs that will call the OPT_STAT_ADVICE stored procedure
- Ensure that the DSNUTILU stored procedure is installed and that the EXECUTE privilege is given
to the IDs that will call the OPT_STAT_ADVICE stored procedure
- Ensure that the full set of IOQWT features is activated. Step 8 in this checklist describes how to do this activation.
- Ensure that the IBM Data Studio client, Version 4.1.0.1 is installed on your workstation. The files
that you must upload are located within the client’s installation directory.
About this task
To set up the stored procedure, you need to 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
- 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)
- Upload the load module jar file aocstatssp.jar.
- On your workstation, open a command window.
- Change to the folder \QueryTunerServerConfig\all_features\ZOS\SCA in the
installation directory for the IBM Data Studio client.
- FTP to the subsystem.
- Change to the directory that the WLM CLASSPATH variable points to.
- Change to binary mode.
- Put the JAR file in the directory.
- Upload the DBRM AOC5OSCM to the subsystem. This DBRM is located in the installation directory
for the IBM Data Studio client at
\QueryTunerServerConfig\all_features\ZOS\V11.
- Customize and run the AOCDDL11 sample JCL job. This sample job is located in the installation
directory for the IBM Data Studio client at
\QueryTunerServerConfig\all_features\ZOS\V11.
- Customize the step AOCBIND. This step binds packages that InfoSphere Optim
Query Workload Tuner uses.
- 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.
- 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 forColumn
|
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. |
- 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.
Note: 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 kick of 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 s uccessful
ly.
4: Completed with warnings. The warning messages are in OUTPUT_MESSAGE parameter.
8:
Fail ed with error s . 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 . O
therwise, the parameter lists error or warning message that were generated by the stored
procedure.
|
Example
This example uses a DGTT .
- 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;
- Populate the DGTT with
data.
INSERT INTO SESSION.STAT_COLL_TABLE (CREATOR, NAME) VALUES (…...);
- 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.