IBM Support

How do I create DB2 Explain tables?

Question & Answer


Question

How do I create DB2 Explain tables ?

Answer

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 Explain tables using one of the two methods -

a)db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',<tablespace_name>,<schema_name>)"

Example:
$ db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C','USERSPACE1','db2inst1')"

  Return Status = 0

If the schema name is not specified, by default explain tables are created under the SYSTOOLS schema.

b) To create under current schema use -
cd INSTHOME/sqllib/misc (for Windows cd DB2PATH\misc)
db2 CONNECT TO <dbname>
db2 -tvf EXPLAIN.DDL

You can also use the SYSPROC.SYSINSTALLOBJECTS table function to delete the Explain tables:
db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','D',<tablespace_name>,<schema_name>)"

Example:
$ db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','D','USERSPACE1','db2inst1')"

Return Status = 0

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Tables","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;9.5;10.1;10.5","Edition":"Advanced Enterprise Server;Advanced Workgroup Server;Enterprise Server;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21685237