Plan Analyzer : Future Explain creates a temporary PLAN_TABLE and index in DSNDB04 and BP0.
Article ID: 276775
Updated On:
Products
Issue/Introduction
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?
Environment
DB2 for Z/OS
Release : R20
Cause
Users not allowed use of bufferpool BP0 causing the auto-create of the PLAN_TABLE index to fail.
Resolution
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) */
/* */
AUTOCRIX (Y) /* AUTOMATICALLY CREATE INDEXES FOR */
/* 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
COMMAND ===>
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
Feedback
