Explain output (Db2 Explain Output panel)

Use the Db2® Explain Output panel and Catalog Table windows to investigate the access path methods of an explained SQL statement. Information regarding packages, DBRMs, table spaces, tables, indexes, and column attributes can also be viewed.

Use the Db2 Explain Output panel to examine the results of an explain request.

When an SQL statement has been chosen for explanation, the following processing depends on the origin of the statement:
Explain existing entry in the plan table
The specified PLAN_TABLE entry is shown as a result of the explain request.
Explain a package's SQL statement
The plan table of the package owner is searched to check whether the SQL statement has been explained during BIND. If the search is successful, this PLAN_TABLE entry is taken. If the search is not successful, a dynamic explain is performed, that is, SQL EXPLAIN is invoked for the statement. Then the result of this explain is displayed.

The plan table is searched by using the bind time of the package. There might be multiple occurrences of the package with different bind times. For example, the Db2 system catalog might contain the latest package that is created by the Db2 command BIND or REBIND. It might also contain a former package version that is activated by the Db2 command REBIND SWITCH.

Explain a DBRM's SQL statement
The plan table of the DBRM owner is searched to check whether the SQL statement has been explained during BIND. If the search is successful, this PLAN_TABLE entry is taken. If the search is not successful, a dynamic explain is performed, that is, SQL EXPLAIN is invoked for the statement. Then the result of this explain is displayed.
SQL statement to be explained has been entered
A dynamic explain is performed, that is, SQL EXPLAIN is invoked for the statement, and the result is displayed.
SQL statement is modified in Db2 Explain Output panel
A dynamic explain is performed, that is, SQL EXPLAIN is invoked for the modified statement and the new result is displayed.
Only the Db2 Explain Output panel for packages (DGOMYPKM) is shown in this section.
Figure 1. Db2 Explain Output panel (packages)
DGOMYPKM                      DB2 Explain Output

Local location . . . . . . . : PMODB2A           SDA2  V10
Current server . . . . . . . : PMODB2A           SDA2  V10

_  Package . . . . . . . . . : KO2EX510.DGO@TPG1                +
   Version . . . . . . . . . : O510_PM32647C                    +
   Explain executed at . . . : 12/01/25 18:33:56.63

   -------------------------------- SQL Text --------------------------------
_  SELECT LOCATION , COLLID , NAME , CONTOKEN , OWNER , CREATOR , TIMESTAMP
   , BINDTIME , QUALIFIER , PKSIZE , AVGSIZE , SYSENTRIES , VALID ,
   Status . : Compiled-bound using defaults for input variables
   Isolation: Uncommitted Read

_  Host variable definitions

   ----- Access path summary for query block 1 step 1 -----
   Matching index scan with scan of referenced data pages
   Number of matching columns: 4. The index has 4 columns
   Non clustered index scan will be used
   Page range scan will not be used

_  Table   SYSIBM   SYSPACKAGE
_  Index   SYSIBM   DSNKKX01

_  PLAN_TABLE details for step

FPEM762 This statement was explained at bind time
Command ===> ________________________________________________________________
 F1=Help      F2=Split     F3=Exit      F7=Up        F8=Down      F9=Swap
F12=Cancel   F16=Look     F17=Collect  F21=Expand   F22=Left     F23=Right
The Db2 Explain Output panel is divided into three sections:
  • The first section of the panel shows the plan name, and information about the package or DBRM that contains the SQL statement. You can select this section for further investigation of the package, DBRM, or plan name associated with the SQL statement.
  • The second section of the panel, titled SQL Text, shows the first 150 characters of the SQL statement text. You can view or modify the full SQL statement text by selecting the input field shown beside the SQL text. The SQL Text Edit panel is then displayed. For more information about the SQL Text Edit panel, see SQL Text Edit panel. After the SQL text is modified, the SQL statement is reexplained, and the new result is shown in the Db2 Explain Output panel. From this section you can also reach windows that provide information about the host variable definitions.
  • The third section of the panel, titled Access path summary, shows information about the access path methods chosen by Db2 for each of the individual query blocks and steps required to execute the SQL statement. Each step also shows a list of the tables and indexes accessed for that step. You can select any table or index listed in a step for further investigation. You can also display the raw PLAN_TABLE data for a step. This also provides online access to help information for PLAN_TABLE columns, similar to the information in Db2 SQL Reference for the EXPLAIN statement.

If the execution of the SQL statement contains many steps, you can use the scrolling keys F7 (Up) and F8 (Down) to browse through the steps.