Dropping an index by using What-If Index

Before you begin

To drop an index

1. On the Explain Results panel, type D beside the index that you want to drop, and press Enter.

   FILE  COMMANDS  OPTIONS  HELP
   -------------------------------------------------------------------------------
   PSSPW200      Explain Results for SQLTEXT                                      
   Command ===>                                                  Scroll ===> CSR
                                                                    More:       >
      Actions: S H R RS RW RI XD XS XP W P T C D U IM SA                             
      CLONE  Objects in database PSS60364                                         
      STMTNO SQL-STATEMENT                                                        
         117 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'                       
            CREATOR TBNAME          CARD    NPAGES COMP  NACTIVE PARENT CHILD BPOO
            PXB     EMP               -1        -1   -1       -1      0     0 BP0
               CREATOR IXNAME          1STKEY     FULLKEY NLEAF NLEVELS CLUSTERRAT
   D           PXB     XEMP2               -1          -1    -1      -1 0.00000000
               PXB     myindex             -1          -1    20       2 0.80000000
      LBL   STMTNO     COST*RATE SQL-STATEMENT                                    
      WI01*    117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
      XD01     117   1012.256592 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
            COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TBNAME   IXNAME   NU J O G
      WI01* 344.63818  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
      XD01  1012.2565  1  1   0 SELECT    0 R      0 N  EMP               N  N N N
   ******************************** Bottom of Data *******************************

   The index to be dropped from the clone is removed from the display (as in Explain Results panel showing dropped index). At this point, the index has not yet been dropped even though it is no longer displayed on the panel. The index is dropped when you perform the Explain.

   Note

   You can make any number of changes before performing the Explain. The What-If Index process is cumulative, so incremental changes followed by Explains enable you to see the effects of each change. When you perform a dynamic Explain on a clone, the Db2 optimizer uses the cloned objects as they exist at that point in time. The Index Component compares each successive What-If Index Explain to the previous Explain.

   FILE  COMMANDS  OPTIONS  HELP
   -------------------------------------------------------------------------------
   PSSPW200      Explain Results for SQLTEXT                                      
   Command ===>                                                  Scroll ===> CSR
                                                                    More:       >
      Actions: S H R RS RW RI XD XS XP W P T C D U IM SA                             
   XD CLONE  Objects in database PSS60364                                         
      STMTNO SQL-STATEMENT                                                        
         117 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'                       
            CREATOR TBNAME          CARD    NPAGES COMP  NACTIVE PARENT CHILD BPOO
            PXB     EMP               -1        -1   -1       -1      0     0 BP0
               CREATOR IXNAME          1STKEY     FULLKEY NLEAF NLEVELS CLUSTERRAT
               PXB     myindex             -1          -1    20       2 0.80000000
      LBL   STMTNO     COST*RATE SQL-STATEMENT                                    
      WI01*    117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
      XD01     117   1012.256592 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
            COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TBNAME   IXNAME   NU J O G
      WI01* 344.63818  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
      XD01  1012.2565  1  1   0 SELECT    0 R      0 N  EMP               N  N N N
   ******************************** Bottom of Data *******************************

2. To perform a dynamic Explain, type XD in the space beside the clone, and press Enter.The results of the Explain are displayed (in Explain Results panel after index is dropped). The WI02 label represents the Explain on the cloned objects with new index myindex and having dropped index XEMP2.

   FILE  COMMANDS  OPTIONS  HELP
   -------------------------------------------------------------------------------
   PSSPW200      Explain Results for SQLTEXT                                      
   Command ===>                                                  Scroll ===> CSR
                                                                    More:       >
      Actions: S H R RS RW RI XD XS XP W P T C D U IM SA                             
      CLONE  Objects in database PSS60364                                         
      LBL   STMTNO     COST*RATE SQL-STATEMENT                                    
      WI02     117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
      WI01*    117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
      XD01     117   1012.256592 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
            COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TBNAME   IXNAME   NU J O G
      WI02  344.63818  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
      WI01* 344.63818  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
      XD01  1012.2565  1  1   0 SELECT    0 R      0 N  EMP               N  N N N
   ******************************** Bottom of Data *******************************
3. You can now perform additional What-If Index operations such as adding another index, updating statistics for an index, and so on.