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
- 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 tuning features is activated. Step 8 in this checklist describes how to do this activation.
- Ensure that the IBM Data Studio client, Version 4.1.1 is installed on your workstation. Files
that you will upload as part of the procedure below are located within the client’s installation
directory.
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
- 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 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 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.
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 .
- 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.