The script (EXPLAIN.DDL) to create the explain tables is always located in ~sqllib/misc directory of your db2 instance. This script also create a couple of ADVISE tables which are used by the design advisor. The procedure to create the EXPLAIN tables is as follows:
cd ~sqllib/misc
db2 connect to SAMPLE
db2 -tnvf EXPLAIN.DDL
db2 terminate
Note: This procedure will create the EXPLAIN tables in your current schema.
Db2 also provides a system stored procedure to create the explain tables:
db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,CURRENT SCHEMA)"
Explain Table
EXPLAIN_INSTANCE Main control table for all Explain information.
EXPLAIN_STATEMENT Contains text of SQL statements.
EXPLAIN_ARGUMENT Represents unique characteristics for each individual operator.
EXPLAIN_OBJECT Contains data objects required by the generated access plan.
EXPLAIN_OPERATOR Contains operators needed to satisfy SQL statement.
EXPLAIN_PREDICATE Identifies predicates applied by a specific operator.
EXPLAIN_STREAM Represents input and output data streams between Operators and data objects.
EXPLAIN_DIAGNOSTIC Contains entries for diagnostic messages for explained statements.
EXPLAIN_DIAGNOSTIC_DATA Contains Message tokens for specific diagnostic messages.
ADVISE_INSTANCE Contains information about db2advis execution.
ADVISE_INDEX Represents the recommended indexes
ADVISE_WORKLOAD Represents the statement that makes up the workload.
ADVISE_MQT Information about Materialized Query Tables recommended by design advisor.
ADVISE_PARTITION Information about DB partitions recommended by design advisor.
ADVISE_TABLE Stores DDL for tables creation recommended by design advisor for MQTs and MDCs.
If you have migrated your database from v8 to v9, you will also need to migrate your EXPLAIN tables. The command to migrate explain tables is db2exmig and the syntax is given below:
db2exmig -d <dbname> -e <explain_schema>
example:
db2exmig -d SAMPLE -e USER1
| Comments |
|
Powered by !JoomlaComment 3.25
