Plan Analyzer : Future Explain creates a temporary PLAN_TABLE and index in DSNDB04 and BP0.
search cancel

Plan Analyzer : Future Explain creates a temporary PLAN_TABLE and index in DSNDB04 and BP0.


Article ID: 276775


Updated On:


Plan Analyzer for DB2 for z/OS


Using Current and Future EXPLAIN functions, and the Future Explain creates a temporary PLAN_TABLE and index in DSNDB04.  If the bufferpool assigned to DSNDB04 is BP0, and the DBAs have not allowed use of that bufferpool, the user will get an authorization error.  Is there a way to suppress the creation of this index?

Most users are not allowed use of BP0 so the auto-create index fails.  How can we specify a buffer pool other than BP0?


DB2 for Z/OS

Release : R20


Users not allowed use of bufferpool BP0 causing the auto-create of the PLAN_TABLE index to fail.


There is no option to specify a buffer pool during an enhanced explain - for the table or index.

The database and tablespace are the only options that can be specified.

In the PPA parmlib member, there are some options that might help:

-The hlq.CDBAPARM(PPA) contains these parms:

DFLT_DB    (DSNDB04)           /* DEFAULT DATABASE NAME FOR         */
                               /* EXPLAIN TABLE(S) CREATION         */
                               /*                                   */
DFLT_TS    ()                  /* DEFAULT TABLESPACE NAME FOR       */
                               /* PLAN_TABLE AND DSN_STATEMNT_TABLE */
                               /* CREATION (MUST BE UNICODE)        */
                               /*                                   */
                               /* EXPLAIN TABLE(S)                  */
                               /*     Y - AUTO-CREATE INDEXES       */
                               /*     N - DO NOT AUTO-CREATE INDEXES*/
                               /*                                   */

These can be updated to specify a different DFLT_DB and DFLT_TS for dynamically created PLAN_TABLEs so that an existing database / tablespace can be used.

The AUTOCRIX   option can be set to "N" to suppress the creation of the indexes to allow them to be created manually,  however changes in the  hlq.CDBAPARM(PPA) impact all users of the installation not just the current user. 

Instead of dynamically creating a PLAN_TABLE each time, manually create the table before carrying out the Enhanced Explain.
This can be achieved via option CT Create Explain/Input Tables. Under this option a database.tablespace can also be specified. In addition, this table can be used by other users. This is controlled by specifying the owner of this table in the Secondary AUTHID field on the Explain Options panel.

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

Strategy     : TESTPRG2            Type            : E
Creator      : authid1             Share Option    : U
Version      : @AUTO               Description     :
---------------------------------------------------------------------- authid1
  Database Options       ===> Y        Historical Database Options
Primary AUTHID         ===> authid1  Secondary AUTHID    ===> authid2
  Update SQL Qualifiers  ===> N        (Override Schemas & SQL/View Qualifiers)
Rule Set SSID          ===> ssid     (Subsystem where Rule Sets are stored)
  PLAN_TABLE Option      ===> C
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     ===> authid1
  Save Host Variables    ===> N        Save Reports        ===> Y
  Uppercase Output       ===> Y        Access Path Filters ===> N
  Reports                ===> Y        Search Conditions   ===> N
  Catalog Statistics     ===> N        Search Filters      ===> N



Additional Information

Auto Creation of the PLAN_TABLE, PLAN_TABLE_HINT_IX and DSN_STATEMNT_TABLE during an EXPLAIN within Plan Analyzer for Db2 for z/OS and SQL-EASE for Db2 for z/OS