How Explain works

Explain creates a set of unique, historical baseline snapshots that contain key Db2 catalog statistics for an application.

Explain requires the ADMIN_EXPLAIN_MAINT Db2 stored procedure. ADMIN_EXPLAIN_MAINT creates explain tables if they do not exist for a dynamic Explain request. For more information, see SQL-Explorer-and-Db2-authority-requirements.

Overview-of-the-Explain-function

The snapshot information is stored in Db2 tables and includes:

SQL text
Dependent objects
Catalog information that influences the Db2 optimizer
Access path information

Explain derives access path information from the Db2 explain tables. The product puts this information in context with the database and system environments at a particular point in time. By capturing the environmental variables and correlating them to the access path information, Explain provides additional information that helps you to understand the performance of your applications.

When an application has performance problems, you normally look for the access paths in the contents of the last plan table and then look for environmental statistics in the Db2 catalog. Because the catalog is always changing, the most recent statistics differ from the statistics that were applicable when the application was first implemented—and possibly when it was last bound. When the application is implemented again, it might perform poorly due to differences in the environment. In such a case, a dynamic Explain can analyze the most current statistics and access paths that the Db2 optimizer chose.

You could compare the results of this dynamic Explain to a static Explain if you had already performed a bind with EXPLAIN(YES). After analyzing the differences between the two Explains, you could perform a What-If Edit SQL Explain by editing the SQL and comparing that Explain to the existing dynamic and static Explains.

Important

SQL Performance for DB2 and BMC AMI Command Center for Db2 users also have a What-If Index feature that allows them to add, drop, or update statistics on one or more indexes using cloned structures. For more information, see the BMC AMI SQL Performance for Db2.

Explain results are based on the environment that existed when the Db2 optimizer selected the access paths. If you specify Y for the HISTORY parameter when you analyze an object in batch, Explain saves the access path information and related catalog statistics to the product history tables for future reporting or for the SQL Explorer Compare function to use. You can correlate the historical information to the access paths that the Db2 optimizer chose. You can use this information to develop additional reports that are designed to find potential problems.

For example, if you want to find applications that are performing table space scans on tables larger than 100 rows, a query against the history tables provides the answer.

Explain can process plans, packages, DBRM libraries (DBRMLIBs), or any ad hoc SQL statement. Explain generates a formatted report that includes the basic information from the plan table and catalog statistics, supplemented by text from a set of predefined expert rules.

The following are the Explain tables that SQL Performance uses for various explains and index advisor depending on the functions being used:

PLAN_TABLE
DSN_STATEMNT_TABLE
DSN_STAT_FEEDBACK
DSN_DETCOST_TABLE
DSN_PREDICAT_TABLE
DSN_QUERYINFO_TABLE
DSN_FILTER_TABLE
DSN_STATEMENT_CACHE_TABLE
DSN_SORT_TABLE
DSN_SORTKEY_TABLE
DSN_VIRTUAL_INDEXES

You can use the database and STOGROUPS used for BMCPERF database, if they exist. The default STOGROUP is SYSDEFLT.

If you don't specify either a table space or a database, a database and a table space are implicitly created. The database is created with the following name:

DSNxxxxx, where xxxxx is a five-digit number.

In the Db2 catalog, you can see auxiliary tables named DSN_% that are created to support LOB columns defined in the explain tables.

How Explain works

BMC AMI SQL Explorer for Db2 13.1

On this page