A number of steps are required to create explain
tables.
About this task
To create explain snapshots, you must ensure that the
following explain tables exist for your user ID:
- EXPLAIN_INSTANCE
- EXPLAIN_STATEMENT
To check if they exist, use the
LIST TABLES command.
Procedure
If the explain tables do not exist, you must create them
using the following instructions:
-
If the Db2®
database manager has not already been started, issue the db2start command.
- From the CLP prompt, connect
to the database that you want to use.
-
Create the explain tables by 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 command
file:
db2 CONNECT TO database-name
db2 -tf EXPLAIN.DDL
This
command file creates explain tables under the current schema. It is 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 the BLOCKNONLOGGED parameter is set
to yes, some statements in EXPLAIN.DDL fail
because NOT LOGGED clause is used for LOB columns.
However, the SYSPROC.SYSINSTALLOBJECTS
procedure
automatically avoids the use of NOT LOGGED clause.