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 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.