db2exfmt takes very long time to complete

Technote (troubleshooting)


Problem(Abstract)

The explain table formatting utility, db2exfmt, with grapher option (-g), takes a long time to complete after running a query that caused a large amount of data to be loaded into the explain tables.

Symptom

The explain table formatting utility, db2exfmt, with grapher option (-g), takes a long time to complete after running a query that caused a large amount of data to be loaded into the explain tables.

Cause

After explaining a very large query access plan, or if there are a large number of explains in the explain tables, db2exfmt can take a very long time to format the information for 1 query. This can occur for query access plans with thousands of operators in them (1000's of rows in the EXPLAIN_OBJECT table).

Diagnosing the problem

If you observe a long running time for db2exfmt and normal running time for db2expln, you may have encountered this issue

Resolving the problem




The following options are available:

1. Getting the fixed versions for this issue (For DB2 LUW V9.1 or V9.5 versions):

For DB2 V9.1 LUW versions:
APAR IZ35481, APAR IZ35481 is fixed in V9.1FP7 or request the special build.

For DB2 V9.5:LUW versions:
APAR IZ35448, APAR IZ35448 is fixed in V9.5FP4 or request the special build.

2. Using the work around. Here are the work around steps (For DB2 V8 LUW versions, or DB2 LUW V9.1 versions and DB2 LUW V9.5 versions where APAR fixes are not included):

1). Create three new indexes using the following sql statements:

set current schema xxx;

CREATE INDEX OPR_I2 on EXPLAIN_OPERATOR(EXPLAIN_TIME, EXPLAIN_LEVEL, STMTNO, SECTNO, explain_requester, source_name, source_schema, source_version, OPERATOR_ID, operator_type, total_cost, io_cost);

CREATE INDEX STRM_I2 on EXPLAIN_STREAM(EXPLAIN_TIME, EXPLAIN_LEVEL, STMTNO, SECTNO, explain_requester, source_name, source_schema, source_version, target_id, source_id, stream_count);

CREATE INDEX ARG_I2 on EXPLAIN_ARGUMENT(EXPLAIN_TIME, EXPLAIN_LEVEL, STMTNO, SECTNO, OPERATOR_ID, argument_type);

2). AFTER the problem query is run, with explain mode set to explain or yes, run the following runstats sql commands BEFORE db2exfmt is issued:


db2 runstats on table xxx.EXPLAIN_OPERATOR with distribution and indexes all;
db2 runstats on table xxx.EXPLAIN_STREAM with distribution and indexes all;
db2 runstats on table xxx.EXPLAIN_ARGUMENT with distribution and indexes all;
db2 runstats on table xxx.EXPLAIN_INSTANCE with distribution and indexes all;
db2 runstats on table xxx.EXPLAIN_STATEMENT with distribution and indexes all;
db2 runstats on table xxx.EXPLAIN_OBJECT with distribution and indexes all;
db2 runstats on table xxx.EXPLAIN_PREDICATE with distribution and indexes all;
db2 runstats on table xxx.EXPLAIN_DIAGNOSTIC with distribution and indexes all;
db2 runstats on table xxx.EXPLAIN_DIAGNOSTIC_DATA with distribution and indexes all;

NOTE:

1. "xxx" is referring a schema name. Typically "xxx" with be one of:

    1. "SYSTOOLS",
    2. The authorization ID you normally use to connect to the database when you are going to explain a query (either using the explain statement, or the special register settings for "explain mode").
    3. The DB2 instance owner.
2. If there are multiple sets of explain tables under different schemas, these steps are required on all explain table schemas that contain explain data for large query access plans.

3. runstats needs be done AFTER the query is completed and BEFORE db2exfmt is issued.


Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows
Compiler- Query Optimizer

Software version:

8, 9.1, 9.5

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Software edition:

Enterprise Server, Personal, Workgroup Server

Reference #:

1322493

Modified date:

2008-11-03

Translate my page

Machine Translation

Content navigation