DB2 10.5 for Linux, UNIX, and Windows

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:

Call the SYSPROC.SYSINSTALLOBJECTS procedure:
```
CONNECT TO database-name
CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', 
        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
Linux AIX® Solaris HP-UX	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.