Skip to Content
Information
Space announcement This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

Reviewing index recommendations

Use this procedure to review the index recommendations for workloads.

Related topic

Recommending-indexes-for-workloads

For each index that the product analyzes, the Recommend field indicates the recommendation for that index. Possible values include:

  • Create—Index Advisor recommends creating this index after analyzing the predicates in the workload. The optimizer used this index in at least one of the statements in the workload and a cost savings was observed.
  • Keep—Index Advisor recommends keeping this existing index. The optimizer used this index while Explaining at least one statement in the workload.
  • Not seen—Index Advisor recommends this index after reviewing the results from analyzing the predicates or this index is an existing index. However, the optimizer does not use this index in any statements in the workload.
  • No savings—Index Advisor recommends this index after reviewing the results from analyzing the predicates in the workload. However, the overall cost increased when the index was created, so no savings are associated with creating this index.

In the Index Recommendation Report, the Savings field indicates the potential cost savings of using the index. For information about additional fields, see Workload-Access-Path-Compare-and-Index-Advisor-report-fields.

PSSWC140          RECOMMINDEX : INDEX RECOMMENDATION  REPORT                   
Command ===>                                                  Scroll ===> CSR
                                                                 More:       >
   Actions: S T H                                                              
  TABLE        PCT PCT                     E L FREE PCT BUFFER     SEG         
  NAME         PAG COM CARDF     NPAGESF   S R PAGE FRE POOL     C SIZ PART NAC
  ------------ --- --- --------- --------- - - ---- --- -------- - --- ---- ---
  ORD_LN_ITEM   -1   0  30217796   5042070 E A   10  35 BP25         0   32   5
       INDEX                                  U C C                BUFFER   FIR
       NAME         SAVINGS  #STMTS RECOMMEND R G D NLEAF     NLVL POOL     KEY
       ------------ -------- ------ --------- - - - --------- ---- -------- ---
       BMC00001       411908      6 CREATE    D N N         1    1 BP0         
       BMC00012       260514      3 CREATE    D N N        55    2 BP0         
       BMC00008          136      1 CREATE    D N N    157385    3 BP0         
       BMC00011           13      1 CREATE    D N N    164228    3 BP0         
       BMC00010            2     27 CREATE    D N N    134302    3 BP0        8
       IXA01055            0    165 KEEP      P Y Y    187916    3 BP5       25
       IXB01055            0      3 KEEP      D N N     86748    4 BP5        8
       IXC01055            0     18 KEEP      D N N    269842    4 BP5         
       IXD01055            0     57 KEEP      D N N    270223    4 BP5         
       IXE01055            0     14 KEEP      D N N    343912    4 BP49        
       BMC00004            0      5 CREATE    D N N    111918    3 BP0       25
       BMC00006            0      0 NOT SEEN  D N N    134302    3 BP0       25
       BMC00007            0      0 NOT SEEN  D N N         2    1 BP0         
 ******************************** Bottom of Data *******************************

To review index recommendations

  1. On the 

    BMC System Performance for Db2

     main menu, select A (Performance Advisors) and press Enter.

  2. On the Performance Advisors panel, select 2 (Workload Index Advisor) and press Enter.
  3. Choose to review index recommendations:
    1. Select 3 (Review Recommendations).
    2. Verify that the workload HLQ for the repository matches the one used when performing the recommendation process.

    3.  Press Enter to display the Index Advisor Workloads panel.This panel lists all workloads in the specified repository that have generated indexes.

      PSSWC245 ------------------ Index Advisor Workloads  --------- Row 1 to 4 of 4
      Command ===>                                               Scroll ===> CSR     
                                                                                    
        Specify a workload                                                           
                                                                                    
        Actions: S V D                                                               
         Explain SQL       Explain Source                                            
         type    source    DB2     DB2    Remarks                                    
         ------  --------  ------  ------ ----------------------------------------   
       _ XD      SQLFILE   DEDR    DEDR   IXA - SELECT FROM SYSPACKAGES - TEST       
       _ XD      CATALOG   DEDK    DEDK   IXA - SQL TEXT FROM COMPANY                
       _ XD      SQLFILE   DEDR    DEDR   IXA - SQL TEXT- NO DUPE CURSORS            
       _ XD      SQLFILE   DEDR    DEDR   IXA - SQL TEXT FROM COMPANY                
      ******************************* Bottom of data ********************************
      Success

      Tip

       To locate an object set by searching for a text string, type FIND string on the COMMAND line, and press Enter. Press F5 to move the cursor to a subsequent instance of the specified text.

  4. Review the index recommendations for a workload:

    1. In the Action field for the workload for which you want to display the results of your index analysis, type S and press Enter.

      Success

      Tip

      From the Index Advisor Workloads panel, you can also:

      • View more information about the workload by typing V and pressing Enter.
      • Permanently delete a workload from the repository by entering D next to the workload and pressing Enter.

    2. On the Recommindex: Index Recommendation Report panel, specify the action that you want to perform:

      PSSWC140          RECOMMINDEX : INDEX RECOMMENDATION  REPORT                   
      Command ===>                                                  Scroll ===> CSR
                                                                       More:       >
         Actions: S T H                                                              
        TABLE                PCT  PCT                     E L FREE  PCT BUFFER      S
        NAME               PAGES COMP     CARDF   NPAGESF S R PAGE FREE POOL     C SI
        ------------------ ----- ---- --------- --------- - - ---- ---- -------- - --
        V8V9DFTB              99    0      2036       255 E A    0    0 BP0          
             INDEX                                        U C C                BUFFER
             NAME                SAVINGS #STMTS RECOMMEND R G D     NLEAF NLVL POOL  
             ------------------ -------- ------ --------- - - - --------- ---- ------
             BMC00001                 19      1 CREATE    D N N        11    2 BP0   
             BMC00002                  0      1 NO SAVING D N N         4    2 BP0   
             V8V9DFTB_INDEX3           0      1 KEEP      U Y N        79    2 BP0   
      ******************************** Bottom of Data *******************************
      • To show the details for a workload, type S in the action column next to the object for which you want to show details and press Enter.
      • To hide the details for a workload, type H in the action column next to the object for which you want to hide details and press Enter.
      • To show the SQL text for a statement, type T in the action column next to the statement and press Enter.

      The following example shows an expanded statement.

      Information
      Example
        TABLE                PCT  PCT                     E L FREE  PCT BUFFER      S
        NAME               PAGES COMP     CARDF   NPAGESF S R PAGE FREE POOL     C SI
        ------------------ ----- ---- --------- --------- - - ---- ---- -------- - --
        V8V9DFTB              99    0      2036       255 E A    0    0 BP0          
             INDEX                                        U C C                BUFFER
             NAME                SAVINGS #STMTS RECOMMEND R G D     NLEAF NLVL POOL  
             ------------------ -------- ------ --------- - - - --------- ---- ------
             BMC00001                 19      1 CREATE    D N N        11    2 BP0   
                  KEY           CO CO   INDEX                                        
                  COLUMN        NO SQ O NAME                                         
                  ------------ --- -- - ------------------                           
                  COLUMN_15     13  1 A BMC00001                                     
                  EXPL SRC                                                           
                  SSID SSID NAME     COLLID       VERSION                    STMTNO  
                  ---- ---- -------- ------------ -------------------------- ------ -
                  DEDK DEDK V8V9DIFF V8V9DICI     2.01                          305  
                       DECLARE  CUR-V8V9DIFF-TWO CURSOR FOR                          
                       SELECT   DISTINCT COLUMN_15                                   
                       FROM     V8V9DFTB                                             
                       WHERE    COLUMN_15 > '2008-08-11-16.14.50.435571'             
                       FOR      FETCH ONLY
      • To show details for all workloads, type S ALL in the Command line.
      • To hide details for all workloads, type H ALL in the Command line.
      • To locate a workload by searching for a text string, see the note to Step 3.c. 
      Warning

      Important

       Fields might scroll off your visible viewing area to the right. Press F10 or F11 to scroll to the left or the right.

      For information about fields on the reports, see Workload-Access-Path-Compare-and-Index-Advisor-report-fields.



 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

SQL Performance for DB2 13.1