Listing plan table rows

A plan table, PLAN_TABLE, is a Db2® EXPLAIN table that contains access path information for SQL statements.

Procedure

To view a plan table:

On the DB2 Administration Menu (ADB2) panel, specify option E, and press Enter.

On the EXPLAIN (ADB2E) panel, specify option L and optionally any qualifiers in the following fields to identify which plan table you want to view:

Schema
Plan name
DBRM/package name
Collection ID

If you do not specify any qualifiers, the default schema name listed on the panel is used.

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.

Press Enter.

The Rows from PLAN_TABLE (ADB2EL) panel is displayed:

Figure 2. Rows from PLAN_TABLE (ADB2EL) panel

ADB2EL in ----------- Rows from ADM001.PLAN_TABLE ------------------ Row 1 of 8
 Command ===>                                                   Scroll ===> PAGE
 
 Commands: HINT  INDEX  COPY  ACCEL              
 Line commands:                                                                
  I - Interpretation  T - Table  X - Index  P - Plan  M - DBRM  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      
             * *  *        *        *  * *  *  * *  *        *                
 -- ---------- -- -------- -------> -- - -- -- - --- -------- ------------------
      960125003 1  ADBLCOLI ADBMAIN  1  0 I  0  N 1  SYSIBM   SYSTABLES        
      999999999 1  EEEPACK  E41MAIN  1  0 I  1  N 1  SYSIBM   SYSTABLES        
      970923001 1  ADBLCOLI ADBMAIN  1  0 I  1  N 1  SYSIBM   SYSTABLES        
      981118002 1  ADBL     ADBMAIN  1  0 I  0  N 1  SYSIBM   SYSTABLES        
      981118003 1  ADBL     ADBMAIN  1  0 I  0  N 1  SYSIBM   SYSTABLES        
      990421001 1  ADBL     ADBMAIN  1  0 I  0  N 1  SYSIBM   SYSTABLES        
      990421002 1  ADBL     ADBMAIN  1  0 I  2  N 1  SYSIBM   SYSTABLES        
      990421003 1  ADBL     ADBMAIN  1  0 I  2  N 1  SYSIBM   SYSTABLES        
 ******************************* END OF DB2 DATA *******************************

If this panel is not displayed, the plan table is empty.

The release level and mode of your Db2 subsystem affect the options that are available.

For more information about any of the columns on this panel, see the online help and PLAN_TABLE (Db2 13 for z/OS documentation) .

What to do next

Use this panel to see how Db2 will execute SQL statements. This information is gathered from previously executed EXPLAIN statements and from Db2 BIND commands that specify EXPLAIN(YES).

You can view the information on this panel in different formats. To switch between formats, use the following primary commands:

COL: Package mode, which shows Collection (COLLID) and Progname (PACKG)
HINT: Hint mode, which shows Hint ID and Hint Used
INDEX: Index information
TABLE: Table information
COPY: Copy displayed rows to another PLAN_TABLE
ACCEL: Accelerator server information. This format is available only if you are using an accelerator.

To display the interpretation information for any queries, issue line command I.

For accelerated queries, I displays an interpretation panel similar to the panel in the following figure. Accelerated queries have an access type of A (accesstype = 'A').

Figure 3. Interpretation of Row from DSN_QUERYINFO_TABLE (ADBPELI) panel

ADBPELI n ------- Interpretation of Row from DSN_QUERYINFO_TABLE --------- 10:35
 Command ===>                                                                   
                                                                                
                                                                    More:     + 
                                                         DB2 System: DD1A       
 Data as produced by EXPLAIN:                                                   
                                                                                
 _ DECLARE C1 CURSOR FOR SELECT  * FROM SYSADM.TBOC5I03                        
 -------------------------------------------------------------------------------
 | Query is marked to be offloaded to an accelerator.                          |
 | Query qualifies for routing to an accelerator.                              |
 |                                                                             |
 |                                                                             |
 -------------------------------------------------------------------------------
 Table schema . . : SYSADM                 Table name . . : TBOC5I03            
 Accelerator name : ZGRYPHON               Location name  : DB2EC1              
 Query number . . : 2                      Query blk no . : 1                   
 Application name : DSNTEP3                Program name . : DSNTEP3             
 Access type  . . : A                      Version  . . . : 2                   
 Collid . . . . . :                        Group member . :                     
 Sectnoi  . . . . :                        Seqno  . . . . :                     
 Explain date . . : 2013-01-31             Explain time . : 04.24.32.67         
 Reason code      : 0                      Service info . :

In addition to the EXPLAIN information, this panel states whether the query is marked to be offloaded to an accelerator and whether it is qualified to be routed to an accelerator. If the query is not qualified to be offloaded to an accelerator, the reason is stated on the panel.