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:

·        Call the DBA Cockpit and choose Diagnostics  ® EXPLAIN in the navigation frame of the DBA Cockpit. On the Diagnostics: EXPLAIN screen, enter an SQL statement manually and choose  Explain.

·        Call the DBA Cockpit and choose Performance  ® Applications in the navigation frame of the DBA Cockpit. For more information, see Applications: Statements.

·        Call the DBA Cockpit and choosePerformance  ® SQL Cache in the navigation frame of the DBA Cockpit. For more information, see Performance: SQL Cache Snapshot.

·        Call the DBA Cockpit and choose Diagnostics  ® Cumulative SQL Trace in the navigation frame of the DBA Cockpit. For more information, see Diagnostics: Cumulative SQL Trace.

·        Call transaction ST05 and choose Enter SQL statement. Enter an SQL statement manually and choose  Explain.

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.

·        Call transaction ST05 and choose Trace list. Select one statement and choose  Explain.

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]	Shows whether the operator acts as an outer/inner input branch for a subsequent join operation.
(<Partition>)	Shows on which partition this operation was performed. This is only displayed if you are using a multi-partition database.

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.