Managing EXPLAIN tables using Db2 Admin tool
You can use the Db2 Admin tool functionality to manage EXPLAIN tables and aliases. You can enter the Schema (optional) at the end of option 6 in brackets. Once this option is selected, the Db2 admin ‘Manage EXPLAIN Tables & Aliases’ panel is opened. If Schema is not provided as explained earlier and highlighted in the screen below, you can logon and change the Schema directly on Db2 Admin tool panel.
Procedure
- Select option 2, Advanced Db2 SQL PA Processing, from the primary menu panel. The Advanced Processing Options panel is displayed.
- Select option 6.
ANLPMAN2 --------------- Advanced Processing Options ------------------ Db2 system: LBB6 Db2 SQLID : TSANLTS Options using SQLPA functionality: 1 Modify statistics 2 Migrate statistics 3 Create or drop an index Options using Db2 Admin Tool functionality: 4 Modify statistics by Db2 Admin Tool 5 Migrate statistics by Db2 Admin Tool 6 Manage EXPLAIN tables & aliases with schema (PH57607 ) 7 Create or drop an index by Db2 Admin Tool - Enter Schema (Optional).
- The Manage EXPLAIN tables & aliases panel is displayed.
Example:
Integration with db2Admin tool using option 6 enables direct access to the ‘Manage EXPLAIN Tables & Aliases’ Db2 Admin Tool panel under the appropriate SQL ID.
LBB6 Manage EXPLAIN Tables & Aliases Row 1 to 23 of 23
SET stmt executed
Input for ADMIN_EXPLAIN_MAINT stored procedure:
Action . . CREATE (Create,Upgrade,Dropall) Manage alias . . NO (Yes/No)
Schema . . PH57607 >
LC S W EXPLAIN Table Schema Owner
-- - - -------------------------- -------------> ------------->
S E PLAN_TABLE PH57607 PH57607
S E DSN_STATEMNT_TABLE PH57607 PH57607
S DSN_STATEMENT_CACHE_TABLE Not found
DSN_QUERYINFO_TABLE Not found
DSN_PREDICAT_TABLE PH57607 PH57607
DSN_FILTER_TABLE Not found
DSN_DETCOST_TABLE PH57607 PH57607
DSN_COLDIST_TABLE Not found
DSN_KEYTGTDIST_TABLE Not found
DSN_VIRTUAL_INDEXES PH57607 PH57607
DSN_VIRTUAL_KEYTARGETS Not found
DSN_FUNCTION_TABLE PH57607 PH57607
DSN_PGRANGE_TABLE Not found
DSN_PGROUP_TABLE Not found
DSN_PREDICATE_SELECTIVITY Not found
DSN_PTASK_TABLE Not found
DSN_QUERY_TABLE Not found
DSN_SORTKEY_TABLE Not found
DSN_SORT_TABLE Not found
DSN_STAT_FEEDBACK Not found
DSN_STRUCT_TABLE Not found
DSN_USERQUERY_TABLE Not found
DSN_VIEWREF_TABLE Not found
******************************* END OF DB2 DATA *******************************
You can create, update, or drop EXPLAIN tables. This command uses the ADMIN_EXPLAIN_MAINT stored procedure.