ADMIN_EXPLAIN_MAINT stored procedure

You can use the ADMIN_EXPLAIN_MAINT stored procedure to create EXPLAIN tables, upgrade the tables to the format for the current Db2 version, and complete other administrative tasks.

Begin general-use programming interface information.

For more detailed information about administrative tasks that you can complete for EXPLAIN tables by using the ADMIN_EXPLAIN_MAINT stored procedure, see the description of the action input parameter.

Important: The ADMIN_EXPLAIN_MAINT stored procedure does not convert EXPLAIN tables that are encoded in EBCDIC to Unicode. However, it does upgrade the format of such tables to the format for the current Db2 version. Starting in DB2® 10, SQLCODE -878 is issued for EXPLAIN tables that are encoded in CCSIDs other than Unicode.

Environment

The ADMIN_EXPLAIN_MAINT stored procedure must run in a WLM-established stored procedure address space.

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 on each package that the stored procedure uses:

EXECUTE privilege on the stored procedure package
Ownership of the package
PACKADM authority for the package collection
SYSADM authority

The stored procedure executes the SET CURRENT SQLID statement to change the value of CURRENT SQLID to the value that is specified by the authid input parameter. The value of CURRENT SQLID is the authorization ID for dynamic SQL statements.

Syntax

The following syntax diagram shows the SQL CALL statement for invoking this stored procedure:

Option descriptions

mode

Specifies the processing mode. This input parameter accepts the following values:

RUN: Alter and create EXPLAIN tables for the specified SCHEMA.
PREVIEW: No changes are processed.

The data type of this input parameter is VARCHAR(8). If this option is not specified, the default value is RUN.

action

Specifies the action that is completed for the EXPLAIN tables in the specified schema. This input parameter accepts the following values:

STANDARDIZE: Upgrade all existing EXPLAIN tables to the format for the current Db2 version.
STANDARDIZE_AND_CREATE: Upgrade all existing EXPLAIN tables to the format for the current Db2 version, and create any missing tables of those that are specified by the table-set input parameter.
CREATE: Create a new set of EXPLAIN tables in the specified schema. Only tables that are specified by the table-set input parameter are created.
CREATE_ALIAS: Create a new set of aliases only. The new aliases are qualified by the value that is specified for the schema-alias input parameter.
DROP: Drop all existing EXPLAIN tables and the associated table space.
DROP_AND_CREATE: Drop all existing EXPLAIN tables, and the associated table spaces, and create a replacement set of the tables that are specified by the table-set input parameter.

The data type of this input parameter is VARCHAR(30). If this option is not specified, the default value is STANDARDIZE.

manage-alias

Specifies whether to create aliases for EXPLAIN tables. This input parameter accepts the following values:

YES: The stored procedure creates aliases so that all EXPLAIN tables in the specified schema have aliases that correspond to the existing PLAN_TABLE aliases.
NO: Aliases are not created.

The data type of this input parameter is VARCHAR(3). If this option is not specified, the default value is NO.

table-set

Specifies the list of EXPLAIN tables to be created.

If the action value is CREATE, the tables that are specified must not already exist. This input parameter accepts the following values:

'table-name-1, table-name-2, ..., table-name-n'

Specifies an explicit list of tables to create.

ALL

All EXPLAIN tables that are created by the DSNTESC and DSNTESH sample jobs.

ACCEL

The following tables only:

PLAN_TABLE
DSN_STATEMNT_TABLE
DSN_STATEMENT_CACHE_TABLE
DSN_QUERYINFO_TABLE

DIAGNOSTICS

The following tables only:

PLAN_TABLE
DSN_STATEMNT_TABLE
DSN_DETCOST_TABLE
DSN_PREDICAT_TABLE
DSN_FILTER_TABLE
DSN_COLDIST_TABLE
DSN_KEYTGTDIST_TABLE
DSN_QUERYINFO_TABLE

ALL_EXCEPT(table-name-1, table-name-2, ..., table-name-n)

All tables that are created by the DSNTESC and DSNTESH sample jobs except for the tables that are specified in the list.

This value is applicable only if action is one of the following values:

STANDARDIZE_AND_CREATE
CREATE
DROP_AND_CREATE

The data type of this input parameter is VARCHAR(1000). If this option is not specified, the following EXPLAIN tables are created by default:

PLAN_TABLE
DSN_STATEMNT_TABLE
DSN_STATEMENT_CACHE_TABLE

authid

The CURRENT SQLID setting. If this option is not specified, the value of the CURRENT SQLID special register is used. The data type of this required input parameter is VARCHAR(128).

schema-name

Specifies the schema name that qualifies the EXPLAIN tables. If this option is not specified, the value of the CURRENT SCHEMA special register is used. The data type of this required input parameter is VARCHAR(128).

schema-alias

Specifies the schema name that qualifies aliases that are created when the action value is CREATE_ALIAS. The data type of this input parameter is VARCHAR(128). This input parameter is required when the action value is CREATE_ALIAS.

database-name

Specifies the database that contains the new EXPLAIN tables. The stored procedure creates the database if it does not exist. The data type of this input parameter is VARCHAR(8). If this option is not specified, the stored procedure omits the database and table space names in the CREATE TABLE statements for the EXPLAIN tables.

stogroup-database

Specifies the storage group for the database that contains the new EXPLAIN tables. The data type of this input parameter is VARCHAR(128). If this option is not specified, the stored procedure omits the STOGROUP keyword from the CREATE DATABASE statement, if the database does not already exist.

stogroup-index

Specifies the storage group for the indexes for the new EXPLAIN tables. The data type of this input parameter is VARCHAR(128). If this option is not specified, the stored procedure omits the USING STOGROUP keyword from the CREATE INDEX statement.

4k-bufferpool

Specifies the name of the 4-KB page buffer pool that is assigned to new table spaces. The data type of this input parameter is VARCHAR(8). If this option is not specified, the stored procedure omits the table space name in the CREATE TABLE statements for the EXPLAIN tables.

8k-bufferpool

Specifies the name of the 8 KB page buffer pool that is assigned to new table spaces. The data type of this input parameter is VARCHAR(8). If this option is not specified, the stored procedure omits the table space name in the CREATE TABLE statements for the EXPLAIN tables.

16k-bufferpool

Specifies the name of the 16 KB page buffer pool that is assigned to new table spaces. The data type of this input parameter is VARCHAR(8). If this option is not specified, the stored procedure omits the table space name in the CREATE TABLE statements for the EXPLAIN tables.

32k-bufferpool

Specifies the name of the 32 KB page buffer pool that is assigned to new table spaces. The data type of this input parameter is VARCHAR(8). If this option is not specified, the stored procedure omits the table space name in the CREATE TABLE statements for the EXPLAIN tables.

index-bufferpool

Specifies the name of the buffer pool for the indexes for the new EXPLAIN tables. The data type of this input parameter is VARCHAR(8). If this option is not specified, the stored procedure omits the BUFFERPOOL keyword from the CREATE INDEX statement.

bp-4kb-lob

Specifies the name of the 4-KB page buffer pool that is assigned to new LOB tables spaces for the new auxiliary tables of the new EXPLAIN tables. The data type of this input parameter is VARCHAR(8). If this option is not specified, the stored procedure omits the BUFFERPOOL keyword from the CREATE LOB TABLESPACE statement.

bp-8kb-lob

Specifies the name of the 8-KB page buffer pool that is assigned to new LOB tables spaces for the new auxiliary tables of the new EXPLAIN tables. The data type of this input parameter is VARCHAR(8). If this option is not specified, the stored procedure omits the BUFFERPOOL keyword from the CREATE LOB TABLESPACE statement.

bp-16kb-lob

Specifies the name of the 16-KB page buffer pool that is assigned to new LOB tables spaces for the new auxiliary tables of the new EXPLAIN tables. The data type of this input parameter is VARCHAR(8). If this option is not specified, the stored procedure omits the BUFFERPOOL keyword from the CREATE LOB TABLESPACE statement.

bp-32kb-lob

Specifies the name of the 32-KB page buffer pool that is assigned to new LOB tables spaces for the new auxiliary tables of the new EXPLAIN tables. The data type of this input parameter is VARCHAR(8). If this option is not specified, the stored procedure omits the BUFFERPOOL keyword from the CREATE LOB TABLESPACE statement.

return-code

An output parameter that contains the return code from the stored procedure. It contains one of the following values:

0: The call completed successfully.
4: One or more existing EXPLAIN tables are not in Unicode. The table formats are updated to the format for the current Db2 version. However, the tables are not converted to Unicode.
8: The format of one or more existing EXPLAIN tables that were included in the table-set input parameter cannot be updated to the format for the current Db2 version because the existing format does not match any known format, or the tables cannot be altered by an ALTER TABLE statement.
12: The call did not complete successfully. The message output parameter contains messages that describe the error.

message

An output parameter that contains messages that describe errors that the stored procedure encountered.

The following C language sample shows how to invoke the ADMIN_EXPLAIN_MAINT stored procedure:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
 /******************* DB2 SQL Communication Area *******************/
EXEC SQL INCLUDE SQLCA;
int main( int argc, char *argv[] )    /* Argument count and list   */
{
 /****************** DB2 Host Variables ****************************/
 EXEC SQL BEGIN DECLARE SECTION;
 /* SYSPROC.ADMIN_EXPLAIN_MAINT parameters                         */
 char       pmode[9];            /* Processing mode                */
 char       paction[31];         /* Action                         */
 char       pmanagealias[4];     /* Manage alias                   */
 char       ptableset[1001];     /* EXPLAIN tables to be created   */
 char       pauthid[129];        /* CURRENT SQLID setting          */
 char       pschema[129];        /* EXPLAIN tables qualifier       */
 char       pschemaalias[129];   /* EXPLAIN tables aliases qual    */
 char       pdatabase[9];        /* Database of EXPLAIN tables     */
 char       pstogroupdb[129];    /* Storage group of database      */
 char       pstogroupix[129];    /* Storage group of EXPLAIN       */
                                 /* tables indexes                 */
 char       p4Kbp[9];            /* 4 KB page buffer pool for      */
                                 /* table space of EXPLAIN table   */
 char       p8Kbp[9];            /* 8 KB page buffer pool for      */
                                 /* table space of EXPLAIN table   */
 char       p16Kbp[9];           /* 16 KB page buffer pool for     */
                                 /* table space of EXPLAIN table   */
 char       p32Kbp[9];           /* 32 KB page buffer pool for     */
                                 /* table space of EXPLAIN table   */
 char       pixbp[9];            /* Buffer pool for indexes of     */
                                 /* EXPLAIN tables                 */
 char       p4Klobbp[9];         /* 4 KB page buffer pool for      */
                                 /* LOB table space                */
 char       p8Klobbp[9];         /* 8 KB page buffer pool for      */
                                 /* LOB table space                */
 char       p16Klobbp[9];        /* 16 KB page buffer pool for     */
                                 /* LOB table space                */
 char       p32Klobbp[9];        /* 32 KB page buffer pool for     */
                                 /* LOB table space                */
 long int   prc;                 /* Return code                    */
 char       pmsg[1332];          /* Error message                  */

 short int  ind_pmode;           /* Indicator variable             */
 short int  ind_paction;         /* Indicator variable             */
 short int  ind_pmanagealias;    /* Indicator variable             */
 short int  ind_ptableset;       /* Indicator variable             */
 short int  ind_pauthid;         /* Indicator variable             */
 short int  ind_pschema;         /* Indicator variable             */
 short int  ind_pschemaalias;    /* Indicator variable             */
 short int  ind_pdatabase;       /* Indicator variable             */
 short int  ind_pstogroupdb;     /* Indicator variable             */
 short int  ind_pstogroupix;     /* Indicator variable             */
 short int  ind_p4Kbp;           /* Indicator variable             */
 short int  ind_p8Kbp;           /* Indicator variable             */
 short int  ind_p16Kbp;          /* Indicator variable             */
 short int  ind_p32Kbp;          /* Indicator variable             */
 short int  ind_pixbp;           /* Indicator variable             */
 short int  ind_p4Klobbp;        /* Indicator variable             */
 short int  ind_p8Klobbp;        /* Indicator variable             */
 short int  ind_p16Klobbp;       /* Indicator variable             */
 short int  ind_p32Klobbp;       /* Indicator variable             */
 short int  ind_prc;             /* Indicator variable             */
 short int  ind_pmsg;            /* Indicator variable             */
 /* Result set locators                                            */
 volatile SQL TYPE IS RESULT_SET_LOCATOR *rs_loc1;
 volatile SQL TYPE IS RESULT_SET_LOCATOR *rs_loc2;
 volatile SQL TYPE IS RESULT_SET_LOCATOR *rs_loc3;
 /* SYSIBM.EXPLAIN_MAINT_SUMMARY result set row                    */
 long int    db_created;             /* Num databases created      */
 long int    ts_created;             /* Num table spaces created   */
 long int    tb_created;             /* Num EXPLAIN tables created */
 long int    aux_created;            /* Num aux tables created     */
 long int    ix_created;             /* Num indexes created        */
 long int    alias_created;          /* Num aliases created        */
 long int    tb_examined;            /* Num EXPLAIN tables examined*/
                                     /* for old format             */
 long int    tb_altered;             /* Num databases created      */
 long int    ts_dropped;             /* Num table spaces dropped   */
 long int    tb_dropped;             /* Num EXPLAIN tables dropped */
 long int    tb_unexpected_format;   /* Num EXPLAIN tables with    */
                                     /* invalid format             */
 long int    tb_not_standardized;    /* Num EXPLAIN tables not     */
                                     /* upgraded to the current    */
                                     /* DB2 release format         */
 long int    tb_not_unicode;         /* Num EXPLAIN tables not     */
                                     /* in UNICODE                 */
 /* SYSIBM.EXPLAIN_MAINT_SQL result set row                        */
 long int    rownum2;                /* Sequence number of the     */
                                     /* table row                  */
 char        sql[16000];             /* SQL statement              */
 /* SYSIBM.EXPLAIN_MAINT_TB_NOT_UPGRADED result set row            */
 long int    rownum3;                /* Sequence number of the     */
                                     /* table row                  */
 char        schemaname[129];        /* EXPLAIN table qualifier    */
 char        tbname[129];            /* EXPLAIN table name         */
 char        reason[1001];           /* Reason                     */
 EXEC SQL END DECLARE SECTION;

 int resultset3 = 0;                 /* Result set 3 not returned  */
 /******************************************************************/
 /* Clear result tables                                            */
 /******************************************************************/
 EXEC SQL DELETE FROM SYSIBM.EXPLAIN_MAINT_SUMMARY;
 EXEC SQL DELETE FROM SYSIBM.EXPLAIN_MAINT_SQL;
 EXEC SQL DELETE FROM SYSIBM.EXPLAIN_MAINT_TB_NOT_UPGRADED;
 /******************************************************************/
 /* Set procedure input parameters                                 */
 /******************************************************************/
 strcpy(paction, "STANDARDIZE_AND_CREATE");
 strcpy(ptableset,
        "DSN_PREDICATE_SELECTIVITY, DSN_STAT_FEEDBACK");
 strcpy(pauthid, "USER001");
 strcpy(pschema, "USER002");
 ind_pmode = -1;
 ind_paction = 0;
 ind_pmanagealias = -1;
 ind_ptableset = 0;
 ind_pauthid = 0;
 ind_pschema = 0;
 ind_pschemaalias = -1;
 ind_pdatabase = -1;
 ind_pstogroupdb = -1;
 ind_pstogroupix = -1;
 ind_p4Kbp = -1;
 ind_p8Kbp = -1;
 ind_p16Kbp = -1;
 ind_p32Kbp = -1;
 ind_pixbp = -1;
 ind_p4Klobbp = -1;
 ind_p8Klobbp = -1;
 ind_p16Klobbp = -1;
 ind_p32Klobbp = -1;
 ind_prc = -1;
 ind_pmsg = -1;
 /******************************************************************/
 /* Call stored procedure SYSPROC.ADMIN_EXPLAIN_MAINT              */
 /******************************************************************/
 EXEC SQL CALL SYSPROC.ADMIN_EXPLAIN_MAINT (
                      :pmode:ind_pmode,
                      :paction:ind_paction,
                      :pmanagealias:ind_pmanagealias,
                      :ptableset:ind_ptableset,
                      :pauthid:ind_pauthid,
                      :pschema:ind_pschema,
                      :pschemaalias:ind_pschemaalias,
                      :pdatabase:ind_pdatabase,
                      :pstogroupdb:ind_pstogroupdb,
                      :pstogroupix:ind_pstogroupix,
                      :p4Kbp:ind_p4Kbp,
                      :p8Kbp:ind_p8Kbp,
                      :p16Kbp:ind_p16Kbp,
                      :p32Kbp:ind_p32Kbp,
                      :pixbp:ind_pixbp,
                      :p4Klobbp:ind_p4Klobbp,
                      :p8Klobbp:ind_p8Klobbp,
                      :p16Klobbp:ind_p16Klobbp,
                      :p32Klobbp:ind_p32Klobbp,
                      :prc:ind_prc,
                      :pmsg:ind_pmsg);
 /******************************************************************/
 /* Retrieve result sets when the SQLCODE from the call is +446,   */
 /* which indicates that result sets were returned                 */
 /******************************************************************/
 if (SQLCODE == +466)               /* Result sets were returned   */
 {
   /* Establish a link between a result set and its locator        */
   EXEC SQL ASSOCIATE LOCATORS (:rs_loc1, :rs_loc2, :rs_loc3)
 		WITH PROCEDURE SYSPROC.ADMIN_EXPLAIN_MAINT;
   /* Associate a cursor with each result set                      */
   EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :rs_loc1;
   EXEC SQL ALLOCATE C2 CURSOR FOR RESULT SET :rs_loc2;
   EXEC SQL ALLOCATE C3 CURSOR FOR RESULT SET :rs_loc3;
   if (SQLCODE == 0)                /* Result set 3 is returned    */
     resultset3 = 1;
   /* Perform fetch using C1 to retrieve the first result set      */
   EXEC SQL FETCH C1 INTO :db_created, :ts_created,
                          :tb_created, :aux_created,
                          :ix_created, :alias_created,
                          :tb_examined, :tb_altered,
                          :ts_dropped, :tb_dropped,
						  :tb_unexpected_format,
						  :tb_not_standardized,
                          :tb_not_unicode;
   /* Perform fetches using C2 to retrieve all rows from the       */
   /* second result set                                            */
   EXEC SQL FETCH C2 INTO :rownum2, :sql;
   while (SQLCODE == 0)
   {
     EXEC SQL FETCH C2 INTO :rownum2, :sql;
   }
   /* Perform fetches using C3 to retrieve all rows from the       */
   /* third result set                                             */
   if (resultset3 == 1)
   {
     EXEC SQL FETCH C3 INTO :rownum3, :schemaname, :tbname,
                            :reason;
     while (SQLCODE == 0)
     {
       EXEC SQL FETCH C3 INTO :rownum3, :schemaname, :tbname,
                              :reason;
     }
   }
 }
 return;
}

Output

The ADMIN_EXPLAIN_MAINT stored procedure creates as many as three result sets that are returned in the following global temporary tables:

SYSIBM.EXPLAIN_MAINT_SUMMARY

The summary of the actions completed by the stored procedure if the value of the mode input parameter is RUN. If the value of the mode input parameter is PREVIEW, this result set summarizes the actions, but the actions are not completed. The values of the various type_CREATED, type_ALTERED, type_DROPPED columns summarize the numbers of corresponding statements for each action in the SQL column of SYSIBM.EXPLAIN_MAINT_SQL result set.

Table 1. Format of the SYSIBM.EXPLAIN_MAINT_SUMMARY result set
Column name	Data Type	Description
DB_CREATED	INTEGER NOT NULL	The number of databases that are created.
TS_CREATED	INTEGER NOT NULL	The number of tables paces that are created.
TB_CREATED	INTEGER NOT NULL	The number of EXPLAIN tables that are created.
AUX_CREATED	INTEGER NOT NULL	The number of auxiliary tables that are created.
IX_CREATED	INTEGER NOT NULL	The number of indexes that are created.
ALIAS_CREATED	INTEGER NOT NULL	The number of aliases that are created.
TB_EXAMINED	INTEGER NOT NULL	The number of EXPLAIN tables that were examined for formats older than the format for the current Db2 version.
TB_ALTERED	INTEGER NOT NULL	The number of tables that are altered to use the format for the current Db2 version.
TS_DROPPED	INTEGER NOT NULL	The number of table spaces that were dropped.
TB_DROPPED	INTEGER NOT NULL	The number of EXPLAIN tables that are dropped.
TB_UNEXPCECTED_FORMAT	INTEGER NOT NULL	The number of EXPLAIN tables that do not match any known valid format.
TB_NOT_STANDARDIZED	INTEGER NOT NULL	The number of EXPLAIN tables that cannot be converted to the format for the current Db2 version.
TB_NOT_UNICODE	INTEGER NOT NULL	The number of existing EXPLAIN tables that are encoded in a CCSID other than Unicode. Such tables are upgraded to the format for the current Db2 version, but the CCSID is not changed to Unicode.

SYSIBM.EXPLAIN_MAINT_SQL

A list of the SQL statements that the stored procedure issues if the value of the mode input parameter is RUN. If the value of the mode input parameter is PREVIEW, this result set summarizes the actions, but the statements are not issued.

Table 2. Format of the SYSIBM.EXPLAIN_MAINT_SQL result set
Column name	Data Type	Description
ROWNUM	INTEGER NOT NULL	The sequence number of the table row, 1 - `n`.
SQL	VARCHAR(16000) NOT NULL	The text of an SQL statement that is issued by the stored procedure to create, alter, or drop EXPLAIN tables and related objects.

Table space names: Table spaces that the ADMIN_EXPLAIN_MAINT stored procedure creates for EXPLAIN tables and related auxiliary tables have names that are based on a four-letter abbreviation of the name of the associated table, followed by a four-digit number that ensures the uniqueness name. The four-letter abbreviations do not follow a consistent pattern in relation to the various table names. For example, PLANnnnn is used for PLAN_TABLE, STMTnnnn is for DSN_STATEMNT_TABLE, and SFEDnnnn is used for DSN_STAT_FEEDBACK table.

You can find the names of the table spaces that the stored procedure creates in the SYSIBM.EXPLAIN_MAINT_SQL result set.

SYSIBM.EXPLAIN_MAINT_TB_NOT_UPGRADED

This result set is returned only when the value of the action input parameter is STANDARDIZE or STANDARDIZE_AND_CREATE and at least one of the following conditions is true. The result set contains one row for each of the following occurrences:

The table is created with a CSSID other than Unicode. Such tables might be upgraded to the format for the current Db2 version. However, the CCSID is not changed to Unicode.
The format of the EXPLAIN table does not match any known format. Such tables are not upgraded.
ALTER TABLE statements cannot complete changes that are required to upgrade a table to the format for the current Db2 version. Such tables are not upgraded.

Table 3. Format of the SYSIBM.EXPLAIN_TMAIN_TB_NOT_UPGRADED result set
Column name	Data Type	Description
ROWNUM	INTEGER NOT NULL	The sequence number of the table row, 1 - `n`.
SCHEMA	VARCHAR(128) NOT NULL	The schema of an EXPLAIN table that cannot be converted to the format for the current version of Db2.
TBNAME	VARCHAR(128) NOT NULL	The name of an EXPLAIN table that cannot be converted to the format for the current version of Db2.
REASON	VARCHAR(1000) NOT NULL	A description of the reason why the table cannot be converted to the format for the current version of Db2.

End general-use programming interface information.