Skip to Content

Explaining SQL statements

The Explain command explains the steps that the DB2 optimizer must take to execute the selected SQL statement.

SQL Explain displays the cost for each step of the SQL statement and the total cost of the entire statement. This data enables you to determine which part of the statement is estimated to consume the most resources.

An Explain takes one of the following forms:

  • A dynamic Explain that asks the DB2 optimizer for an explanation of the access path. This process evaluates access paths, SQL text, and key catalog statistics in real time.
  • A static Explain from a bind with EXPLAIN(YES). This process reports the access path information that was derived when you performed a bind with EXPLAIN(YES).
  • An Explain Statement Cache on a dynamic SQL statement. This process performs an EXPLAIN STMTCACHE STMTID on a dynamic SQL statement that was executed and is still in the statement cache. The access path used at the time that the statement was executed is retrieved from the statement cache and reported on.
  • An Explain Package, in which the current access path for a static SQL statement is retrieved and reported on even if a BIND with EXPLAIN(YES) was not previously performed.
Warning

Note

By default, BMC Workbench for DB2 honors the DB2 privileges to generate Explain plans. To configure BMC Workbench to use Install SYSADM for executing Explains, see information about the authexpl option in Configuring security for BMC Workbench in the Common DB2 documents space.

The resulting Explain plan is the hierarchical representation of rows extracted from the DB2 plan table.

To Explain an SQL statement

New BMC Workbench console

  1. Use any of the following methods to select a SQL statement as the baseline statement:

    The Performance perspective displays the SQL statement and its parameters. For more information, see Explain-tab.

  2. Click Generate explain plan NW_generate_explain.png.The Explain results are displayed along with the following tabs:
Classic BMC Workbench console

  1. Use any of the following methods to select a SQL statement as the baseline statement:

    The Performance perspective displays the SQL statement and its parameters. For more information, see Explain-tab.

  2. Click Generate explain plan explain-24.png.The Explain results are displayed along with the following tabs:

Related topic

Tuning-SQL

Performance-perspective


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC Workbench for DB2 12.1