EXPLAIN Tables

DB2 ESE uses explain tables to store access plan information so that users can see the decision that the optimizer has made. These tables are:

EXPLAIN_ARGUMENT
EXPLAIN_INSTANCE
EXPLAIN_OBJECT
EXPLAIN_OPERATOR
EXPLAIN_PREDICATE
EXPLAIN_STATEMENT
EXPLAIN_STREAM

The EXPLAIN tables must be created for each user ID that will run EXPLAIN (on each database). The tables can be created using Visual EXPLAIN (see below) or by a script run from the DB2 command line:

$ db2 ?tvf  $HOME/sqllib/misc/EXPLAIN.DDL

  where $HOME is the DB2 ESE instance owner's directory

For DB2 on OS/390, a slightly different procedure is required: For a user to be able to create his or her own explain tables on OS/390, the user must be granted CREATE TABLESPACE authority in the particular database in which the EXPLAIN tables will be created.

NOTE

Check with the DB2 on OS/390 SYSADM to get the appropriate authority granted or have the SYSADM person create the explain tables for your user ID.

There are two ways to create explain tables for DB2 on OS/390 host390:

From a TSO session via SPUFI, execute explain DDL
From a UNIX command line, connect to host390 using OS/390 user ID and password (i.e., tst1txp), and execute EXPLAIN.DDL