Start of change

Explaining a package

You can run EXPLAIN on a package to capture access path information for all SQL statements in that package. For example, you can run EXPLAIN on an older copy of a package to compare old access paths with the current ones.

About this task

Explaining a package in Db2 Admin Tool is equivalent to running the Db2 EXPLAIN PACKAGE statement.

This procedure guides you through using the Explain option (option E on the main menu) to run EXPLAIN on a package. Alternatively, you can navigate to the package on the Packages (ADB21K) panel and issue the EXP line command to display the Explain Package (ADB21KEX) panel; see step 3.

Procedure

To explain a package:

  1. On the DB2 Administration Menu (ADB2) panel, specify option E, and press Enter.
  2. On the EXPLAIN (ADB2E) panel, specify option EK, and press Enter:
    Figure 1. EXPLAIN (ADB2E) panel
    ADB2E min                             Explain                             23:33
Option ===>                                                                    
                                                                               
                                                                               
   E  - Explain an SQL statement                         DB2 System: DD1A 
   EK - Explain package                                  DB2 SQL ID: ADM001     
   L  - List PLAN_TABLE   Q - List SYSQUERY explain info       
         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 Package (ADB21KEX) panel, specify the requested information, and press Enter to run the EXPLAIN statement:
    For more information about any of the fields on this panel, see the online help (PF1).
    Figure 2. Explain Package (ADB21KEX) panel
    ADB21KEX                          Explain Package                         17:28
Option ===>                                                                    
                                                                               
                                                                               
EXPLAIN PACKAGE COLLECTION                                                     
Collection name . . . . KAWCOL2                     > (? to lookup)            
                                                                               
PACKAGE                                                                        
Package name  . . . . . TEST0216                    > (? to lookup)            
                                                                               
VERSION                                                                        
Version name  . . . . . V1                          >                          
                                                                               
COPY                                                                           
Copy ID . . . . . . . .          (blank, CURRENT, PREVIOUS or ORIGINAL)        
                                                                               
Pre-clean PLAN_TABLE  . NO  (Yes/No)
    After you press Enter, relevant plan table rows for the selected package are displayed:
    Figure 3. Rows from PLAN_TABLE (ADB2EL) panel
     ADB2EL in ----------- Rows from TS6462.PLAN_TABLE   ---------- Row 1 to 3 of 3 
 Command ===>                                                  Scroll ===> DATA 
 EXPLAIN stmt executed                                                          
 Commands: HINT  INDEX                COPY                                      
 Line commands:                                                                 
  I - Interpretation  T - Table  X - Index  P - Plan  K - Package               
  DP - Delete rows for plan  DK - Delete for package  DQ - Delete for query no  
  ? - Show all line commands                                                    
                                                                                
         Query Q  Collect. Progname Pl M Ac M  I  T  Table                      
 S      Number Bl (COLLID) (Packg)  No T Ty Co O  No Schema   Table Name        
             * *  *        *        *  * *  *  * *   *        *                 
 -- ---------- -- -------- -------- -- - -- -- - --- -------- ------------------
             0 1  KAWCOL2  TEST0216 1  0 R  0  N   1 SYSIBM   SYSUTILITIES      
             0 1  KAWCOL2  TEST0216 1  0 R  0  N   1 SYSIBM   SYSUTILITIES      
             0 1  KAWCOL2  TEST0216 1  0 R  0  N   1 SYSIBM   SYSUTILITIES      
 ******************************* END OF DB2 DATA *******************************
End of change