Explaining SQL Statements

You can use Db2 EXPLAIN to capture access path information for your queries.

Procedure

To explain an SQL statement:

  1. On the DB2 Administration Menu (ADB2) panel, specify option E, and press Enter.
  2. 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.
  3. 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.
  4. On the Rows from PLAN_TABLE (ADB2EL) panel, use the I line command to display the EXPLAIN results.