Auto Creation of the PLAN_TABLE, PLAN_TABLE_HINT_IX and DSN_STATEMNT_TABLE during an EXPLAIN
Article ID: 54723
Updated On:
Products
Issue/Introduction
When an EXPLAIN is executed with Plan Analyzer(PPA) or SQL-Ease and the PLAN_TABLE and it's associated objects do 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
Component: PPADB2
Release : R20
Resolution
In order to create these database objects the explain utilizes the hlq.CDBAPARM(PPA) member and
locates 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 sample 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. Note that this is true for the first user in the subsystem creating the implicit tablespaces for these explain tables.
From Db2 12 and above, the PPA parmlib DFLT_TS should be set to (). This forces Db2 to implicitly create a tablespace for each explain table and the Db2 12 default for implicitly created tablespaces is a UTS partition by growth.
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 -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 implicit tablespaces.
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 "Review the Security Authorization Requirements", "Authority to Execute an EXPLAIN PLAN".
See also Implicitly defined table spaces in the IBM DB2 manual.
Feedback
