search cancel

Plan Analyzer for DB2 for Z/OS : How does PPA assign creator of the PLAN_TABLE used in Explain?

book

Article ID: 53952

calendar_today

Updated On:

Products

Plan Analyzer for DB2 for z/OS

Issue/Introduction

A Plan Analyzer(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.

Environment

Release: R20
Component: PPADB2

Resolution

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
COMMAND ===>


---------------------------------------------------------------------- authid
Database Options       ===> N        Historical Database Options
  Primary AUTHID         ===>          Secondary AUTHID    ===>plancrt 
  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:

.CALL EXPLAIN
.DATA
 RULESSID   = (ssid)
 ACM        = (N,authid)
 STRATEGY   = (ssid,,authid)
   PLANTAB    = (ROLLBACK)
   SECAUTH    = (PLANCRT)
   LINES      = (55)
   PROCDDF    = (Y)
   FLOATFMT   = (ENG)
   SPEXPL     = (Y)
   PROCVIEW   = (Y)
   EXPLTYPE   = (FUTURE)
   ISOLATE    = (CS)
 TARGET     = (ssid(@@RULDBA))
 DATABASE   = (AUTO,@CURRDBA,ssid)
   REPORT     = (ACCESS/SHORT,COST,DEPENDENCY/SHORT,PHYSRULE/SHORT,
               PLANRULE/SHORT,PREDICATE,PREDRULE/SHORT,SQLRULE/SHORT,
               SUMMARY,SUPPRPTS)
 SRCPACK    = (ssid,LOCAL,TSTPRG2,,'VERS()')
.ENDDATA

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 highlvl.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. Article ID: 54723 discusses rules for allocation of the Database and tablespace.

-- 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 column of SYSIBM.SYSPACKAGE.

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