Auto Creation of the PLAN_TABLE, PLAN_TABLE_HINT_IX and DSN_STATEMNT_TABLE during an EXPLAIN.

Document ID : KB000054723
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

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.

Solution:

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

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.

When DFLT_TS is left blank the PLAN_TABLE is created on a new tablespace called PLANRTAB and the DSN_STATEMNT_TABLE is created on a new tablespace called DSNRSTAT.

If DFLT_TS is not blank and does exist both tables are placed on this one tablespace previously specified in parmlib.

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

  • PLAN_TABLE DID NOT EXIST, SO THE TABLE WAS AUTO-CREATED.
  • PLAN_TABLE_HINT_IX INDEX DID NOT EXIST, SO THE INDEX WAS AUTO-CREATED.
  • DSN_STATEMNT_TABLE DID NOT EXIST, SO THE TABLE WAS AUTO-CREATED.

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

The two values in hlq.parmlib(ppa) DFLT_DB and DFLT_TS are defined in the Implementation Guide.

The Sql-Ease User Guide, "Authorization and Security" 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 "Product Authorization", "Authority to Execute an EXPLAIN".