Explain reports

OMEGAMON for Db2 Performance Expert provides Explain reports by query number, statement text, plan, package, and QMF query.

Query number
You can investigate a particular SQL statement identified by its query number when:
  • A dynamic SQL EXPLAIN statement was executed with a given query number. The statement can be from Db2 or QMF. If the query number is not specified, Db2 assigns a number. You can get the query number from the PLAN_TABLE.
  • The application was bound (or rebound) with EXPLAIN(YES). In this instance, the statement query number is assigned by the precompiler.

Explain searches for the query number in the job submitter's PLAN_TABLE, unless a different owner is specified. If you want to run an Explain report for a statement that you do not own, you need access to the owner's plan table.

Statement text

Can be used to investigate a particular SQL statement that is supplied in its text form.

Explain uses 999 735 911 as a query number. If this statement number already exists in the PLAN_TABLE, the row is deleted before processing the SQL statement. After successful execution of SQL EXPLAIN, the newly inserted row in the PLAN_TABLE is used to produce the Explain report.

Plan

This reports on all or selected SQL statements contained in an application plan.

To create a report for a plan, that plan must exist in the catalog table SYSIBM.SYSPLAN and must have been bound with EXPLAIN(YES).

Package

This reports on SQL statements in a package.

To create a report for a package, that package must exist in the catalog table SYSIBM.SYSPACKAGE and must have been bound with EXPLAIN(YES).

QMF query

Can be used to investigate a saved QMF query written in SQL. QBE and PROMPTED queries must be converted to SQL before they can be explained.

You can create reports about your own saved QMF queries, and queries created by other users, saved with SHARE=YES.

The QMF query can contain parameters, for example, and &PARM1 and &PARM2. These parameters can also substitute column names in the select list. However, the query must not contain literals and other strings with one or more ampersands (&), enclosed between quotes (').

If there is more than one entry in the PLAN_TABLE with the same identifiers, the most recent entry is reported.