Explaining SQL statements

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

Related topics

Tuning SQL

Performance perspective

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

Important

By default, BMC AMI Command Center for Db2 honors the Db2 privileges to generate Explain plans. To configure BMC AMI Command Center to use Install SYSADM for executing Explains, see information about the authexpl option in Configuring security for BMC AMI Command Center for Db2 documentation.

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

To Explain an SQL statement

  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:
  3. (Optional) To export the explain plan to a PDF file, perform the following steps:
    1. Click Generate Report NW_file_text_o.png.
      Based on your browser settings, after Command Center generates the report, either the Save As dialog box is displayed or the browser saves the file automatically.
    2. Enter a name for the file.
    3. Click Save.

 

 

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