Creating EXPLAIN tables

Before you or optimization tools can capture and analyze EXPLAIN information, you must create the appropriate EXPLAIN tables to hold the information.

About this task

Db2 uses EXPLAIN tables to store information about the access plan that is uses to process SQL queries, and the tables are populated when the EXPLAIN function is invoked by you, by bind options, or by certain optimization tools. You can create one or more EXPLAIN tables that are qualified by a user ID for your own use and EXPLAIN tables that are qualified by SYSIBM.

Procedure

To create or upgrade EXPLAIN tables to the format for Db2 12, take one of the following actions:

Call the ADMIN_EXPLAIN_MAINT stored procedure.
Create a PLAN_TABLE and any other EXPLAIN tables qualified by a user ID by modifying the sample CREATE TABLE statements in the DSNTESC member of the SDSNSAMP library.
Only the PLAN_TABLE is required to enable the basic EXPLAIN function. The other tables support analysis of the specific types of information that each table captures.