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.

Begin program-specific programming interface information.

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.

Important: It is best to convert EXPLAIN tables to Db2 11 format during migration, or soon after migration. In Db2 11, the EXPLAIN function supports tables in Db2 11 or Db2 10 formats only. However, Db2 10 format EXPLAIN tables are deprecated. If you invoke EXPLAIN and Db2 10 tables are used, Db2 issues SQL code +20520. If tables of an unsupported format are found, Db2 issues SQL code -20008 and the EXPLAIN operation fails. You can call the ADMIN_EXPLAIN_MAINT stored procedure to create EXPLAIN tables, upgrade them to the format for the current Db2 release, or complete other maintenance tasks. See ADMIN_EXPLAIN_MAINT stored procedure for information about using the action input parameter to request each of these tasks.

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:

Table 1. List of new and changed EXPLAIN tables
EXPLAIN table Changes
PLAN_TABLE New columns:
  • SCAN_DIRECTION
  • EXPANSION_REASON

Changed columns:

  • MERGN (new values)
  • PRIMARY_ACCESSTYPE
DSN_PREDICAT_TABLE New columns:
  • EXPANSION_REASON

Changed columns:

  • ADDED_PRED (new values)
DSN_PREDICATE_SELECTIVITY table New table.
DSN_PTASK_TABLE New columns:
  • EXPANSION_REASON
DSN_QUERYINFO_TABLE New columns:
  • EXPANSION_REASON
DSN_QUERY_TABLE New columns:
  • EXPANSION_REASON
DSN_SORTKEY_TABLE New columns:
  • EXPANSION_REASON
DSN_SORT_TABLE New columns:
  • EXPANSION_REASON
DSN_STATEMNT_TABLE New columns:
  • EXPANSION_REASON
DSN_STRUCT_TABLE New columns:
  • EXPANSION_REASON
DSN_VIEWREF_TABLE New columns:
  • EXPANSION_REASON
DSN_VIRTUAL_INDEXES New columns:
  • KEYTARGET_COUNT
  • UNIQUE_COUNT
  • IX_EXTENSION_TYPE
  • DATAREPEATFACTORF
  • SPARSE
DSN_VIRTUAL_KEYTARGETS New table.

End program-specific programming interface information.