
The EXPLAIN Function (DB2 UDB for UNIX and Windows)
You can use this function to explain all SELECT, INSERT, UPDATE or DELETE statements.
The statements may contain optional comments such as --OPTLEVEL( <optlevel> ) --QUERY_DEGREE(< query_degree> --LOCATION( <report> , <position> ). If no comments are specified, the statements are explained using the default <optlevel> and default <query degree> for the work process.
You can call the EXPLAIN function in the following ways:
If a statement cannot be explained, the ERROR: Check SQL Statement screen appears providing a detailed error message and the possibility to modify the statement. To continue, choose
Explain Again.
Access Plan of a Statement
If a statement was explained successfully, the Display Execution Plan for SQL Statement screen appears, providing information on the SQL statement text, the OPTLEVEL and QUERY_DEGREE that was used to explain this statement, and the access plan.
The access plan generated by the DB2 optimizer is displayed as a tree structure. It consists of all database operations that will be performed when the statement is executed.
The estimated execution time is displayed in timerons (arbitrary IBM time unit). All operators are numbered, starting with zero. Operators can have the following extensions:
Extension |
Description |
[O]/[I] |
It shows whether the operator acts as an outer/inner input branch for a subsequent join operation. |
(<Partition>) |
This is only displayed if your database is DB2 UDB EEE for UNIX and Windows. It shows on which partition this operation was performed. |
Non-volatile tables and indexes of non-volatile tables are displayed in blue. Volatile tables and indexes of volatile tables are displayed in orange.
For each index used in the access plan, the number of key columns, that means index fields that were really used within the access plan, are displayed.
For further analysis of the displayed information, you can choose from various options in the application tool bar. For more information, see
EXPLAIN Options.
See also:
For additional information on the EXPLAIN function, see SAP Note 400938.
For more general information, see the IBM documentation: Administration Guide: Chapter 26, SQL Explain Facility.