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.
To create EXPLAIN tables:
|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.|
|EXPLAIN tables for use by optimization tools||EXPLAIN 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.|