Explaining SQL Statements
You can use Db2 EXPLAIN to capture access path information for your queries.
Procedure
To explain an SQL statement:
- On the DB2 Administration Menu (ADB2) panel, specify option E, and press Enter.
-
On the EXPLAIN (ADB2E)
panel, specify option
E, and press Enter:
Figure 1. EXPLAIN (ADB2E) panel ADB2E min Explain 23:33 Option ===> E - Explain an SQL statement DB2 System: DD1A L - List PLAN_TABLE Q - List SYSQUERY explain info DB2 SQL ID: ADM001 Schema . . . . . . . . . > (default is ADM001) Plan name . . . . . . . > (optional) DBRM/package name . . . > (optional) Collection ID . . . . . > (optional) DPS - Dynamic Plan Stability SCT - Statement Cache Table MNT - Create, upgrade, or drop EXPLAIN tables via ADMIN_EXPLAIN_MAINT stored procedure
Note: The DPS and SCT options are displayed only if the CACHEDYN subsystem parameter is set to YES and the CACHEDYN_STABILIZATION subsystem parameter is set to something other than NONE. -
On the Explain an SQL Statement
(ADB2EE) panel, specify the following
information, and press Enter to run the EXPLAIN statement:
- Specify a query number and an SQL statement. If you leave the query number blank, Db2 Admin Tool generates a query number for you in the form YYMMDDSSS, where YYMMDD is the year/month/day and SSS is a sequence number.
- Optional: Use the SET CURRENT DEGREE field to set the current degree of parallelism before running the EXPLAIN plan statement. Valid values are 1 and ANY. If the field is blank, the current degree is not changed.
ADB2EE in ---------------- DD1A Explain an SQL Statement ---------------- 15:50 Command ===> SET CURRENT DEGREE = ; (Optional) DB2 System: DD1A EXPLAIN ALL DB2 SQL ID: ADM001 SET QUERYNO = Query number==> FOR SQL stmt ==> SELECT * FROM SYSIBM.SYSTABLES WHERE NAME LIKE 'SYS'; Press ENTER to execute explain, or enter EDIT on the command line to edit the SQL statement.
- On the Rows from PLAN_TABLE (ADB2EL) panel, use the I line command to display the EXPLAIN results.