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:
db2 CONNECT TO database-name
db2 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 DB2® command
file:
db2 CONNECT TO database-name
db2 -tf EXPLAIN.DDL
This command file creates explain
tables under the current schema.It is located at the DB2PATH\misc
directory on Windows operating
systems, and the INSTHOME/sqllib/misc directory on Linux and UNIX operating
systems. DB2PATH is the location where you install your DB2 copy and INSTHOME is the instance home directory.
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.