ADMIN_INFO_SQL stored procedure
The ADMIN_INFO_SQL stored procedure captures statistics about a DB2® subsystem, its objects, and applications and returns the results in a data set or as a result set. This information can help IBM® Software Support re-create and troubleshoot problems, such as a poorly performing SQL query.
The output from ADMIN_INFO_SQL is primarily for the use of IBM Software Support. The output might change at any time.
To provide the results to IBM Software Support, you must terse the files and upload them to an FTP site. Then, you update the PMR when the files are available. For more information, see APAR II11945.
Environment
ADMIN_INFO_SQL must run in a WLM-established stored procedures address space, where NUMTCB is a value between 40 and 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.
Syntax
The following syntax diagram shows the SQL CALL statement for invoking this stored procedure:
>>-CALL--SYSPROC.ADMIN_INFO_SQL--(--table-creator,--------------> >--table-name,--catalog-creator,--plan-info,--collect-ddl,------> >--collect-stats,--collect-column-stats,--edit-ddl,-------------> >--edit-version-mode,--partition-rotation,--output-method,------> >--output-info,--pmr-info,--return-code,--message--)-----------><
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 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
- Specifies the programs and query numbers from PLAN_TABLE for the tables to be collected.
If you are collecting information from a single table that is not PLAN_TABLE, or a list of tables, specify 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 by IBM Software Support unless they direct you to do so.
- 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 Software 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 Software 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 should be formatted for a different version and mode of DB2 for z/OS® than the
version and mode that is currently running when collecting information. You must specify the version
number and the mode, or you can specify NONE so that the output is not converted to another
format.
Valid values for mode are C for conversion mode and N for new-function mode. For example, if your DB2 subsystem is running in DB2 10 conversion mode, and you want to generate the output for DB2 9 new-function mode, specify 9-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.
- 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:
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.qualifier-primary(value or DEFLT)-secondary(value or DEFLT)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.
The DEFAULT value creates a data set name with the following format:
PMxxxxx.Dxxxxxx.Txxxxxx.Vx.TypeFor example, for PMR 12345, the ADMIN_INFO_SQL stored procedure generates the following files:
- PM12345.D091007.T170318.V9.COLST
- PM12345.D091007.T170318.V9.DDL
- PM12345.D091007.T170318.V9.EXPL
- PM12345.D091007.T170318.V9.PARM
- PM12345.D091007.T170318.V9.SQL
- PM12345.D091007.T170318.V9.STATS
- 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_DDnameThe 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:
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:EXEC SQL CREATE GLOBAL TEMPORARY TABLE SYSIBM.SERVICE_SQL_OUTPUT (TID INTEGER NOT NULL, SEQNO INTEGER NOT NULL, TEXT VARCHAR(4096) NOT NULL);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 NULLThe table number. SEQNO INTEGER
NOT NULLThe 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 the PMR number, branch code, and country code in the following format:
xxxxx.xxx.xxx.
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).
Examples
You can invoke the call for the ADMIN_INFO_SQL stored procedure from a DB2 command line processor, if you have access to a z/OS server. You also can call this stored procedure by using Java JDBC applications and by using the C language.
In addition, you can use DSNADMSB, an IBM-supplied program, to call the ADMIN_INFO_SQL stored procedure. The result set is returned in a data set or as part of the job stream.
Example 1: The following example calls the ADMIN_INFO_SQL stored procedure to collect information from the PLAN_TABLE for program APROGRAM and query numbers between 1 and 12345.
CALL SYSPROC.ADMIN_INFO_SQL('sysadm','PLAN_TABLE','DEFAULT',
'APROGRAM-1-12345','Y','Y','N','Y','NONE','N','D','DDL;DEFAULT;EDSDMP;
TRK;200;200-SQL;DEFAULT;EDSDMP;TRK;200;200-STATS;DEFAULT;EDSDMP;TRK;200;200
-COLST;DEFAULT;EDSDMP;TRK;200;200-EXPL;DEFAULT;EDSDMP;TRK;200;200
-PARM;DEFAULT;EDSDMP;TRK;200;200','12345.000.000',?,?);The output is created in data sets on volume EDSDMP with 200 primary tracks and 200 secondary tracks. These data sets have the following naming convention, where 'x' is the creation date and 'y' is the creation time:
- PM12345.Dxxxxxx.Tyyyyyy.V9.COLST
- PM12345.Dxxxxxx.Tyyyyyy.V9.DDL
- PM12345.Dxxxxxx.Tyyyyyy.V9.EXPL
- PM12345.Dxxxxxx.Tyyyyyy.V9.PARM
- PM12345.Dxxxxxx.Tyyyyyy.V9.SQL
- PM12345.Dxxxxxx.Tyyyyyy.V9.STATS
Example 2: The following example of the ADMIN_INFO_SQL stored procedure uses the list table T1 to collect data from all of the base tables that are in the list. A list table is a database table that contains the columns CREATOR and TABLE. The following SQL statements show how to create a list table:
DROP TABLE TL1;
DROP DATABASE DL1;
COMMIT;
CREATE DATABASE DL1;
CREATE TABLESPACE TSL1 IN DL1;
CREATE TABLE TL1 (CREATOR VARCHAR(128), TABLE VARCHAR(128)) IN DL1.TSL1;
COMMIT;
INSERT INTO TL1 VALUES ('SYSADM','T1');
INSERT INTO TL1 VALUES ('SYSADM','T2');
INSERT INTO TL1 VALUES ('SYSADM','T3');
COMMIT;The following CALL statement for the ADMIN_INFO_SQL stored procedure returns a single result set in the job stream. The list table name must be preceded by 'LIST_TABLE-".
CALL SYSPROC.ADMIN_INFO_SQL('sysadm','LIST_TABLE-T1','DEFAULT','NONE','Y','Y',
'N','Y','NONE','N','R','NONE','12345.000.000',?,?);Even though the column name is TABLE in the list table,
the input for a list of objects can be any 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
Example 3: You also can call this stored procedure by using Java JDBC applications and by using the C language. The following examples call the ADMIN_INFO_SQL stored procedure to collect information from the PLAN_TABLE for program APROGRAM and query numbers between 1 and 12345, and return a result set.
Java JDBC code snippet example:
try
{
cstmt = conn.prepareCall("CALL SYSPROC.ADMIN_INFO_SQL(?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?)");
// Create a CallableStatement object
cstmt.setString (1, "sysadm");
cstmt.setString (2, "PLAN_TABLE");
cstmt.setString (3, "DEFAULT");
cstmt.setString (4, "APROGRAM-1-12345");
cstmt.setString (5, "Y");
cstmt.setString (6, "Y");
cstmt.setString (7, "Y");
cstmt.setString (8, "N");
cstmt.setString (9, "NONE");
cstmt.setString (10, "N");
cstmt.setString (11, "R");
cstmt.setString (12, "NONE");
cstmt.setString (13, "12345.000.000");
// Set input parameters (DB2 command)
cstmt.registerOutParameter (14, Types.INTEGER);
cstmt.registerOutParameter (15, Types.VARCHAR);
// Register output parameters
boolean resultsAvailable = cstmt.execute();
rc = cstmt.getInt(14); // Get the output parameter values
errbuff = cstmt.getString(15);
while (resultsAvailable)
{
ResultSet rs = cstmt.getResultSet();
while (rs.next())
{
String s = rs.getString(3);
System.out.println(s);
}
rs.close();
resultsAvailable = cstmt.getMoreResults();
} C language code snippet example:
EXEC SQL CALL SYSPROC.ADMIN_INFO_SQL ('sysadm','PLAN_TABLE'
,'DEFAULT', 'APROGRAM-1-12345','Y','Y','N','Y','9-N','N','R',
'NONE','12345.000.000',:out1,:out2);
printf( "%d CALL SQLCODE\n", SQLCODE);
printf( "%d CALL RC\n", out1);
printf( "%s CALL DETAILS\n", out2);
if(SQLCODE==+466)
{
EXEC SQL ASSOCIATE LOCATORS (:loc1) WITH
PROCEDURE SYSPROC.ADMIN_INFO_SQL;
printf( "%d ASSOC SQLCODE\n", SQLCODE);
EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :loc1;
printf( "%d ALLOC SQLCODE\n", SQLCODE);
while(SQLCODE==0)
{
DATA.LNG = 0;
SEQNO = 0;
TID = 0;
ind1 = -1;
ind2 = -1;
ind3 = -1;
EXEC SQL FETCH C1 INTO :TID :ind1, :SEQNO :ind2, :DATA :ind3;
memcpy(output, DATA.THEDATA, DATA.LNG);
output??(DATA.LNG??) = '\0';
printf( "%s\n", output);
}
}
printf( "%d FETCH SQLCODE\n", SQLCODE); 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.
