DB2 Version 10.1 for Linux, UNIX, and Windows

Creating the explain tables

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:

  1. If the DB2® database manager has not already been started, issue the db2start command.
  2. From the CLP prompt, connect to the database that you want to use.
  3. 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.