Explain PLAN_TABLE

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:

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:

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:

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:

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).

A list of the line commands available can be invoked as a window by entering 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.

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 command.

   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 is printed.

   If function code O has not been selected, all information on PLAN_TABLE entries covered by the options listed in the above window is 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).