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