Explain tables

The Explain tables capture access plans when the Explain facility is activated.

The Explain tables must be created before Explain can be invoked. You can create them using one of the following methods:
    CONNECT TO database-name
            CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))
    This call creates the explain tables under the SYSTOOLS schema. To create them under a different schema, specify a schema name as the last parameter in the call.
  • Run the EXPLAIN.DDL command file:
    CONNECT TO database-name
    db2 -tf EXPLAIN.DDL
    This command file creates explain tables under the current schema. The location of this command file depends on the operating system, as illustrated in the following table:
    Operating system Location of the EXPLAIN command file
    Located in the INSTHOME/sqllib/misc directory. INSTHOME is the instance home directory.
    Windows Located at the DB2PATH\misc directory on Windows operating systems. DB2PATH is the location where you install your Db2® copy

    Calling the SYSPROC.SYSINSTALLOBJECTS procedure is preferred over using the EXPLAIN.DDL file since it can automatically adapt to different database configurations. For example, if BLOCKNONLOGGED parameter is set to yes, then some statements in EXPLAIN.DDL fail because NOT LOGGED clause is used for LOB columns. However, if BLOCKNONLOGGED parameter is set to yes then the SYSPROC.SYSINSTALLOBJECTS procedure automatically avoids the use of NOT LOGGED clause.

The Explain facility uses the following IDs as the schema when qualifying Explain tables that it is populating:
  • The session authorization ID for dynamic SQL
  • The statement authorization ID for static SQL
  • The SYSTOOLS schema if explain tables do not exist with the authorization ID schema
The schema can be associated with a set of Explain tables, or aliases that point to a set of Explain tables under a different schema. If no Explain tables are found under the schema, the Explain facility checks for Explain tables under the SYSTOOLS schema and attempts to use those tables.

The population of the Explain tables by the Explain facility will not activate triggers or referential or check constraints. For example, if an insert trigger were defined on the EXPLAIN_INSTANCE table, and an eligible statement were explained, the trigger would not be activated.

To improve the performance of the Explain facility in a partitioned database system, it is recommended that the Explain tables be created in a single partition database partition group, preferably on the same database partition to which you will be connected when compiling the query.