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 EXPLAIN tables:

Modify and run the appropriate sample job, or issue the CREATE TABLE statements, depending on the purpose of the tables:
Option Description
EXPLAIN tables for your own use Create a PLAN_TABLE and any additional plan tables qualified by a user ID by modifying the sample CREATE TABLE statements in the DSNTESC member of the SDSNSAMP library. Only PLAN_TABLE is required to enable the basic EXPLAIN function, but you can create additional tables to analyze the types of information that they capture.
Start of changeEXPLAIN tables for use by optimization toolsEnd of change Start of changeEXPLAIN tables, qualified by SYSIBM, are created for use by SQL optimization tools when you run job DSNTIJSG to install and configure DB2-supplied routines. You might also be able to create the required tables, or specify an existing set of EXPLAIN tables, from the optimization tool's client interface.End of change