Report Command Reference - EXPLAIN command

EXPLAIN command

This section provides an overview of the EXPLAIN command.

Usage

Use the OMEGAMON® XE for DB2® PE EXPLAIN command to produce explain reports.

Usage notes

You can specify any number of EXPLAIN commands in the OMEGAMON XE for DB2 PE command stream.
For each EXPLAIN command a separate OMEGAMON XE for DB2 PE explain report is produced.
Some parameters can include a wildcard (*) provided that it is the last character in the text string.

Syntax

>>---EXPLAIN----+-PLAN-----+----object specifications----------><
                +-PACKAGE--+
                +-QMFQUERY-+
                +-SQLSTMT--+
                '-QUERYNO--'

Subcommands and options

Each of the functions is identified with an appropriate keyword, followed by various subcommands and options, which identify the object being explained and control the amount of detail being produced.

The following sections identify the subcommands available for each function. Here you find a description of the EXPLAIN subcommands and options that can be specified with the EXPLAIN and GLOBAL commands:

ACCTYPE

Can be specified for the PLAN and PACKAGE keywords to control the statements that are to be explained within the plan or package based on the chosen access path. The following values are available:

ALL: For each explainable SQL statement in the plan, a report is produced. This is the default value.
MATCHING: Only explainable SQL statements where an access path of matching index scan has been selected, are processed and reported.
NONMATCH: Only explainable SQL statements where an access path of nonmatching index scan has been selected, are processed and reported.
TABSCAN: Only explainable SQL statements where an access path of table space scan has been selected, are processed and reported.

Note:

ACCTYPE selection, as with any other OMEGAMON XE for DB2 PE explain selection, applies to individual PLAN_TABLE rows, not to the entire SQL statement. For example, if a particular SQL statement is executed in two steps, the first using matching index scan and the second using nonmatching index scan, and ACCTYPE(MATCHING) is specified, only the first step is reported.

DBRM

Can be specified for the PLAN keyword to control the DBRMs to be explained within the plan. If DBRM is not specified, all DBRMs within the plan are explained. If only a given DBRM within the plan is to be explained, you must specify the actual DBRM name in DBRM. If all DBRMs with a given name pattern are to be explained, a wildcard (*) can be used.

DEGREE

Can be specified for the QMFQUERY and SQLSTMT keywords to indicate whether the SQL statement or statements are eligible for query parallelism. The following values are available:

1: The SQL statement does not use query parallelism. This is the default.
ANY: The SQL statement is eligible for query parallelism.
DSJ: Disable star join.
ESJ: Enable star join.

FIRST/LAST

Specifies the number of the first and the last statement in the plan or package to be explained, to control the range of SQL statements to be explained within the plan or package.

If FIRST is not specified, a value of 1 is used. If LAST is not specified, a value of 999 999 999 is used. If FIRST is greater than LAST, both parameters are set to the value of FIRST.

FORCE

Can be specified for the PACKAGE keyword to control the explanation of the SQL statements in a package. The following values are available:

NO: If more than one package is referenced, the statements are only explained if the total number of SQL statements is less than 300. This is the default value.
YES: All statements in all packages that conform to the specification are explained.

FORMAT

Can be specified for the PLAN and PACKAGE keywords to control the formatting of the SQL statements in the plan or package. The following values are available:

YES: The SQL statement is formatted so that a new line is started for SQL keywords such as SELECT, INTO, FROM, WHERE. Subselects, however, are not indented. This is the default value.
NO: The SQL statement is formatted so that a new line is only started for the SQL keywords SELECT and UNION. By using this option, the SQL statement uses minimum page space.

GEN

Controls the number of version generations in a package to be explained, with PLAN and PACKAGE.

Specify a value from 1 to 99 to overwrite the default. For PLAN, if GEN is not specified, the default is 1.

For PACKAGE, if GEN is not specified, the default is 1, when no wildcard is used in the version ID of the package. If a wildcard is present, a value of 99 is used.

HOSTVAR

Can be specified for the PLAN and PACKAGE keywords to control the listing of host variable specifications. The following values are available:

NO: No host variable definitions are listed in the report. This is the default value.
YES: A listing of all (maximum 500) host variables used in the SQL statement is produced. This listing includes the definition type, length, and null characteristics.

INDEX

Controls the level of index information unless the index data block is excluded by using the LEVEL option. The following values are available:

YES: If DB2 has selected a matching or nonmatching index scan, OMEGAMON XE for DB2 PE explain shows all index information for the selected index including key column information. If DB2 has selected a table space scan, OMEGAMON XE for DB2 PE explain shows detailed index information for all indexes of the accessed table. This is the default value.
NO: No index information is shown in the report.
ALL: Information for all indexes created for the table (including key column information) is shown in the report. The information is listed after the table details.

LEVEL

Controls which of the following blocks of data the OMEGAMON XE for DB2 PE explain report contains:

"Raw" SQL EXPLAIN data as found in the PLAN_TABLE
Access path data
Table and table space data
Index data
Key data
Distribution of the ten mostly used keys
Plan/package bind data, if applicable
Host variables data, if applicable
Summary report

The table below summarizes which values can be specified and which blocks are reported.

Table 9. LEVEL values
LEVEL	Raw SQL Explain	Access Path Data	Table (Space) Data	Index Data	Key Data	Top Ten Key Dist.	Plan/ Package Data	Host Variables	Summary Report
SUMMARY									•
SQL		•							•
BASIC	•	•							•
INDEXES	•	•	•	•			•	•	•
DETAIL	•	•	•	•	•		•	•	•
NORAWXPL		•	•	•	•		•	•	•
KEYDIST	•	•	•	•	•	•	•	•	•
(None)	•	•	•	•	•		•	•

LOCATION

Can be specified for the PLAN keyword to determine the location of the plan. If LOCATION is not specified, the local location, that is, the location specified in the SSID option, is used.

When a valid location name is specified, OMEGAMON XE for DB2 PE explain connects to the specified location and EXPLAIN PLAN processing continues at that location. To be valid, the location name must appear in the LOCATION column of the SYSIBM.SYSLOCATIONS table, or be the local DB2 subsystem's location name.

OWNER

Can be specified for the GLOBAL and QUERYNO keywords to control the authorization ID of the PLAN_TABLE for the current request. To select the correct PLAN_TABLE, OMEGAMON XE for DB2 PE explain looks for the authorization ID specified in the OWNER option of QUERYNO. If it is not specified, the OWNER value in GLOBAL is used. If OWNER is not specified in GLOBAL, OMEGAMON XE for DB2 PE explain uses the authorization ID of the job submitter.

PACKAGES

Can be specified for the PLAN keyword to control the explanation of statements in packages within a plan. The following values are available:

YES: For each package in the plan, only statements in the most recent version are explained. This is the default value.
NO: No statements in the packages are explained.
ALL: All statements in all versions of the packages are explained.

PACKLIMIT

Controls the number of packages to be explained.

A particular plan can consist of more packages than has been specified in PACKLIMIT (default is 100). In this case, a report listing all packages is produced but no SQL statements in these packages are explained. PACKLIMIT does not affect DBRMs.

PLANEXPLAIN

One of the steps in OMEGAMON XE for DB2 PE installation is to bind the OMEGAMON XE for DB2 PE explain application. The default name for this plan is KO2EXPL.

If you do not want to use the default name, you can specify the name of the OMEGAMON XE for DB2 PE explain plan at OMEGAMON XE for DB2 PE execution time. This is done by specifying PLANEXPLAIN(xxxxxxxx) in GLOBAL, where xxxxxxxx is the OMEGAMON XE for DB2 PE explain plan name.

SQLID

Defines the current SQL ID. If you specify SQLID(USER), the primary SQL ID is set. Otherwise, a valid secondary authorization ID is set. This option has the same effect and is used in the same context as the SQL statement SET CURRENT SQLID.

SSID

Identifies the DB2 subsystem where the object specified in EXPLAIN resides. If SSID is not specified in either EXPLAIN or GLOBAL, an error message is issued and OMEGAMON XE for DB2 PE resumes processing with the next command.

TABLE

Can be specified for the PLAN and PACKAGE keywords to control the statements that are to be explained within the plan or package. If TABLE is not specified, all statements within the plan or package are explained.

If only statements accessing a given table are to be explained, you must specify the actual table name in TABLE. If only statements accessing tables with a given name pattern are to be explained, you can use a wildcard (*).