Using the DBA Command Manager for DB2 Module › Explain an SQL Statement

Explain an SQL Statement

Use the DBA Command Manager for DB2 module or Explain action in the Investigator to explain an SQL statement. A DB2 EXPLAIN is executed on the SQL statement and access path information and CA-supplied rules and recommendations for that statement are returned.

Note: EXPLAIN processing does not currently support comments. Comments that are added while developing complicated statements must be removed before doing an EXPLAIN.

You can explain DML statements such as SELECT, INSERT, and UPDATE. However, do not explain DDL statements. These statements typically begin with CREATE or ALTER.

Follow these steps:

1. Add the DBA Command Manager for DB2 module to a dashboard.
2. Enter an explainable SQL statement in the text box and click the Explain button.

   Note: Enter the statement syntax using capital letters, and enter only one SQL statement at a time. EXPLAIN does not support embedded comments.

   The module explains the statement. The module also displays the access path information. This information appears under the Access Path tab and CA-supplied rules and recommendations for the SQL statement under the Rules and Recommendations tab.

   The first 2000 rows are returned. Remaining rows are truncated.

   Important! Each user must complete the CA Plan Analyzer @DEFAULT ruleset to receive a complete output of the recommended rules and recommendations.

   If the @DEFAULT rule set is not defined in CA Plan Analyzer, the EXPLAIN request output may be incomplete.

   Note: Syntax checking is performed before performing an EXPLAIN. Nonexplainable SQL statements, such as DDL, can produce a SQL syntax error instead of a nonexplainable statement error.