How does PPA assign creator of the PLAN_TABLE used in Explain?

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

Description:

A 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 PPA Explain options and the table might be auto created.
You can define a default secondary ID for PPA.
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.

Solution:

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 you leave this blank, the Primary AUTHID (PRIAUTH) is used. The default for Primary AUTHID is your TSO logon id.

r11.5     -------- PPA Quick Explain - Explain Options -------- 2008/11/1 08:55
 COMMAND ===>                                                                   
                                                                                
                                                                                
 ---------------------------------------------------------------------- HENPE01 
   Database Options       ===> N        Historical Database Options             
   Primary AUTHID         ===>          Secondary AUTHID    ===> plancrt        
   Update SQL Qualifiers  ===> Y        (Override Schemas & SQL/View Qualifiers)
   Rule Set SSID          ===> D81A     (Subsystem where Rule Sets are stored)  
   PLAN_TABLE Option      ===> R        (C - Commit, R - Rollback)              
   Explain Type           ===> F        (C - Current, F - Future)               
   Plan Explain Option    ===> B        (D - DBRM, P - Package, B - Both)       
   Non-Catalog Isolation  ===> CS       Optimization Hint   ===>                
   Process Views (Y, N)   ===> Y        Parallelism Degree  ===>
  . . .

Subset of explain cards:

CALL EXPLAIN                      
DATA                              
  RULESSID = (D81A)               
  ACM      = (N,HENPEA1)          
  VERSION  = (LATEST)             
  STRATEGY = (D91A,,HENPE01)      
  PLANTAB  = (ROLLBACK)           
  SECAUTH  = (PLANCRT)            
  SQLQUAL  = (HENPE01,OVERRIDE) 
. . .

Note: When you specify a secondary ID, Unicenter 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.

You can specify a default secondary auth ID in highlvl.PARMLIB(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. Document TEC471762 discusses rules for allocation of the Database and tablespace.

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 note that it is the DB2 optimizer that inserts information into the PLAN_TABLE. Therefore you cannot rename this table.

For more information please refer to:

  • Plan Analyzer Reference Guide, Chapter 3, section Authorization IDs
  • Plan Analyzer User Guide, chapter 4, section Current Explain
  • Plan Analyzer User Guide, chapter 19, Maintaining Explain Profiles