Create a PLAN_TABLE and associated tables for use by an EXPLAIN
Article ID: 279755
Updated On:
Products
Issue/Introduction
When explain data from a BIND is required to be stored for later analysis the DB2 PLAN_TABLE and associated tables must exist. They can be created by the process carrying out the BIND before the BIND but it is best to create the objects on the appropriate DATABASE and TABLESPACE beforehand to ensure correct placement and sizing. Products like PLAN ANALYZER for DB2 for Z/OS and SQL-EASE for DB2 for Z/OS are able to Auto create these tables during an enhanced explain if the tables are not found to exist already.
These tables can be created manually also.
Environment
DB2 for Z/OS
Resolution
Three products are able to create the PLAN_TABLE and associated tables.
1) SQL-Ease for Db2 for z/OS 20
Enter the required information below and press enter to create. The DDL is generated for review online providing the user with the ability to execute the DDL online or batch or save it for later.
Menu item 10- EXPLTB - Create Explain Table(s)
SQPUPTM ------ SQL-EASE Explain Table(s) Creation ----- yyyy/mm/dd hh:mm
COMMAND ===>
User ===> * Database ===> DSNDB04
---------------------------------------------------------------------- authid
Location ===> LOCAL DB2 SSID ===> ssid Version ===> 121M500
Create Explain/Input Objects:
PLAN_TABLE ===> Y Tablespace ===>
DSN_STATEMNT_TABLE ===> Y Tablespace ===>
DSN_FUNCTION_TABLE ===> Y Tablespace ===>
DSN_COLDIST_TABLE ===> Y Tablespace ===>
DSN_KEYTGTDIST_TABLE ===> Y Tablespace ===>
DSN_PREDICAT_TABLE ===> Y Tablespace ===>
DSN_FILTER_TABLE ===> Y Tablespace ===>
DSN_VIRTUAL_INDEXES ===> Y Tablespace ===>
Complete the header fields and press ENTER. When the User List appears,
select each user for whom you want to create Explain/Input Table(s).
2) Plan Analyzer for DB2 for Z/OS
Enter the required information below and press enter to create. The DDL is generated for review online providing the user with the ability to execute the DDL online or batch or save it for later.
Menu item CT Create Explain/Input Tables
-------- PPA Explain/Input Table(s) Creation ------- yyyy/mm/dd hh:mm
COMMAND ===>
User ===> * Database ===> DSNDB04
---------------------------------------------------------------------- authid
Location ===> LOCAL DB2 SSID ===> ssid Version ===> 121M500
Create Explain/Input Objects:
PLAN_TABLE ===> Y Tablespace ===>
DSN_STATEMNT_TABLE ===> Y Tablespace ===>
DSN_FUNCTION_TABLE ===> Y Tablespace ===>
DSN_USERQUERY_TABLE ===> N Tablespace ===> LOB TS ===>
DSN_STATEMENT_CACHE ===> N Tablespace ===> LOB TS ===>
DSN_COLDIST_TABLE ===> Y Tablespace ===>
DSN_KEYTGTDIST_TABLE ===> Y Tablespace ===>
DSN_PREDICAT_TABLE ===> Y Tablespace ===>
DSN_FILTER_TABLE ===> Y Tablespace ===>
DSN_VIRTUAL_INDEXES ===> Y Tablespace ===>
Enter the header fields above and press ENTER. When the User List appears,
you may select each user you wish to create Explain/Input Table(s) for.
3) SYSVIEW Performance Management Option for Db2 20
Menu item 7 Explain then menu item 5 Manage EXPLAIN environment
Enter the required information below and select option 1 to create. It will create the objects online.
Menu Print Tools Help SYSVIEW for DB2 ssid lpar mm/dd/yy hh:mm:ss
20.0.10 datacollector authid1
Manage EXPLAIN Environment
Row 1/14
Specify the items to be created or dropped and the desired options (blank
fields will have default values used). Then press Enter.
Options:
Type of request . . 1 1. Create Database Tblspace
2. Drop SQLID . . Qualifier
DB/Tblspace Index
Items to be created/dropped: Buffer pool . . . .
Database VCAT/STOGROUP . . .
Tablespace Name . . . . . SYSDEFLT . . SYSDEFLT
S PLAN_TABLE Pri quantity . . 30 . . 12
PLAN_TABLE_HINT_IX Sec quantity . . 30 . . 12
DSN_STATEMNT_TABLE Close YES/NO . . NO . . NO
DSN_QUERYINFO_TABLE CCSID . . . . : UNICODE
Feedback
