EXPLAIN command

This section provides an overview of the EXPLAIN command.

Usage

Use the OMEGAMON for Db2 PE EXPLAIN command to produce explain reports.

Usage notes

  • You can specify any number of EXPLAIN commands in the OMEGAMON for Db2 PE command stream.
  • For each EXPLAIN command a separate OMEGAMON 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

Read syntax diagramSkip visual syntax diagramEXPLAINPLANPACKAGEQMFQUERYSQLSTMTQUERYNOobject specifications

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 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 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 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 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 1. 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 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 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 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 for Db2 PE installation is to bind the OMEGAMON 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 for Db2 PE explain plan at OMEGAMON for Db2 PE execution time. This is done by specifying PLANEXPLAIN(xxxxxxxx) in GLOBAL, where xxxxxxxx is the OMEGAMON 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 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 (*).