How does Plan Analyzer assign creator of the PLAN_TABLE used in Explain?
Article ID: 53952
Updated On:
Products
Issue/Introduction
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.
Environment
Release: r20
Component: Plan Analyzer for Db2 for z/OS
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 ===>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:
.CALL EXPLAIN
.DATA
RULESSID = (ssid)
ACM = (N,authid)
STRATEGY = (ssid,,authid)
PLANTAB = (ROLLBACK)
SECAUTH = (xxxxxxx)
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 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
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
Feedback
