How does Plan Analyzer assign creator of the PLAN_TABLE used in Explain?
search cancel

How does Plan Analyzer assign creator of the PLAN_TABLE used in Explain?


Article ID: 53952


Updated On:


Plan Analyzer for DB2 for z/OS


A Plan Analyzer for Db2 for z/OS (PPA) Explain is analyzing the information from the Db2 optimizer in the PLAN_TABLE.
For a Future PPA Explain the creator of the PLAN_TABLE is obtained from the secondary or primary authid field in the
PPA Explain options and the table might be auto created.
A default secondary ID for PPA is able to be defined.

For a Current PPA Explain the creator is read from the owner of the plan/packages being analyzed.

Since it is the Db2 optimizer that inserts information into the PLAN_TABLE this table name cannot be changed.


Release: r20
Component: Plan Analyzer for Db2 for z/OS


For a Future-type explain, Db2 will write rows into the PLAN_TABLE  and then Plan Analyzer will read them. In this scenario,
the CREATOR of the PLAN_TABLE is specified in the Secondary AUTHID (SECAUTH) field. If it is left blank, the Primary AUTHID (PRIAUTH) is used.

The default for Primary AUTHID is the current TSO logon id.

          -------- PPA Quick Explain - Explain Options ------- yyyy/mm/dd hh:mm

---------------------------------------------------------------------- authid
Database Options       ===> N        Historical Database Options
  Primary AUTHID         ===>          Secondary AUTHID    ===>xxxxxxx 
  Update SQL Qualifiers  ===> Y        (Override Schemas & SQL/View Qualifiers)
Rule Set SSID          ===> ssid     (Subsystem where Rule Sets are stored)
  PLAN_TABLE Option      ===> R
Explain Type           ===> F        Package Copy        ===> C
  Non-Catalog Isolation  ===> CS       Optimization Hint   ===>           >
  Process Views (Y, N)   ===> Y        Parallelism Degree  ===>

Target SSID            ===> ssid     Target Rule Set     ===> @@RULDBA
  Uppercase Output       ===> N        Access Path Filter  ===> N
  Reports                ===> Y        Search Conditions   ===> N
  Catalog Statistics     ===> N        Search Filters      ===> N

Subset of explain cards:

 RULESSID   = (ssid)
 ACM        = (N,authid)
 STRATEGY   = (ssid,,authid)
   SECAUTH    = (xxxxxxx)
   LINES      = (55)
   PROCDDF    = (Y)
   SPEXPL     = (Y)
   PROCVIEW   = (Y)
   ISOLATE    = (CS)
 TARGET     = (ssid(@@RULDBA))
 SRCPACK    = (ssid,LOCAL,TSTPRG2,,'VERS()')

Note: When a secondary ID is specified, Plan Analyzer uses the primary ID to connect to DB2, then issues a SET CURRENT SQLID command
to change the current SQLID to the specified secondary ID. This ID must be one of your secondary IDs, or you must be a SYSADM.

It is possible to use the same secondary ID for multiple users/jobs so they will use the same PLAN_TABLE.

A default secondary auth ID can be specified in hlq.CDBAPARM(PPA) option SECAUTH which will be used for new users.
Another possibility is to define SECAUTH in an Explain Profile.

If the creator.PLAN_TABLE does not exist PPA will attempt to create it. 

-- PLANCRT.PLAN_TABLE table was auto-created and format is V12.
-- PLANCRT.PLAN_TABLE_HINT_IX index was auto-created.

For a Current-type explain, rows are already in the PLAN_TABLE from when the plan or package was last bound with EXPLAIN(YES).
The CREATOR of the PLAN_TABLE that Plan Analyzer will read the rows from is the owner of the plan or package. For plans,
the owner ID is obtained from the CREATOR column of SYSIBM.SYSPLAN. For packages, the owner ID is obtained from the OWNER

For both types of explain(CURRENT and FUTURE) it is the DB2 optimizer that inserts information into the PLAN_TABLE.
Therefore you must not rename this table.

Additional Information

For more information please refer to:

Authorization IDs for Explain
Current and Future Explains
Manage Explain Profiles