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 and input 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.
- 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.
- DSN_VIRTUAL_INDEXES
- The virtual indexes table, DSN_VIRTUAL_INDEXES, enables optimization
tools to test the effect of creating and dropping indexes on the performance
of particular queries.
- PLAN_TABLE
- The plan table, PLAN_TABLE, contains information about access paths that is collected from the
results of EXPLAIN statements.
EXPLAIN tables and input 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.
- 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_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_USERQUERY_TABLE
- The DSN_USERQUERY_TABLE table contains information about optimization
hints. The values identify statements that hints apply to, and specify
the purpose of the hint. Values in the DSN_USERQUERY_TABLE are used
to populate certain catalog tables when a BIND QUERY command is issued.
- 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.
- DSN_VIRTUAL_INDEXES
- The virtual indexes table, DSN_VIRTUAL_INDEXES, enables optimization
tools to test the effect of creating and dropping indexes on the performance
of particular queries.
- PLAN_TABLE
- The plan table, PLAN_TABLE, contains information about access paths that is collected from the
results of EXPLAIN statements.
EXPLAIN tables and input tables for DB2 11 for z/OS
All of the EXPLAIN tables for DB2 11 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.
- 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_PREDICATE_SELECTIVITY
The predicate selectivity table contains information about the selectivity of predicates that are
used for access path selection. It is used as an input table for the BIND QUERY command when
selectivity overrides are specified. The selectivity override feature in InfoSphere Optim Query
Workload Tuner is available for DB2 11 for z/OS new-function mode or later.
- 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_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_SORT_TABLE
- The sort table, DSN_SORT_TABLE, contains information about the sort operations required by a
query.
- 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_STAT_FEEDBACK
The DSN_STAT_FEEDBACK table contains recommendations for capturing missing or conflicting
statistics that are defined during EXPLAIN. Collecting these statistics by the RUNSTATS utility
might improve the performance of the 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_USERQUERY_TABLE
- The DSN_USERQUERY_TABLE table contains information about optimization hints. The values identify
statements that hints apply to, and specify the purpose of the hint. Values in the
DSN_USERQUERY_TABLE are used to populate certain catalog tables when a BIND QUERY command is
issued.
- On DB2 11 for z/OS
new-function mode or later, this table also contains information that is used by the selectivity
override feature.
- 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.
- DSN_VIRTUAL_INDEXES
- The virtual indexes table, DSN_VIRTUAL_INDEXES, enables optimization tools to test the effect of
creating and dropping indexes on the performance of particular queries.
- DSN_VIRTUAL_KEYTARGETS
- The DSN_VIRTUAL_KEYTARGETS table contains information about expression-based indexes and XML
indexes.
- PLAN_TABLE
- The plan table, PLAN_TABLE, contains information about access paths that is collected from the
results of EXPLAIN statements.