EXPLAIN QUERYNO specifications

This section describes the EXPLAIN QUERYNO specifications.

Usage

Use this specification to check if a particular SQL statement that is identified by its query number. You can obtain the query number as follows:
  • A dynamic SQL EXPLAIN statement has been executed with a given query number. The SQL EXPLAIN statement might have been executed from either DB2I or QMF. If the query number is not specified in the SQL EXPLAIN statement, Db2 assigns a number. You can obtain the query number directly from the PLAN_TABLE.
  • The application has been bound with the EXPLAIN(YES) option on the BIND or REBIND commands. The query number is the statement number that was assigned by the precompiler and placed in the DBRM.
    Note: If a statement belonging to a particular plan or package is to be explained, EXPLAIN PLAN or EXPLAIN PACKAGE specifications are better suited than EXPLAIN QUERYNO. By using EXPLAIN PACKAGE or EXPLAIN PLAN options such as FIRST, LAST, or DBRM, the statement can be better identified.

Usage notes

  • OMEGAMON for Db2 PE EXPLAIN searches for the query number in the job submitter's PLAN_TABLE, unless the OWNER keyword with a different user ID is specified.
  • If the job submitter has SELECT authorization to another user's PLAN_TABLE, the job submitter can select EXPLAIN information from this table, by specifying the other user's authorization ID as the OWNER option.
  • If the specified query number does not exist in the PLAN_TABLE, a warning message is issued. OMEGAMON for Db2 PE resumes processing with the next request.

Syntax of the EXPLAIN QUERYNO specification

Read syntax diagramSkip visual syntax diagramEXPLAIN QUERYNO( query number )SSID1(Db2 subsystem id)SQLID(secondary authidUSER)LEVEL(DETAILBASICINDEXESKEYDISTSQLSUMMARYNORAWXPL)INDEX(YESNOALL)OWNER(authidUSER)DEGREE(1ANY)
Notes:
  • 1 SSID is required unless specified in a preceding GLOBAL command.

Subcommand options

Note: This section only explains specific subcommands. The other subcommands and options are described in Subcommands and options. The following list gives additional or specific descriptions of selected options, where appropriate.
query number
Identifies a query number in the PLAN_TABLE being accessed.