Running queries on indexes not used by any plan or package

Use the performance queries function to view information about and work with indexes that are not used by any plan or package.

Procedure

  1. On the DB2 Administration Menu (ADB2) panel, specify option 3, and press Enter.
    The DB2 Performance Queries panel is displayed.
  2. Specify option 8, and press Enter.

    The Indexes Not Used By Any Plan or Package (ADB239) panel panel is displayed, as shown in the following figure:

    Figure 1. Indexes Not Used By Any Plan or Package (ADB239) panel
     DB2 Admin -------- DB2X Indexes Not Used by Any Plan or Package - Row 1 of 138 
 Command ===>                                                  Scroll ===> PAGE 
                                                                                
 The following indexes are not used by any plan or package with static SQL.     
 Consider dropping the index if it is not used in QMF or any other dynamic SQL  
 statement.                                                                     
                                                                                
 Commands:       UT - Utilities                                                 
 Line commands:   S - Select      DROP - Drop Index                             
                                                                                
                         Index                       Table                      
 Sel  Index Name         Schema   Table Name         Schema                     
      *                  *        *                  *                          
 ---- ------------------ -------- ------------------ --------                   
      IBMSNAP_CRITSECX   ASN      IBMSNAP_CRITSEC    ASN                        
      IBMSNAP_PRUNCNTLX  ASN      IBMSNAP_PRUNCNTL   ASN                        
      IBMSNAP_REGISTERX  ASN      IBMSNAP_REGISTER   ASN                        
      IBMSNAP_SUBS_COLSX ASN      IBMSNAP_SUBS_COLS  ASN                        
      IBMSNAP_SUBS_EVENX ASN      IBMSNAP_SUBS_EVENT ASN                        
      IBMSNAP_SUBS_MEMBX ASN      IBMSNAP_SUBS_MEMBR ASN                        
      IBMSNAP_SUBS_SETX  ASN      IBMSNAP_SUBS_SET   ASN                        
      IBMSNAP_SUBS_STMTX ASN      IBMSNAP_SUBS_STMTS ASN                        
      IBMSNAP_UOW_IDX    ASN      IBMSNAP_UOW        ASN                        
      DSN_REGISTER_APPLI DSNRGCOL DSN_REGISTER_APPL  DSNRGCOL                   
      DSN_REGISTER_OBJTI DSNRGCOL DSN_REGISTER_OBJT  DSNRGCOL                   
      XACT1              DSN8810  ACT                DSN8810                    
      XACT2              DSN8810  ACT                DSN8810                    
      XDEPT1             DSN8810  DEPT               DSN8810                    
      XDEPT2             DSN8810  DEPT               DSN8810                    
      XDEPT3             DSN8810  DEPT               DSN8810                    
      XEMP1              DSN8810  EMP                DSN8810                    
      XEMP2              DSN8810  EMP                DSN8810                    
      XEMPPROJACT1       DSN8810  EMPPROJACT         DSN8810                    
      XEMPPROJACT2       DSN8810  EMPPROJACT         DSN8810                    
      XMAP_TBL           DSN8810  MAP_TBL            DSN8810                    
      XPARTS             DSN8810  PARTS              DSN8810                    
      XPROJ1             DSN8810  PROJ               DSN8810                    
      XPROJ2             DSN8810  PROJ               DSN8810                    
      XPROJAC1           DSN8810  PROJACT            DSN8810                    
      XCONA1             DSN8810  TCONA              DSN8810                    
      XDSPTXT1           DSN8810  TDSPTXT            DSN8810                    
      XOPTVAL1           DSN8810  TOPTVAL            DSN8810

    The following columns are on this panel:

    Sel
    Selects an index when you use the S line command.
    Index Name
    The name of the index.
    Index Schema
    The authorization ID of the owner of the index.
    Table Name
    The name of the table on which the index is defined.
    Table Schema
    The authorization ID of the owner of the table.
  3. Optional: Work with the indexes by using the primary commands and line commands.

    Consider dropping indexes that are not used by any plan or package with static SQL if they are not used in QMF or any other dynamic SQL statement.

    The DROP line command enables you to quickly issue a DROP INDEX statement. Entering the DROP line command is equivalent to entering an S line command and a DROP line command in succession.