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 the alias on the full set of EXPLAIN tables.
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, and information
that was captured as the result 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, and information
that was captured as the result 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.