ADMIN_INFO_SQL stored procedure

Environment

ADMIN_INFO_SQL must run in a WLM-established stored procedures address space, where NUMTCB is a value in the range 40–60.

If you collect information by using PLAN_TABLE, ensure that the DSN_VIEWREF_TABLE table exists before you capture EXPLAIN information. Especially if the query contains a view, the availability of DSN_VIEWREF_TABLE helps to narrow the view so that it is specific to the query, rather than collecting all of the view dependencies. DSN_VIEWREF_TABLE must have the same qualifier as the PLAN_TABLE. This qualifier is the table-creator value, which is the first input parameter.

Authorization

To execute the CALL statement, the owner of the package or plan that contains the CALL statement must have one or more of the following privileges:

• The EXECUTE privilege on the ADMIN_INFO_SQL stored procedure
• Ownership of the stored procedure
• SYSADM authority

Optionally, you need authority to create data sets, or access existing data sets, if the information is collected into a data set.

Option descriptions

table-creator

Specifies the explicit qualifier for the object, the list of tables, or the plan table (PLAN_TABLE).

This is an input parameter of type VARCHAR(128) and cannot be null.

table-name

Specifies the name of a single user object, a list of objects, or the plan table (PLAN_TABLE).

Valid options are an object name, LIST_TABLE-table, or PLAN_TABLE. The table portion of the LIST_TABLE-table option is the name of a Db2 table that contains two columns, CREATOR and TABLE. These two columns drive the program to collect information. The PLAN_TABLE option uses the CREATOR and TNAME columns of the PLAN_TABLE to collect information. This option depends on the plan_info parameter to qualify tables as input. The reference point to the plan table must point to a base table only.

When you specify PLAN_TABLE, ensure that the DSN_VIEWREF_TABLE table exists before you execute EXPLAIN. The availability of DSN_VIEWREF_TABLE minimizes the size of the DDL information. DSN_VIEWREF_TABLE must have the same qualifier as the plan table. This qualifier is the table-creator value, which is the first input parameter.

The input for a single user object or a list of objects must be one of the following types of objects:

• Base table
• View
• Alias
• Clone table
• Created temporary table
• History table
• Materialized query table
• Implicitly created table for an XML column

The input parameter table-creator must be the qualifier for these tables.

This is an input parameter of type VARCHAR(128).

catalog-creator

Specifies the catalog to use for collection. The default catalog is SYSIBM. To use the default catalog, you can specify DEFAULT or SYSIBM.

This is an input parameter of type VARCHAR(128) and cannot be null.

plan-info

If the table that is specified by table-name is PLAN_TABLE, plan-info is a value of the following form, or NONE:

program-begin-queryno-end-queryno

The meanings of these variables are:

program

A value from the PROGNAME column of the PLAN_TABLE, or a pattern that specifies a set of PROGNAME values in the PLAN_TABLE. Any pattern that is valid in a LIKE predicate can be specified. program represents programs or packages for which ADMIN_INFO_SQL collects PLAN_TABLE information.

begin-queryno

A value from the QUERYNO column of the PLAN_TABLE. The value represents the lowest statement number for which ADMIN_INFO_SQL collects PLAN_TABLE information.

end-queryno

A value from the QUERYNO column of the PLAN_TABLE. The value represents the highest statement number for which ADMIN_INFO_SQL collects PLAN_TABLE information.

If table-name does not specify PLAN_TABLE, the plan-info value must be NONE.

This is an input parameter of type VARCHAR(150) and cannot be null.

collect-ddl

Specifies whether to collect DDL information. Valid values are Y, N, 0 (zero), 1, 2, 3, or 4.

This is an input parameter of type CHAR(1) and cannot be null.

When the input table is not PLAN_TABLE, possible values are:

N

Do not return the data definition language statements that created the objects.

Y

Return the data definition language statements that created:

• The input objects
• Foreign keys that reference the input objects
• Views on the input objects

0

Return the data definition language statements that created:

• The input objects. Statements that create views on the input objects or foreign keys that reference the input objects are not collected.

1

Return the data definition language statements that created:

• The input objects
• Views on the input objects

2

Return the data definition language statements that created:

• The input objects
• Foreign keys that reference the input objects

3

Return the data definition language statements that created:

• The input objects
• Foreign keys that reference the input objects
• Views on the input objects
• Other objects that depend on the input objects, such as materialized query tables

This option can result in a large amount of data. Do no specify this option for problem analysis except at the direction of IBM Support.

4

Return the same data definition language statements that are returned when option Y is specified.

When the input table is PLAN_TABLE, possible values are:

N

Do not return the data definition language statements that created the objects.

Y

Return the data definition language statements that created:

• The objects that are identified by plan-info
• Foreign keys that reference the objects that are identified by plan-info
• If DSN_VIEWREF_TABLE exists and is populated, views or materialized query tables that are used to process the queries that are identified by plan-info.
• If DSN_VIEWREF_TABLE does not exist, views on objects that are identified by plan-info.

0

Return the data definition language statements that created:

• The objects that are identified by plan-info only. Statements that create views on the objects or foreign keys that reference the objects that are identified by plan-info are not collected.

1

Return the data definition language statements that created:

• The objects that are identified by plan-info
• If DSN_VIEWREF_TABLE exists and is populated, views or materialized query tables that are used to process the queries that are identified by plan-info.
• If DSN_VIEWREF_TABLE does not exist, views on objects that are identified by plan-info.

2

Return the data definition language statements that created:

• Foreign keys that reference the objects that are identified by plan-info

3

Return the data definition language statements that created:

• The objects that are identified by plan-info
• Foreign keys that reference the objects that are identified by plan-info
• Views on objects that are identified by plan-info
• Other objects that depend on the objects that are identified by plan-info, such as materialized query tables

This option can result in a large amount of data. Do no specify this option for problem analysis by IBM Support unless they direct you to do so.

4

Return the data definition language statements that created:

• The objects that are identified by plan-info
• Foreign keys that reference the objects that are identified by plan-info
• Views on objects that are identified by plan-info

This option does not use information from DSN_VIEWREF_TABLE.

collect-stats

Specifies whether to collect statistics information. Valid values are Y for yes, or N for no.

This is an input parameter of type CHAR(1) and cannot be null.

collect-column-stats

Specifies whether to collect column-level statistics information. Valid values are Y for yes, or N for no.

To collect column statistics, the collect-stats parameter must be set to Y.

This is an input parameter of type CHAR(1) and cannot be null.

edit-ddl

Specifies whether to edit the DDL output. Valid values are Y for yes, or N for no.

If edited, the DDL output contains changes, such as the STOGROUP set to SYSDEFLT, PRIQTY and SECQTY set to minimum values, and FOREIGNKEY definitions commented out. Sometimes IBM Support needs DDL output that is not edited. However, if the data to populate the DDL-defined tables will not be sent with the problem report, specify Y for this parameter.

This is an input parameter of type CHAR(1) and cannot be null.

edit-version-mode

Specifies that the output format for a different version and mode of Db2 for z/OS than is currently running when collecting information. In most cases, specify NONE, so that the output is not converted to another format. Otherwise, you must specify the version number and the mode.

Valid values for mode are C for conversion mode and N for new-function mode. For example, if you want to generate the output in the format used by Db2 11 new-function mode, specify 11-N.

This is an input parameter of type CHAR(4) and cannot be null.

partition-rotation

Specifies whether you want to verify the number of partition rotations that are required to balance the table. Valid values are Y for yes, or N for no.

This is an input parameter of type CHAR(1) and cannot be null.

output-method

Specifies the data set attributes that you want the output to include.

This is an input parameter of type CHAR(1) and cannot be null.

Possible values for output-method are:

Q

Returns dynamically created data sets with size parameters.

N

Returns the result data sets in an already existing data set in the WLM environment.

D

Returns dynamically created data sets on a volume that you specify.

R

Returns a result set in a predetermined format.

Output data sets and result sets contain the following information:

DDL

The creation statements for databases, table spaces, tables, and indexes.

SQL

INSERT statements for PLAN_TABLE, DSN_PROFILE_TABLE, DSN_PROFILE_ATTRIBUTES, and SYSACCELERATORS if the tables exist.

STATS

Statistical information related to the tables.

COLST

Statistical information related to the columns.

EXPL

The output begins with a first-pass analysis report, which contains the following information, if it is available:

• A report on missing EXPLAIN tables
• A report on statistics that are recommended but were not collected
• Results of a preliminary analysis of the EXPLAIN data

The following information is also returned:

• Visual output of the following tables, if they exist: PLAN_TABLE, DSN_PREDICAT_TABLE, DSN_DETCOST_TABLE, DSN_PROFILE_TABLE, DSN_PROFILE_ATTRIBUTES, and SYSACCELERATORS
• Information about the objects, messages, and input parameters.

The output also contains the following reports which describe object structures in relation the query:

• A table report contains information about the table space, table, and index key columns, organized by table space. It includes information about multi-column and index key cardinality.
• An index report that maps index details to predicates.

PARM

Subsystem parameter, service, module, and relational data system (RDS) MEPL information.

output-info

Specifies the output information. The values that you specify depend on the value of the output-method parameter.

This is an input parameter of type VARCHAR(1024) and cannot be null.

Based on the value of output-method, you must format the input for output-info as follows:

output-method = Q

This output method has the following format:

qualifier-primary(value or DEFLT)-secondary(value or DEFLT)

You specify a 29-character qualifier, including periods. You also can specify a primary and secondary track value. The default value is 200 for primary and 200 for secondary.

The result data set is created on temporary storage as a data set with one of the following types:

• .DDL
• .SQL
• .STATS
• .COLST
• .EXPL
• .PARM

The file might be deleted in a short period of time, depending on the configuration of your z/OS® system. Because the data set is created as a new one, existing data sets with the same name are deleted.

If pmr-info specifies a support case ID, the data set names are created with the following format, with the case ID numbers split into two parts:

Tnnnnn.Snnnn.Ddddddd.Ttttttt.VXX.file-type

For example, for support case TS123456789, the ADMIN_INFO_SQL stored procedure generates data sets with names in the following format: T12345.S6789.D190801.T170318.VXX.type

If pmr-info specifies a PMR number, the DEFAULT value creates a data set name with the following format:

PMxxxxx.Dxxxxxx.Txxxxxx.VXX.file-type

For example, for PMR 12345, the ADMIN_INFO_SQL stored procedure generates data sets with the following name format: PM12345.D091007.T170318.VXX.type

output-method = N

For established data sets in the WLM environment, you must specify the DD name in the following format:

DDL_DDname-SQL_DDname-Stats_DDname-Colst_DDname-Expl_DDname-
Parm_DDname

The WLM administrator must create these data sets with DD names in the WLM startup procedure and supply those names to the person calling the stored procedure. These data sets can be generational. You must create the data sets as new ones rather than appending existing data sets. The ADMIN_INFO_SQL stored procedure opens the data set at initialization and closes the file when complete.

output-method = D

You specify the volume where you want the data sets created and the names and sizes of the data sets. This output method has the following format but in one continuous line with no spaces:

DDL;DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary-
SQL;DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary-
STATS;DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary-
COLST;DSnameDEFAULT);volser;alcunit(TRK or CYL);primary;secondary-
EXPL;DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary-
PARM;DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary

The DEFAULT value creates the data set name with the same format and types as for output method Q. The data set name must contain the type identifier (for example, .DDL, .SQL, .STATS, etc.). As a result, the data sets can be listed in any order.

These data sets are created with the option disp=(NEW,CATLG,KEEP). Therefore, if a data set with the same name already exists, the ADMIN_INFO_SQL stored procedure generates an error.

output-method = R

You must specify NONE. This output method returns a result set in the following format:

EXEC SQL CREATE GLOBAL TEMPORARY TABLE SYSIBM.SERVICE_SQL_OUTPUT
  (TID INTEGER NOT NULL, SEQNO INTEGER NOT NULL,   
  TEXT VARCHAR(4096) NOT NULL); 

Where TEXT is the information in the result set, such as DDL statements, statistical information, and service and module information. SEQNO is the sequence number in the table, and TID is the table number. For example:

Table 1. Table numbers for result set information

Table number (TID)	Result set information
1	DDL
2	SQL
3	STATS
4	COLST
5	EXPL
6	PARM

The following table shows the format of the result set that is returned in the created global temporary table SYSIBM.SERVICE_SQL_OUTPUT:

Table 2. Result set row for ADMIN_INFO_SQL result set

Column name	Data type	Contents
TID	INTEGER NOT NULL	The table number.
SEQNO	INTEGER NOT NULL	The sequence number in the table.
TEXT	VARCHAR(4096)	The information in the result set, such as DDL statements, statistical information, and service and module information.

The following DECLARE statement shows the data that is returned for the result set and the order that the data is returned in:

EXEC SQL DECLARE DATA_CSR CURSOR WITH RETURN WITH HOLD FOR       
  SELECT TID, SEQNO, TEXT FROM SYSIBM.SERVICE_SQL_OUTPUT         
  ORDER BY TID, SEQNO;

pmr-info

Specifies one of the following values:

• The support case ID in TSnnnnnnnnn format, where nnnnnnnnn is a 9-digit number.
• The PMR number, branch code and country code in xxxxx.yyy.zzz format, where xxxxx is the PMR number, yyy is the branch code, and zzz is the country code.

This is an input parameter of type VARCHAR(13) and cannot be null.

return-code

Provides the return code from the stored procedure. Possible values are:

0

The call completed successfully.

4

Warning. The message output parameter contains messages describing the warning.

12

The call did not complete successfully. The message output parameter contains messages describing the error.

This is an output parameter of type INTEGER.

message

Contains messages describing the error or warning encountered by the stored procedure. If no error occurred, the message states "DSNADMSS completed successfully."

The first messages in this area are generated by the stored procedure. Messages that are generated by Db2 might follow the first messages.

This is an output parameter of type VARCHAR(1331).

Output

This stored procedure returns the following output parameters, which are described in Option descriptions:

• return-code
• message

In addition, this stored procedure returns output in data sets or a result set. You must ensure that enough space is available for the output. The ADMIN_INFO_SQL stored procedure might generate large amounts of data. Two to three megabytes of space is the average, but larger workloads might generate up to 20 megabytes of data. To conserve space, set the collect-column-stats option to N.