The Explain tables capture access plans when the Explain facility is activated.
- Call the SYSPROC.SYSINSTALLOBJECTS procedure:
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.
CONNECT TO database-name CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))
the EXPLAIN.DDL command file:
CONNECT TO database-name db2 -tf EXPLAIN.DDLThis command file creates explain tables under the current schema. The location of this command file depends on the operating system, as illustrated in the following table:
Operating system Location of the EXPLAIN command file Linux®
Located in the INSTHOME/sqllib/misc directory. INSTHOME is the instance home directory. Windows Located at the DB2PATH\misc directory on Windows operating systems. DB2PATH is the location where you install your Db2® copy
SYSPROC.SYSINSTALLOBJECTSprocedure 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.SYSINSTALLOBJECTSprocedure automatically avoids the use of NOT LOGGED clause.
- The session authorization ID for dynamic SQL
- The statement authorization ID for static SQL
- The SYSTOOLS schema if explain tables do not exist with the authorization ID schema
The population of the Explain tables by the Explain facility will not activate triggers or referential or check constraints. For example, if an insert trigger were defined on the EXPLAIN_INSTANCE table, and an eligible statement were explained, the trigger would not be activated.
To improve the performance of the Explain facility in a partitioned database system, it is recommended that the Explain tables be created in a single partition database partition group, preferably on the same database partition to which you will be connected when compiling the query.