search cancel

CA Plan Analyzer for DB2 for z/os and CA SQL-EASE for DB2 for z/os: Auto Creation of the PLAN_TABLE, PLAN_TABLE_HINT_IX and DSN_STATEMNT_TABLE during an EXPLAIN.

book

Article ID: 54723

calendar_today

Updated On:

Products

Plan Analyzer for DB2 for z/OS SQL-Ease for DB2 for z/OS

Issue/Introduction

When an EXPLAIN is executed with Plan Analyzer or SQL-EASE and the PLAN_TABLE and it?s associated objects does not exist then the Explain will attempt to create them. It will attempt to create the PLAN_TABLE, the PLAN_TABLE_HINT_IX index associated with the Plan table and the DSN_STATEMNT_TABLE for you using your current authid.

 

Environment

Release: R20
Component: PPADB2

DB2v12

Resolution

In order to create these database objects the explain looks in the hlq.CDBAPARM(PPA) member and finds the values for DFLT_DB , DFLT_TS and AUTOCRIX.
The Database and/or the Tablespace referenced here must already exist.

DFLT_DB    (PLANDB1)           /* DEFAULT DATABASE NAME FOR         */
                               /* EXPLAIN TABLE(S) CREATION         */
                               /*                                   */
DFLT_TS    ()                  /* DEFAULT TABLESPACE NAME FOR       */
                               /* PLAN_TABLE AND DSN_STATEMNT_TABLE */
                               /* CREATION (MUST BE UNICODE)        */
                               /*                                   */
AUTOCRIX   (Y)                 /* AUTOMATICALLY CREATE INDEXES FOR  */
                               /* EXPLAIN TABLE(S)                  */
                               /*     Y - AUTO-CREATE INDEXES       */
                               /*     N - DO NOT AUTO-CREATE INDEXES*/
                               /*                                   */

On the Edit Parmlib Member screen for the member PPA the screen fields look like this:

Dflt DB for Expl Table(s) create> PLANDB1
Dflt TS for Expl Table(s) create>             (Define as UNICODE and 8K)
Auto-create IX for Expl Table(s)> Y           (Y,N)

The authid being used (either primary or secondary) must have authority to create the tables on these objects.
If your PLAN_TABLE Option is "C" for commit then these objects will be created and will remain after the explain otherwise they are all rolled-back.

The installed default for DFLT_DB is DSNDB04 and DFLT_TS is blank. The above samples uses PLANDB1 which is a database selected for this example. 

When DFLT_TS is left blank the PLAN_TABLE is created on a new tablespace called PLANRTAB , DSN_STATEMNT_TABLE is created on a new tablespace called DSNRSTAT, DSN_FILTER_TABLE is created on a new tablespace called DSNRFILT and DSN_PREDICAT_TABLE is created on a new tablespace called DSNRPRED.

When these objects are auto-created for the user some of these messages will be seen in the explain output depending on which objects had to be created.

-- authid.PLAN_TABLE table was auto-created and format is V12.
-- authid.PLAN_TABLE_HINT_IX index was auto-created.
-- authid.DSN_STATEMNT_TABLE table was auto-created and format is V12.
-- authid.DSN_STMT_TABLE_IX index was auto-created.
-- authid.DSN_PREDICAT_TABLE table was auto-created and format is V12.
-- authid.DSN_PREDICAT_TABLE_IDX1 index was auto-created.
-- authid.DSN_PREDICAT_TABLE_IDX2 index was auto-created.
-- authid.DSN_PREDICAT_TABLE_IDX3 index was auto-created.
-- authid.DSN_FILTER_TABLE table was auto-created and format is V12.
-- authid.DSN_FILTER_TABLE_IDX1 index was auto-created.
-- authid.DSN_FILTER_TABLE_IDX2 index was auto-created.

The resulting structure looks like this:

DATABASE   TABLESPACE   TABLENAME           TBCREATR    INDEXNAME               IXCREATR
PLANDB1
         DSNRFILT     DSN_FILTER_TABLE    AUTHID     DSN_FILTER_TABLE_IDX1   AUTHID
                                                      DSN_FILTER_TABLE_IDX2   AUTHID

         DSNRPRED     DSN_PREDICAT_TABLE AUTHID     DSN_PREDICAT_TABLE_IDX1 AUTHID
                                                      DSN_PREDICAT_TABLE_IDX2 AUTHID
                                                      DSN_PREDICAT_TABLE_IDX3 AUTHID

         DSNRSTAT     DSN_STATEMNT_TABLE AUTHID     DSN_STMT_TABLE_IX       AUTHID

         PLANRTAB     PLAN_TABLE         AUTHID     PLAN_TABLE_HINT_IX     AUTHID

If DFLT_TS is not blank and does exist the tables are placed on this one tablespace previously specified in CDBAPARM(PPA). In this example the tablespace is specified on screen thus:
Dflt TS for Expl Table(s) create>   PLANTS1      (Define as UNICODE and 8K)
and in CDBAPARM(PPA)........
DFLT_TS    (PLANTS1)


 DATABASE  TABLESPACE   TABLENAME           TBCREATR    INDEXNAME               IXCREATR
PLANDB1   PLANTS1     DSN_FILTER_TABLE   AUTHID      DSN_FILTER_TABLE_IDX1   AUTHID
                                                        DSN_FILTER_TABLE_IDX2   AUTHID 
                        DSN_PREDICAT_TABLE  AUTHID      DSN_PREDICAT_TABLE_IDX1 AUTHID 
                                                        DSN_PREDICAT_TABLE_IDX2 AUTHID 
                                                        DSN_PREDICAT_TABLE_IDX3 AUTHID 
                        DSN_STATEMNT_TABLE  AUTHID      DSN_STMT_TABLE_IX       AUTHID 
                        PLAN_TABLE          AUTHID      PLAN_TABLE_HINT_IX      AUTHID 

If  -551 errors are encountered which make reference to the inability to create objects during an explain then the user should investigate the level of authority they have to create the above objects on the hlq.CABAPARM(PPA) default database and tablespace.

 

Additional Information

The Sql-Ease User Guide, "Create Explain Tables" section describes the security requirements for the auto creation of the Plan_Table.
The Plan Analyzer User Guide discusses the required authority for Plan_Table creation in "Create Explain Table Security", "Authority to Execute an EXPLAIN PLAN".