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:
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 data set referred to
by DD name CMPRT01
(logical printer 1).