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.
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:
DB2 provides three ways to explain SQL statements:
Depending on the way the identifications of the explanations differ.
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:
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:
| 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.
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:
| 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.
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:
| 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. |
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:
| 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. |
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 LISTSQL command, the names of the Natural library and program are also displayed.
To invoke the Explain PLAN_TABLE facility
Enter function code "X" on the Natural Tools for DB2 Main Menu.
The Explain PLAN_TABLE screen is displayed:
16:45:35 ***** NATURAL TOOLS FOR DB2 ***** 2006-05-24
- 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 '>' (strings truncated at the right end) or a '<' (numbers truncated at the left end). Note, that for further commands on a line e.g. 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 ***** 2006-05-24
- 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. |
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.
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.
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 |
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 "?" 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 "I" line command. INFO displays a window where additional information can be selected on all explanations previously selected by the "S" line command.
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 ***** 2006-05-24
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 ***** 2006-05-24
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.
| Option | Description |
|---|---|
| Information on 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: |
| Catalog Statistics | This option provides statistical information from the DB2 catalog. |
| Columns of Indexes | This option provides the columns of used indexes including catalog statistics on this columns. |
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 ***** 2006-05-24
- 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.
An adapted version of the Explain PLAN_TABLE facility is also available for online mass processing and for batch mode execution.
For online mass processing, a modified version of the Explain PLAN_TABLE facility is available.
To invoke this facility, LOGON to the Natural system library SYSDB2 and enter the command EXPLAINB. The following screen is displayed:
16:45:35 ***** NATURAL TOOLS FOR DB2 ***** 2006-05-24
- 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 ***** 2006-05-24
- 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.
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 dataset referred to by DD name CMPRT01 (logical printer 1).
