Explain PLAN_TABLE

#### Search Results

Search

# Explain PLAN_TABLE

Important
Before you use the Explain PLAN_TABLE function, refer to LISTSQL and Explain Functions in the section Special Requirements for Natural Tools for Db2 in Installing Natural for Db2 on z/OS.

The Explain PLAN_TABLE facility of the Natural Tools for Db2 interprets the results of SQL `EXPLAIN` commands from your `PLAN_TABLE`. The information contained in your `PLAN_TABLE` is represented in so-called explanations.

Explanations of a `PLAN_TABLE` describe the access paths chosen by Db2 to execute SQL statements.

An SQL statement is executed by Db2 in one or more steps. For each execution step, one row is inserted into the `PLAN_TABLE`. All rows together describing the access path for one SQL statement are called an explanation.

The explanations are identified in the `PLAN_TABLE` by a combination of either plan name, DBRM (database request module) name, and query number or collection name, package name, and query number.

This section covers the following topics:

- EXPLAIN Modes

- Invoking the EXPLAIN_TABLE Function

- List PLAN_TABLE - Latest Explanations

- List PLAN_TABLE - All Explanations

- Delete from PLAN_TABLE

- Explain PLAN_TABLE Facility for Mass and Batch Processing

## EXPLAIN Modes

Db2 provides three ways to explain SQL statements:

- Dynamic EXPLAIN

- Bind Plan EXPLAIN

- Bind Package EXPLAIN

Depending on the way the identifications of the explanations differ.

### Dynamic EXPLAIN

Executes an SQL `EXPLAIN` command dynamically, where the explanation is inserted into the `PLAN_TABLE` of your current SQLID.

The `EXPLAIN` command can be issued within the Catalog Maintenance and Interactive SQL facilities of the Natural Tools for Db2. In addition, the Natural `LISTSQL` command can be used to extract SQL statements from cataloged Natural programs, and to issue the SQL `EXPLAIN` command for the extracted SQL statements.

If you issue the SQL `EXPLAIN` command dynamically, you should specify a query number to help identify the explanation in the `PLAN_TABLE`. The same query number should be used for related statements.

Depending on the method with which the DBRM used by the dynamic SQL processor is bound into the plan, Db2 uses two different ways to identify rows in the `PLAN_TABLE`:

- Dynamic Mode
- Package Mode

#### Dynamic Mode

The DBRM is bound directly into the plan.

When an explanation is inserted, the plan name, the DBRM name, and the query number are determined by Db2 as follows:

Parameter | Description
--- | ---
`plan name` | is left blank;
`DBRM name` | is the name of the DBRM used by the dynamic SQL processor;
`query number` | is equal to the query number you specified with the `EXPLAIN` command (the default query number is `1`).

This explanation mode is called dynamic mode.

#### Package Mode

The DBRM is bound as package into the plan.

When an explanation is inserted, the collection name, the package name, and the query number are determined by Db2 as follows:

Parameter | Description
--- | ---
`collection name` | is the name of the collection that contains the package;
`package name` | is the name of the package used by the dynamic SQL processor;
`query number` | is equal to the query number you specified with the `EXPLAIN` command (the default query number is `1`).

This explanation mode is called package mode.

### Bind Plan EXPLAIN

Binds an application plan with the option EXPLAIN YES, where the explanation is inserted into the `PLAN_TABLE` of the owner of the plan. When an explanation is inserted, the plan name, the DBRM name, and the query number are determined by Db2 as follows:

Parameter | Description
--- | ---
`plan name` | is the name of the plan;
`DBRM name` | is the name of the DBRM that contains the SQL statement;
`query number` | is equal to the statement number (`stmtno`), which is generated by the Db2 precompiler.

### Bind Package EXPLAIN

Binds a package with the option EXPLAIN YES, where the explanation is inserted into the `PLAN_TABLE` of the owner of the package. When an explanation is inserted, the collection name, the package name, and the query number are determined by Db2 as follows:

Parameter | Description
--- | ---
`collection name` | is the name of the collection that contains the package;
`package name` | is the name of the package that contains the SQL statement;
`query number` | is equal to the statement number (`stmtno`), which is generated by the Db2 precompiler.

## Invoking the EXPLAIN_TABLE Function

Explanations can be selected by either plan name, DBRM name, and query number or collection name, package name, and query number. If you issue an `EXPLAIN` command various times, it is possible that multiple explanations are identified by a given combination of these selection fields. Thus, you can select either all explanations or only the most recent one. A list with all selected explanations is displayed, from which you can select individual rows for a more detailed description.

The individual rows of a `PLAN_TABLE` are displayed one row per line. Rows that describe the same SQL statement are shown together as one explanation. Different explanations, are separated by empty lines. You can browse through the list and select a detailed report for individual explanations. If rows have been inserted into your `PLAN_TABLE` as a result of a Natural system command `LISTSQL`, the names of the Natural library and program are also displayed.

To invoke the Explain PLAN_TABLE facility

- On the Natural Tools for Db2 Main Menu, enter function code `X`.

  The Explain PLAN_TABLE screen is displayed:

      16:45:35              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                    - Explain PLAN_TABLE -

                            Code Function

                             L   List PLAN_TABLE - Latest Explanations
                             A   List PLAN_TABLE - All    Explanations
                             D   Delete from PLAN_TABLE
                             ?   Help
                             .   Exit

                     Code .. _   Mode ........ DYNAMIC_ ( Dynamic, Plan, Package )
                                 Plan ........ ________
                                 Collection .. ________
                                 DBRM/Package  ________
                                 Queryno ..... _____ - _____

        Command ===>
        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
              Help  Setup Exit                                                  Canc

  With PF2 (Setup) the maximum length of one column and the number of fixed characters when scrolling left may be specified. The default values for both parameters may be changed in the `CONFIG` subprogram in library `SYSDB2`.

  When a column value is longer than the maximum length, it will be truncated and marked with a greater than symbol (>), which means that strings are truncated at the right end, or a or a less than symbol (<), which means that numbers are truncated at the left end. Note, that for further commands on a line, for example, the line command `I`, only the visible value can be taken as input. This means that commands on lines will fail, when values for further processing are truncated.

      16:45:35              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                    - Explain PLAN_TABLE -

                            Code Function    +----------Explain PLAN_TABLE----------+
                                             !                                      !
                             L   List PLAN_T ! Maximum length of columns ... ___12  !
                             A   List PLAN_T ! Number of fixed characters .. ____0  !
                             D   Delete from !                                      !
                             ?   Help        !                                      !
                             .   Exit        +--------------------------------------+

                     Code .. _   Mode ........ DYNAMIC_ ( Dynamic, Plan, Package )
                                 Plan ........ ________
                                 Collection .. ________
                                 DBRM/Package  ________
                                 Queryno ..... _____ - _____

        Command ===>
        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
              Help  Setup Exit                                                  Canc

  The following functions are available:

  Code | Description
  --- | ---
  `L` | The List PLAN_TABLE - Latest Explanations function lists the last explanation for any combination of the parameters described below.
  `A` | The List PLAN_TABLE - All Explanations function lists all explanations for any combination of the parameters described below.
  `D` | The Delete from PLAN_TABLE function deletes the specified explanations from your `PLAN_TABLE`.

  The following parameters can be specified:

  Parameter | Description
  --- | ---
  `Mode` | Specifies the explanation mode (Dynamic, Plan, or Package).
  `Plan plan-name` | Specifies a valid plan name. The parameter `Plan` is only required in Plan mode.
  `Collection collection-name` | Specifies a valid collection name. The parameter `Collection` is only required in Package mode.
  `DBRM/Package dbrm/package-name` | In Plan mode, specifies a valid DBRM name. In Package mode,specifies a valid package name. In dynamic mode, specifies the DBRM used by the dynamic SQL processor. If a value followed by an asterisk (*) is specified, all DBRMs/packages of the specified plan/collection whose names start with the specified value are considered. If asterisk notation is specified only, all DBRMs/packages of the specified plan/collection are considered. The `DBRM/Package` parameter is used to limit the display to individual DBRMs/packages.
  `Queryno no.1 - no.2` | This parameter specifies a valid range of query numbers, where the following rules apply: If no query number is specified, all query numbers are displayed; If only the first query number is specified, only this query number is displayed; If only the second query number is specified, all query numbers up to and including the second query number are displayed; If both query numbers are specified, all query numbers between and including the first and the second query number are displayed.

## List PLAN_TABLE - Latest Explanations

This function only lists the most recent explanation for any specified combination of either plan name, DBRM name, and query number or package name, collection name and query number.

## List PLAN_TABLE - All Explanations

This function lists all explanations for any combination of either plan name, DBRM name, and query number or package name, collection name and query number. The query number parameters are interpreted as above.

#### Sample Listing of Explanations

    11:04:04              ***** NATURAL TOOLS FOR DB2 *****             2007-09-05
     Plan TESTPLAN                             S 01    Row 0 of 152 Columns 032 075
     ====>                                                        Scroll ===>  PAGE
        DBRM          QNO    ME ACC    MA IO    PRE SORTN SORTC TCREATOR TABLENAME
     ** ******************************* top of data *******************************
     __ TEST          722       I       1 -          ----  ---- SAGCRE   DEPT
     __ TEST          722     1 I       1 -          ----  ---- SAGCRE   EMP
     __ TEST          722     3           -          ----  --O-
     __ TEST          722       I       1 -          ----  ---- SAGCRE   DEPT
     __ TEST          722       I       1 Y          ----  ---- SAGCRE   EMP
     __ TEST          722       I       1 -          ----  ---- SAGCRE   DEPT
     __
     __ TEST          761       I       1 -          ----  ---- SAGCRE   EMP
     __ TEST          761     1 I       1 -          ----  ---- SAGCRE   DEPT
     __ TEST          761     3           -          ----  --O-
     __ TEST          761       I       1 -          ----  ---- SAGCRE   EMP
     __ TEST          761       I       1 Y          ----  ---- SAGCRE   DEPT
     __
     __ TEST          793       I       1 -          ----  ---- SAGCRE   DEPT
     __ TEST          793     1 I       1 -          ----  ---- SAGCRE   EMP
     __ TEST          793     1 I       1 -          ----  ---- SAGCRE   EMP

    Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
          Help        Exit        Rfind        -     +           <     >    Canc

#### Commands Available

The following line commands are available within listings of the Explain PLAN_TABLE facility. Line commands are entered in front of any of the rows of the desired explanation(s).

Command | Description
--- | ---
`I` | Displays a window where additional information about an explanation can be selected
`S` | Selects an explanation to be used with the `INFO` command described below.
`U` | Unselects an explanation for use with the `INFO` command.

A list of the line commands available can be invoked as a window by entering the help character, that is a question mark (?), in front of any of the listed rows.

Apart from the line commands, the `INFO` command can be specified, too. The `INFO` command must be entered in the command line of the listing screen and is the equivalent of the line command `I`. `INFO` displays a window where additional information can be selected on all explanations previously selected by the line command `S`.

In Plan mode, the following window is displayed, where you can select which additional information you want to be displayed or printed.

      16:48:24              ***** NATURAL TOOLS FOR DB2 *****              2009-10-30
       Plan TESTPLAN                             S 01     Row 0 of 82 Columns 048 100
       ====>                                                        Scroll ===>  PAGE
          DBRM     QNO   ME ACC MA IO     PRE SORTN SORTC TCREATOR TABLENAME
       ** **** +---------------------------------------------------------+***********
       __ TEST !                                                         !
       __ TEST !                 Select what to display                  !
       __ TEST !                                                         !
       __ TEST !                 _ information about plan                !
       __ TEST !                 _ statements of plan                    !
       __ TEST !                 _ data from PLAN_TABLE                  !
       __      !                 _ evaluation of PLAN_TABLE              !
       __ TEST !                 _ catalog statistics                    !
       __ TEST !                 _ columns of used indexes               !
       __ TEST !                                                         !
       __ TEST !                 Mark  _ to print output                 !
       __ TEST !                                                         !
       __      +---------------------------------------------------------+
       __ TEST     793      I   1  -           ----  ---- SAGCRE   DEPT
       __ TEST     793   1  I   1  -           ----  ---- SAGCRE   EMP
       __ TEST     793   1  I   1  -           ----  ---- SAGCRE   EMP

      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help        Exit        Rfind        -     +           <     >    Canc

Accordingly, the following window is displayed in Package mode:

      16:48:24              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
       Package TESTPACK                          S 01     Row 0 of 82 Columns 048 100
       ====>                                                        Scroll ===>  PAGE
          DBRM +---------------------------------------------------------+
       ** **** !                                                         ! **********
       __ TEST !                                                         ! ES
       __ TEST !                 Select what to display                  ! ES
       __ TEST !                                                         ! ES
       __ TEST !                 _ information about package             ! ES
       __ TEST !                 _ statements of package                 ! ES
       __ TEST !                 _ data from PLAN_TABLE                  ! ES
       __      !                 _ evaluation of PLAN_TABLE              ! ES
       __ TEST !                 _ catalog statistics                    ! ES
       __ TEST !                 _ columns of used indexes               ! ES
       __ TEST !                                                         ! ES
       __ TEST !                 Mark  _ to print output                 ! ES
       ** **** +---------------------------------------------------------+ **********

      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help        Exit        Rfind        -     +           <     >    Canc

Browsing of data displayed is performed with browse commands, of which the most important can also be issued via PF keys; see Editing within the Natural Tools for Db2.

Option | Description
--- | ---
Information about plan/package | If a plan/package name has been specified, this option includes information from the Db2 catalog, such as date and time of the bind, as well as several bind options. In Dynamic mode, this option is not available.
Statements of plan/package | If a plan/package name has been specified, this option provides information on the explained SQL statements contained in this package. This information is taken from the Db2 catalog. In Dynamic mode, this option is not available.
Data from PLAN_TABLE | This option provides information from the `PLAN_TABLE` about the selected rows.
Evaluation of PLAN_TABLE | This option provides a description of the `PLAN_TABLE`. For each execution step, it describes: the locks chosen by Db2, whether a join operation is performed, whether the data is sorted and why the sort is performed, the access path in detail.
Catalog statistics | This option provides statistical information from the Db2 catalog.
Columns of used indexes | This option provides the columns of used indexes including catalog statistics on this columns.

## Delete from PLAN_TABLE

The Delete from PLAN_TABLE function is also used to select `PLAN_TABLE` explanations depending on the specified combination of either plan name, DBRM name, and query number or collection name, package name, and query number. This time, however, the selected `PLAN_TABLE` explanations are not displayed but deleted.

The Delete from PLAN_TABLE function is useful to delete old data before either binding or rebinding a plan, or before executing an SQL `EXPLAIN` command.

To prevent `PLAN_TABLE` explanations from being deleted unintentionally, you are prompted for confirmation:

      16:50:23              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                - Delete from PLAN_TABLE -

              The SQL Command

                   DELETE FROM PLAN_TABLE
                      WHERE APPLNAME = ' '
                        AND COLLID = 'OLD'
                        AND PROGNAME LIKE 'ANY%'
                        AND QUERYNO BETWEEN 1 AND 2

              will be executed.

              Press PF5 to delete the data from the PLAN_TABLE or
                    PF3 to return to the menu without deleting data

      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help        Exit        Del                                       Canc

Apart from the global PF-key settings, with the Delete from PLAN_TABLE function of the Explain PLAN_TABLE facility, PF5 (Del) is used to confirm the deletion of previously selected explanations.

## Explain PLAN_TABLE Facility for Mass and Batch Processing

An adapted version of the Explain PLAN_TABLE facility is also available for online mass processing and for batch mode execution.

### EXPLAINB for Mass Processing

For online mass processing, a modified version of the Explain PLAN_TABLE facility is available.

To invoke the modified version of the Explain PLAN_TABLE facility

1. Logon to the Natural system library `SYSDB2`.

2. In the command line, enter the command `EXPLAINB` and press ENTER.

   The following screen is displayed:

         16:45:35              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                     - Explain PLAN_TABLE -

                             Code Function

                              L   List PLAN_TABLE - Latest Explanations
                              A   List PLAN_TABLE - All    Explanations
                              O   Output Options
                              .   Exit

                      Code .. _   Mode .......... DYNAMIC_ ( Dynamic, Plan, Package )
                                  Plan .......... ________
                                  Collection .... __________________
                                  DBRM/Package .. ________
                                  Queryno ....... _____ - _____

         Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
               Help        Exit                                                  Canc

   In addition to function codes `L` (List PLAN_TABLE - Latest Entries function) and `A` (List PLAN_TABLE - All Entries function), function code `O` (Output Options) is available.

   The Output Options function enables you to restrict the output of information on `PLAN_TABLE` entries. The various options are listed in a window invoked by entering function code `O` on the above Explain PLAN_TABLE menu. The window is similar to the one invoked by the online `I` or `INFO` commands.

         16:53:20              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                     - Explain PLAN_TABLE -

                   +---------------------------------------------------------+
                   !                                                         !
                   !                                                         !
                   !                 Select what to display                  !
                   !                                                         !
                   !                 _ information about plan/package        !
                   !                 _ statements of plan/package            !
                   !                 _ data from PLAN_TABLE                  !
                   !                 _ evaluation of PLAN_TABLE              !
                   !                 _ catalog statistics                    !
                   !                 _ columns of used indexes               ! kage )
                   !                                                         !
                   !                                                         !
                   +---------------------------------------------------------+
                                  Queryno ....... _____ - _____

         Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                           Exit                                                  Canc

   If the Output Options function has been selected, only information covered by the options marked for output are printed.

   If function code `O` has not been selected, all information on `PLAN_TABLE` entries covered by the options listed in the above window are printed.

   In both cases, you are prompted for a printer.

### EXPLAINB in Batch Mode

Apart from being used for online mass processing, the functionality of `EXPLAINB` is especially intended for batch processing. If `EXPLAINB` is used in batch mode, output is sent to a data set referred to by DD name `CMPRT01` (logical printer 1).

### On this page

- EXPLAIN Modes
- Invoking the EXPLAIN_TABLE Function
- List PLAN_TABLE - Latest Explanations
- List PLAN_TABLE - All Explanations
- Delete from PLAN_TABLE
- Explain PLAN_TABLE Facility for Mass and Batch Processing