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 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.

Feedback