Lists of required EXPLAIN tables

The following EXPLAIN tables must be present on each subsystem that you use for tuning SQL statements and query workloads.

The tables must be accessible to all users who need to carry out tuning activities.

Each set of tables must be in a single schema. For example, you cannot create some of a single set of EXPLAIN tables in schema A and the rest of the tables in schema B. The entire set of EXPLAIN tables must be in schema A or in schema B. Moreover, you cannot create an alias on only a subset of EXPLAIN tables. You must create it on the full set.

EXPLAIN tables for DB2 Version 9.1 for z/OS

All of the EXPLAIN tables for DB2® Version 9.1 for z/OS® must be present when you are tuning SQL statements and query workloads that run on that version of DB2 for z/OS.

PLAN_TABLE
The plan table, PLAN_TABLE, contains information about access paths that is collected from the results of EXPLAIN statements.
DSN_DETCOST_TABLE
The detailed cost table, DSN_DETCOST_TABLE, contains information about detailed cost estimation of the mini-plans in a query.
DSN_FILTER_TABLE
The filter table, DSN_FILTER_TABLE, contains information about how predicates are used during query processing.
DSN_FUNCTION_TABLE
The function table, DSN_FUNCTION_TABLE, contains descriptions of functions that are used in specified SQL statements.
DSN_PGRANGE_TABLE
The page range table, DSN_PGRANGE_TABLE, contains information about qualified partitions for all page range scans in a query.
DSN_PGROUP_TABLE
The parallel group table, DSN_PGROUP_TABLE, contains information about the parallel groups in a query.
DSN_PREDICAT_TABLE
The predicate table, DSN_PREDICAT_TABLE, contains information about all of the predicates in a query.
DSN_PTASK_TABLE
The parallel tasks table, DSN_PTASK_TABLE, contains information about all of the parallel tasks in a query.
DSN_QUERYINFO_TABLE
The query information table, DSN_QUERYINFO_TABLE, contains information about the eligibility of query blocks for automatic query rewrite, information about the materialized query tables that are considered for eligible query blocks, reasons why ineligible query blocks are not eligible, and information about acceleration of query blocks.
DSN_QUERY_TABLE
The query table, DSN_QUERY_TABLE, contains information about a SQL statement, and displays the statement before and after query transformation.
DSN_SORTKEY_TABLE
The sort key table, DSN_SORTKEY_TABLE, contains information about sort keys for all of the sorts required by a query.
DSN_SORT_TABLE
The sort table, DSN_SORT_TABLE, contains information about the sort operations required by a query.
DSN_STATEMENT_CACHE_TABLE
The statement cache table, DSN_STATEMENT_CACHE_TABLE, contains information about the SQL statements in the statement cache, information captured as the results of an EXPLAIN STATEMENT CACHE ALL statement.
DSN_STATEMNT_TABLE
The statement table, DSN_STATEMNT_TABLE, contains information about the estimated cost of specified SQL statements.
DSN_STRUCT_TABLE
The structure table, DSN_STRUCT_TABLE, contains information about all of the query blocks in a query.
DSN_VIEWREF_TABLE
The view reference table, DSN_VIEWREF_TABLE, contains information about all of the views and materialized query tables that are used to process a query.

EXPLAIN tables for DB2 10 for z/OS

All of the EXPLAIN tables for DB2 10 for z/OS must be present when you are tuning SQL statements and query workloads that run on that version of DB2 for z/OS.

PLAN_TABLE
The plan table, PLAN_TABLE, contains information about access paths that is collected from the results of EXPLAIN statements.
DSN_COLDIST_TABLE
The column distribution table contains non-uniform column group statistics that are obtained dynamically by DB2 from non-index leaf pages..
DSN_DETCOST_TABLE
The detailed cost table, DSN_DETCOST_TABLE, contains information about detailed cost estimation of the mini-plans in a query.
DSN_FILTER_TABLE
The filter table, DSN_FILTER_TABLE, contains information about how predicates are used during query processing.
DSN_FUNCTION_TABLE
The function table, DSN_FUNCTION_TABLE, contains descriptions of functions that are used in specified SQL statements.
DSN_KEYTGTDIST_TABLE
The key-target distribution table contains non-uniform index expression statistic that are obtained dynamically by the DB2 optimizer.
DSN_PGRANGE_TABLE
The page range table, DSN_PGRANGE_TABLE, contains information about qualified partitions for all page range scans in a query.
DSN_PGROUP_TABLE
The parallel group table, DSN_PGROUP_TABLE, contains information about the parallel groups in a query.
DSN_PREDICAT_TABLE
The predicate table, DSN_PREDICAT_TABLE, contains information about all of the predicates in a query.
DSN_PTASK_TABLE
The parallel tasks table, DSN_PTASK_TABLE, contains information about all of the parallel tasks in a query.
DSN_QUERY_TABLE
The query table, DSN_QUERY_TABLE, contains information about a SQL statement, and displays the statement before and after query transformation.
DSN_SORTKEY_TABLE
The sort key table, DSN_SORTKEY_TABLE, contains information about sort keys for all of the sorts required by a query.
DSN_SORT_TABLE
The sort table, DSN_SORT_TABLE, contains information about the sort operations required by a query.
DSN_STATEMENT_CACHE_TABLE
The statement cache table, DSN_STATEMENT_CACHE_TABLE, contains information about the SQL statements in the statement cache, information captured as the results of an EXPLAIN STATEMENT CACHE ALL statement.
DSN_STATEMNT_TABLE
The statement table, DSN_STATEMNT_TABLE, contains information about the estimated cost of specified SQL statements.
DSN_STRUCT_TABLE
The structure table, DSN_STRUCT_TABLE, contains information about all of the query blocks in a query.
DSN_VIEWREF_TABLE
The view reference table, DSN_VIEWREF_TABLE, contains information about all of the views and materialized query tables that are used to process a query.