EXPLAIN table changes

Db2 13 introduces changes to the formats of certain EXPLAIN tables.

Tip: The changes in the highest available function levels are listed first. If you are preparing for migration to Db2 13, start with Function level 100 EXPLAIN table changes.
Important: Db2 13 introduces continuous delivery of new capabilities and enhancements in function levels. Most new capabilities become available only after activation of the Db2 13 function level that introduces them, or when applications run with the corresponding application compatibility level. For more information, see Activating Db2 13 function levels.

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 13 format during migration, or soon after migration. In Db2 13, the EXPLAIN function supports tables in Db2 13 or Db2 12 formats only. However, Db2 12 format EXPLAIN tables are deprecated. If you invoke EXPLAIN and Db2 12 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.

Function level 100 EXPLAIN table changes

The following changes to EXPLAIN tables take effect when you migrate to Db2 13 function level 100.

EXPLAIN table Description of changes APAR Incompatible change?
PLAN_TABLE New column: AP_PLANID PH48053 No
DSN_COLDIST_TABLE New column: AP_PLANID PH48053 No
DSN_KEYTGTDIST_TABLE New column: AP_PLANID PH48053 No
DSN_DETCOST_TABLE New column: AP_PLANID PH48053 No
DSN_FUNCTION_TABLE New column: AP_PLANID PH48053 No
DSN_PGRANGE_TABLE New column: AP_PLANID PH48053 No
DSN_PGROUP_TABLE New column: AP_PLANID PH48053 No
DSN_PREDICAT_TABLE New column: AP_PLANID PH48053 No
DSN_PTASK_TABLE New column: AP_PLANID PH48053 No
DSN_SORT_TABLE New column: AP_PLANID PH48053 No
DSN_STAT_FEEDBACK New column: AP_PLANID PH48053 No
DSN_STATEMENT_CACHE_TABLE New columns:
  • STMT_HASHID2
  • STMT_HASH2VER
  • AP_PLANID
  • AP_PLANHASH
  • AP_PLANHASHVER
  • CONNECTION_TYPE
  • CLIENT_USERID
  • CLIENT_APPLNAME
  • CLIENT_WRKSTNNAME
 PH48053 No
DSN_STATEMNT_TABLE New columns:
  • STMT_HASHID2
  • STMT_HASH2VER
  • AP_PLANID
  • AP_PLANHASH
  • AP_PLANHASHVER
  • AP_SERVICE_DATA
  • CONNECTION_TYPE
  • CLIENT_USERID
  • CLIENT_APPLNAME
  • CLIENT_WRKSTNNAME
 PH48053 No
DSN_VIEWREF_TABLE New column: AP_PLANID PH48053 No
DSN_FILTER_TABLE New column: AP_PLANID PH48053 No
DSN_QUERYINFO_TABLE New column: AP_PLANID PH48053 No