EXPLAIN table changes in Db2 11
Before you can use EXPLAIN, you must create an EXPLAIN table to hold the captured information. Certain optimization tools also create and use their own instances of the EXPLAIN tables.
For the current formats, and sample CREATE TABLE statements for PLAN_TABLE and the other EXPLAIN tables, see member DSNTESC of the prefix.SDSNSAMP library. For the complete set of column descriptions for each table, see EXPLAIN tables and Input tables.
DSNTIJG creates no EXPLAIN tables with SYSIBM qualifier
The DSNTIJSG installation job no long contains CREATE statements for EXPLAIN tables with the SYSIBM qualifier. Db2 no longer uses these tables.
Universal table spaces for EXPLAIN tables
In Db2 11, the table space type for the EXPLAIN tables that are created by the supplied sample jobs and the ADMIN_EXPLAIN_MAINT stored procedure are created as partition-by-growth table spaces, instead of segmented (non-UTS) table spaces. For more information about table space types, see Table space types and characteristics in Db2 for z/OS.
New and changed EXPLAIN tables
The following table lists only new and changed columns for the following EXPLAIN tables:
| EXPLAIN table | Changes |
|---|---|
| PLAN_TABLE | New columns:
Changed columns:
|
| DSN_PREDICAT_TABLE | New columns:
Changed columns:
|
| DSN_PREDICATE_SELECTIVITY table | New table. |
| DSN_PTASK_TABLE | New columns:
|
| DSN_QUERYINFO_TABLE | New columns:
|
| DSN_QUERY_TABLE | New columns:
|
| DSN_SORTKEY_TABLE | New columns:
|
| DSN_SORT_TABLE | New columns:
|
| DSN_STATEMNT_TABLE | New columns:
|
| DSN_STRUCT_TABLE | New columns:
|
| DSN_VIEWREF_TABLE | New columns:
|
| DSN_VIRTUAL_INDEXES | New columns:
|
| DSN_VIRTUAL_KEYTARGETS | New table. |