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:
| 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.
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.
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.
|
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.
|
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 |
Collection
collection-name |
Specifies a valid collection name.
The parameter |
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 |
Queryno no.1 -
no.2 |
This parameter specifies a valid range of query numbers,
where the following rules apply:
|
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, 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
|
| 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. |
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.
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 the modified version of the Explain PLAN_TABLE
facility
Logon to the Natural system library SYSDB2.
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.
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).
