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:

  1. From a TSO session via SPUFI, execute explain DDL

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



Advanced DBA Certification Guide and Reference for DB2 UDB v8 for Linux, Unix and Windows
Advanced DBA Certification Guide and Reference for DB2 Universal Database v8 for Linux, UNIX, and Windows
ISBN: 0130463884
EAN: 2147483647
Year: 2003
Pages: 121

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net