IBM Support

[Db2] How to create and remove Explain tables.

Question & Answer


Question

How can I create and remove Explain tables, which was used to collect access plan information?

Answer

Explain tables are used to store access plan information when a user takes an Explain. Users need to create Explain tables before one uses Explain function.

[How to create Explain tables in Db2 9.1 or earlier]
You can create Explain tables by running SQLs in EXPLAIN.DDL file, which is provided by Db2. The file can be found in $INSTHOME/sqllib/misc directory in Unix and Linux systems, or in %DB2PATH%\MISC directory in Windows systems.
EXPLAIN.DDL does not explicitly specify schema or table space where the Explain tables are created. These tables are created with current schema, and in table space where the user has USE privilege. Here are the sample commands on how to execute the DDL.
  [Unix and Linux]
  $ db2 connect to [database]
  $ db2 -tf ~/sqllib/misc/EXPLAIN.DDL
  [Windows]
  > db2 connect to [database]
  > db2 -tf "%DB2PATH%\MISC\EXPLAIN.DDL"

[How to remove Explain tables in Db2 9.1 or earlier]
Run DROP statement against the objects created by EXPLAIN.DDL file.
There are dependencies between some of these objects, therefore issue DROP in reverse order of creation.
 

[How to create Explain tables in Db2 9.5 or later]
In Db2 9.5 or later, you can still create Explain tables as same ways as 9.1, but another method is available. Db2 produced SYSINSTALLOBJECTS proceure to create Explain tables.
The 3rd argument of the SYSINTALLOBJECTS procedure specifies the table space where the Explain table is created. The 4th argument specifies the schema of the Explain tables. If NULL or null string was specified, Explain tables is created in SYSTOOLSPACE table space with SYSTOOLS schema. If SYSTOOLSPACE does not exist, it's created automatically.

In the following example, Explain tables is created in SYSTOOLSPACE table space with the schema of connecting user ID.

$ db2 connect to [database]
$ db2 "call SYSPROC.SYSINSTALLOBJECTS( 'EXPLAIN', 'C' , '', CURRENT USER )"
[How to remove Explain tables in Db2 9.5 or later]
You can remove Explain tables by using SYSINSTALLOBJECTS procedure. Specify the schema of Explain tables to be dropped in 4th parameter. If NULL or null string was specified, Explain tables with SYSTOOLS schema is removed.

In the following example, Explain tables, which have schema of connecting user ID, is removed.

$ db2 connect to [database]
$ db2 "call SYSPROC.SYSINSTALLOBJECTS( 'EXPLAIN', 'D', '', CURRENT USER )"
Operational consideration
  • How to maintain contents of Explain tables
    If you want to delete unnecessary rows in Explain tables, you delete rows in EXPLAIN_INSTANCE table. Between Explain tables, referential integrity with CASCADE option is defined. All the related rows in other Explain tables are automatically deleted.
    Therefore, if you want to delete all data in Explain tables, delete all rows from EXPLAIN_INSTANCE table. In the following example, you can delete all data in Explain tables for current schema.

    $ db2 connect to [database]
    $ db2 delete from EXPLAIN_INSTANCE

    NOTE: You might want to specify table schema to delete Explain information for specific Explain schema.

    If there are too many rows in Explain tables, the delete statement can fail with "transaction log for the database is full" error (SQL0964C). In this case, refer the technote [Db2] How to solve transaction log for the databases is full (SQL0964C)

  • Upgrading Explain tables
    The configuration of Explain table can vary depending on the version of Db2.
    If you want to keep the contents of Explain tables taken in older version, you should run db2exmig command to migrate existing Explain table configuration.
    Upgrading explain tables

Related information
Explain tables and the organization of explain information
SYSINSTALLOBJECTS procedure

 

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkcAAE","label":"Compiler"},{"code":"a8m500000008PkyAAE","label":"Compiler-\u003EOptimization db2explain db2advis"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
07 April 2023

UID

ibm11074771