Limited supportBMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Ops Monitor for Db2 13.1 image-2024-5-19_8-5-1.png .

Explain parameters

You can use the following parameters in your Explain job.

The equal sign (=) is required for these parameters where shown. You can include more than one object in a batch job by using a wildcard character or by repeating the --NEWOBJ/--ENDOBJ set of parameters in the SYSIN.

Parameter name

Execution parameter

Description

ACCEL= (optional)

NA

Whether Analytics are displayed:

  • Y (default) specifies that Accelerator rules are displayed.
  • N specifies that Accelerator rules are not displayed.

--BEGSQL (optional; required only for OBJECT= SQLTEXT)

NA

Start-delimiter for inline SQL associated with OBJECT=SQLTEXT

When Explain encounters this parameter, it starts processing the input lines as inline SQL.

Note

You must also delimit the end of the inline SQL with the --ENDSQL parameter.

BMCMSG= (optional)

NA

Use and display format of the BMC rule messages produced in the report

Specify one of the following values:

  • 1 displays the rule message with the message text, the severity of the rule message, and the BMC Message ID (default).
  • 2 produces the rule messages with only the message text.
  • 3 bypasses all rule message processing.

Note

This parameter is for use only in batch jobs.

COLBRK= (optional)

NA

Whether a break occurs between columns listed as part of the SELECT clause in the SQL text

Specify one of the following values:

  • Y specifies that the break occurs.
  • N specifies that the break does not occur. (default)

COLLID= (optional)

08

Collection ID for packages

If you omit this value, this parameter is not part of the selection criteria. The maximum length is 128. 1

COMPARE= (optional)

NA

Whether to compare the dynamic Explain with the static Explain for the object

Specify one of the following values:

  • Y specifies that the comparison occurs.
  • N specifies that the comparison does not occur (default).

CONTOKEN= (optional)

NA

Contoken for packages, used to provide the CHAR value of the consistency token

The length must be 16.

CURRENCY= (optional)

NA

Three-character currency code for the monetary unit used to calculate the cost translation rate

You can use any characters except spaces in this field, though BMC Software recommends that you use ISO 4217 standard codes. The default is USD (U.S. dollars).

DBRM= (optional; required only for OBJECT= DBRM)

NA

Name of the DBRM to Explain

You can use Db2 wildcards with this parameter. The maximum length is 8. The default is %.

DBRMLIB= (optional)

NA

Whether to Explain a DBRMLIB member

Specify one of the following values:

  • Y specifies that the Explain occurs.
  • N specifies that the Explain does not occur (default).

DDFLOC= (optional)

NA

Name of Db2 location, if you plan to access data on a different subsystem using DDF

The maximum length is 16.

DEGREE= (optional)

NA

Whether to run a query using parallel processing for better performance

For plans, this parameter applies only to the DBRMs bound directly to the plan (named in the MEMBER option on BIND PLAN), and has no effect on PKLIST names. This parameter has no effect on dynamic SQL statements, because they use the value of the special register CURRENT DEGREE.

Specify one of the following values:

  • 1 prohibits parallel processing.
  • ANY allows parallel processing (default for SQLTEXT and DBRMLIB Explains).
  • blank uses the value specified at bind time (default for all other Explain types).

--ENDOBJ (required)

NA

End-delimiter for a set of Explain parameters associated with a specific object

When Explain encounters this parameter, it processes the preceding Explain parameters (delimited by the --NEWOBJ parameter) as a completed Explain request.

--ENDSQL (optional; required only for OBJECT= SQLTEXT)

NA

End-delimiter for inline SQL associated with OBJECT=SQLTEXT

When Explain encounters this parameter, it stops processing the preceding input lines (delimited by the --BEGSQL parameter) as inline SQL.

HISTORY= (optional)

NA

Whether the history tables are to be updated with the results of an Explain

Specify one of the following values:

  • Y specifies to update the history tables with Explain results.
  • N specifies not to update the history tables with Explain results. (default)

Note

This parameter does not apply to single SQL text (OBJECT=SQLTEXT) or DBRMLIB Explains.

INSERT= (optional)

NA

Whether to Explain INSERT statements

  • Y specifies to Explain the INSERT statements (default).
  • N specifies not to Explain the INSERT statements.

MSGLEVEL= (optional)

NA

Minimum severity level of the messages produced in the report

Specify one of the following values:

  • A (ALL) specifies to display all Explain messages. (default)
  • S (SEVERE) specifies to display only messages indicating severe problems.
  • W (WARNING) specifies to display only warnings and severe messages.
  • I (INFORMATIONAL) specifies to display specifies to display informational, warning and severe messages.

NAME= (optional; required only for OBJECT=PLAN or PACKAGE, or when DBRMLIB=YES)

07

Name of the package or plan to Explain

You can use Db2 wildcards with this parameter. The default is %.

  • The maximum length for plan names is 8.
  • The maximum length for package names is 128. 1

--NEWOBJ (optional)

NA

Start-delimiter for a set of Explain parameters associated with a specific object

You must also delimit the end of the set with the --ENDOBJ parameter. The delimited set of parameters are treated as a single request.

OBJECT= (required)

04

Object type to be analyzed

Specify one of the following values: 1

  • PLAN
  • PACKAGE
  • DBRM
  • DBRMLIB
  • SQLTEXT

ONLINE= (optional)

NA

Whether the object is used in online or batch mode

Specify one of the following values:

  • Y specifies that the Explain occurs online.
  • N specifies that the Explain occurs in batch (default).

Note

Setting this value to Y triggers specific rules for table space scan, List/Sequential Prefetch, and multiple index access path (MIAP) considerations.

PLAN= (required)

02

Product plan name used for executing the product function 1

PLANTBL= (optional)

NA

Whether to process a dynamic Explain or a static Explain

Specify one of the following values:

  • Y specifies a static Explain and causes the Explain engine to search an existing PLAN_TABLE for the latest version of the requested object and use the access paths reported there.
  • N specifies a dynamic Explain. (default)

Note

The Explain function does not perform a bind or rebind with EXPLAIN(YES) on the object, because this could affect currently executing objects.

PLNAME= (optional; required only for OBJECT= DBRM)

06

Name of the plan containing DBRMs and packages to process

You can use Db2 wildcards to display a list of matching plans in batch. The maximum length is 8. The default is %. 1

PTOWNR= (optional)

NA

Qualifier for the plan table used by the dynamic Explain operation

Specify one of the following values:

  • authID specifies to use authID.Plan_Table, where authID can be any valid Db2 qualifier.
  • USERID specifies to use the job submitter’s user ID for the plan table. Use this value if you do not have the authority to perform the SET CURRENT SQLID command.
  • OWNER specifies the owner of the package or DBRM.

RATE= (optional)

NA

Rate used to translate the timeron cost into a monetary unit, in the form of monetary unit/timerons 2

RATESU= (optional)

NA

Rate used to translate the service units cost into a monetary unit, in the form of monetary unit/service units 2

READONLY= (optional)

NA

Whether the object's access intent is read only

Specify one of the following values:

  • Y triggers specific rules for plans, DBRMs, and DBRMLIBs.
  • N specifies that the object’s access intent is not read only. (default)

RECALL= (optional)

NA

Whether to recall an archived DBRMLIB data set when processing a DBRM or DBRMLIB

Specify one of the following values:

  • Y recalls the archived DBRMLIB data set (default).
  • N does not recall the archived data set.

RETAIN= (optional)

NA

Number of Explain results to retain

The purge process for history uses this value to clean up the history table during the Explain operation. If the HISTORY parameter is set to N, this value is ignored and no purge processing is performed. This parameter is a value from 0 to 99. A value of 0 deletes all existing rows for the object, or saves none. The default is 3.

RPTHDR= (optional)

NA

Whether to include report headings in the batch report

Specify one of the following values:

  • Y specifies to include the report headings (default).
  • N specifies not to include the report headings; use this option if you plan to review the report online from an output data set.

RULES= (optional)

NA

Name of a rule set from which to provide expert rules in the report

Specify one of the following values:

  • DEFAULT specifies one of the product’s predefined rule sets (default).
  • APPLDEV specifies one of the product’s predefined rule sets.
  • A specific rule set name associated with a customized rules set.

The maximum length is 8.

SQLTEXT= (optional)

NA

Used when OBJECT=SQLTEXT, where SQLTEXT is the SQL text you want to print; should be the last parameter in the JCL

Note

You can use the SQLTEXT parameter instead of the –BEGSQL and –ENDSQL parameters.

SSID= (required)

01

Db2 subsystem name on which to perform the Explain

The subsystem value cannot be changed within a single job step. The maximum length is 4. 1

STATIC= (optional)

NA

Rows in the PLAN_TABLE used for a static Explain

Specify one of the following values:

  • L uses the most recent rows in the owner.PLAN_TABLE from the last time you ran a bind with EXPLAIN=YES for the object you want to Explain (default).
  • A uses all of the rows in the owner.PLAN_TABLE for the object you want to Explain.

STMTNO= (optional)

NA

Restricts the process to a single preprocessor SQL statement number associated with the object

SUMRPT= (optional)

NA

Sort order of the batch Explain report, which contains one line for each object and statement

Specify one of the following values:

  • 1 specifies to sort the report sequenced by object, statement. (default)
  • 2 specifies to sort the report sequenced by cost, ranking all statements processed in the job step by cost in descending order.
  • 3 specifies to sort the report sequenced by object, cost.

SUPPRESSDET= (optional)

NA

Level of report detail printed during a batch Explain (used in conjunction with MSGLEVEL)

Specify one of the following values:

  • Y suppresses the detail in a batch Explain report when no rules are issued, based on the MSGLEVEL you specify.
  • N displays the detail in a batch Explain report, based on the MSGLEVEL you specify. (default)

TBQUAL= (optional)

82

Qualifier for objects and tables in the Explain

Specify one of the following values:

  • QUAL specifies that Explain uses the qualifier name provided at bind time. (default)
  • authID specifies a valid Db2 qualifier used to qualify any unqualified objects. 1

TITLE= (optional)

NA

Title to display on each page of the batch report

The maximum length is 64.

VERSION= (optional)

09

Version value associated with a package

The maximum length of this value is 122. 1

1 You can use any combination of execution parameters, as long as the total byte count of all execution parameters and their values does not exceed 100.

2  This value can be any number greater than 0. The default is 1.0.

Related topic

Overview-of-the-Explain-function


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*