Preparing to use What-If Index

Use this procedure to specify a data source, select an interval, and specify an initial report for your analysis.

  1. On the System and SQL Performance for DB2 main menu, select 

    Apptune

     and Index Component, and press Enter.The Apptune and Index Component Main Menu is displayed.

    IODESELC/I           APPTUNE AND INDEX COMPONENT MAIN MENU            14:57:20
    COMMAND ====> ________________________________________________________________
                                                                                 
    CURRENT DATA COLLECTOR : DOB3    STATUS : ACTIVE   DATA COLLECTION : READY    
                                                                                 
    SELECT ONE OF THE FOLLOWING OPTIONS.  THEN PRESS ENTER.                       
                                                                                 
    _  0. STATEMENT CACHE      - ANALYZE SQL STATEMENT CACHE STATISTICS           
       1. SQL WORKLOAD         - ANALYZE CURRENT AND HISTORICAL SQL WORKLOADS     
                                                                                 
       3. EXPLAIN INTERFACE    - EXPLAIN AN SQL STATEMENT                         
                                                                                 
       4. APPLICATION PROFILES - CREATE AND MAINTAIN APPLICATION GROUPS           
       5. COMMAND INTERFACE    - ISSUE COMMANDS, VIEW RESPONSES                   
                                                                                 
       D. ARCHIVE DIRECTORY    - VIEW/MANAGE THE DIRECTORY OF TRACE ARCHIVES      
                                                                                 
       Z. ABOUT APPTUNE

  2. Select SQL Workload, and press Enter.The SQL Workload Analysis Menu is displayed. This menu displays the criteria that are used to select data for workload analysis reporting, and offers options to begin reporting and to define new reporting criteria.

     ASQEWAM1/I               SQL Workload Analysis Menu                   03:56:11
    Command ====>___________________________________________________________
                    
    Report Criteria:                                                              
     Source of data  . . . : Subsystem MB2D                                       
     For DB2 SSIDs . . . . : *                                                    
     Start time  . . . . . : 02/08/2017 03:00:00                                  
     Duration  . . . . . . : No time limit                                        
     Initial report  . . . : PLAN ANALYSIS (DATA)                                 
     Application profile . : DEFAULT             Owner :                          
     Workload focus  . . . : NONE                Qualified: N                     
                                                                                 
    Select one of the following options.                                          
                                                                                 
    _ 1. Workload analysis        - Begin workload reporting                      
      2. Initial report           - Select the initial report to be viewed        
      3. Report type              - Select the report type (data or graph)        
      4. Application profile      - Select the profile for group reporting        
      5. Time interval            - Specify the time frame for reporting          
      6. Data source, DB2(s)      - Select data source and DB2 subsystems         
      7. Workload focus           - Specify options for faster report access

  3. Select Data source, Db2(s) and press Enter to display the Data Source panel.

    DOMEPNL3 I                        Data Source                         14:42:09
    Command ====> ________________________________________________________________
                                                                                  
    Type the data source for reporting below. Then Exit.                           
                                                                                  
    Data source . . . . . . S           ( D =Data set            )                 
                                        ( A =Archive Directory   )                 
                                        ( S =DC Subsystem : N01A )                 
    Data set  . . . . . . . AFDQA.Q01A.ACCT.ARCHIVE.GDG.G0272V00________           
                                                                                  
    DB2 SSID list . . . . . *___ ____   ( * for all)

  4. On the Data Source panel, specify data source information for reporting, and press F3.

    Warning

    Index analysis is sensitive to certain objects and their statistics, and to the SQL executed against those objects. For this reason BMC Software recommends that you limit your index reporting to one Db2 subsystem or data sharing group at a time. Reporting on index information gathered from multiple subsystems or from subsystems that are not related by data sharing might lead to a misleading report or result.

  5. At the SQL Workload Analysis Menu, select Time interval and press Enter.The Select Analysis Interval report is displayed.

    DOMEPNL4 I                     View a Report                      LINE 1 OF 14
    Command ====> _______________________________________________ Scroll ===> CSR_
    BMCSftwr.SQMINTVD    --    SELECT ANALYSIS INTERVAL    --       03/07 14:43:20
       Select a single interval or a range of intervals. Type 'S' beside a single  
       interval or beside the first and last intervals in the range, and press     
       Enter.  Exit when finished.                                                 
                                                                                  
      Actions: F-Filtering Criteria                                                
                                                                                  
       +------------------- Interval -----------------+        Number     Active   
       Begin               End                 Duration  DB2   Entries    Filter   
       ------------------  ------------------  --------  ----  -------   --------  
    +  03/07/11  00:00:00  03/07/11  14:43:19  14:43:20  DEC9     3421   AMD1      
    +  03/07/11  00:00:00  03/07/11  14:43:19  14:43:19  DEDR    25231   AMD1      
    +  03/06/11  00:00:00  03/06/11  23:59:59  23:59:59  DEC9     3425   AMD1      
    +  03/06/11  00:00:00  03/06/11  23:59:59  23:59:59  DEDR     9535   AMD1      
    +  03/05/11  12:07:59  03/05/11  23:59:59  11:52:00  DEC9     3497   AMD1      
    +  03/05/11  12:07:59  03/05/11  23:59:59  11:52:00  DEDR     9245   AMD1      
    +  03/04/11  13:56:00  03/04/11  23:59:59  10:03:59  DEC9     3417   AMD1      
    +  03/04/11  13:55:00  03/04/11  23:59:59  10:04:59  DEDR    14739   AMD1      
    +  03/04/11  00:00:00  03/04/11  13:55:59  13:55:59  DEC9     3319   AMD1      
    +  03/04/11  00:00:00  03/04/11  13:54:59  13:54:59  DEDR    32912   AMD1
  6. To select an interval, type S beside a single interval or beside the first and last intervals in the range, and press Enter. Press F3 when finished.

  7. At the SQL Workload Analysis menu, select Initial report, and press Enter.The SQL Workload Initial Analysis Level panel is displayed.

    IODEWAL1/I             SQL WORKLOAD INITIAL ANALYSIS LEVEL              00:28:24
     COMMAND ====> ________________________________________________________________
                                                                                   
     CURRENT INITIAL REPORT : INDEX TABLE GETPAGE VOLUME                            
                                                                                   
     SELECT ONE OF THE FOLLOWING INITIAL REPORTS TO DISPLAY, THEN PRESS ENTER.      
     22 APPTUNE REPORTS:                                                            
         1. DB2 SUBSYSTEM ID                16. LOGICAL DB2 (DS GROUP OR SSID)      
         2. PROGRAM/DBRM                    17. REQUESTING LOCATION                 
         3. PLAN                            18. IMPLICIT QUALIFIER                  
         4. USER/OPERATOR ID                19. CLIENT CORRELATION TOKEN
         5. APPLICATION GROUP                                                       
         6. CONNECTION ID                   INDEX COMPONENT REPORTS:                
         7. SQL STATEMENT                   21. SUBSYSTEM GETPAGE VOLUME            
         8. SQL ERROR CODE                  22. TABLE GETPAGE VOLUME                
         9. CORRID                          23. INDEX GETPAGE VOLUME                
        10. OBJECTS                         24. APPLICATION GROUP GETPAGE VOLUME    
        11. CLIENT APPLICATION NAME                                                 
        12. CLIENT WORKSTATION NAME                                                 
        13. CLIENT USER ID                                                          
        14. INTERVAL                                                                
        15. SAP

  8. In the 

    Apptune

     Reports field, select Table Getpage Volume, and press Enter.

  9. At the SQL Workload Analysis Menu, select Workload analysis, and press Enter.
    The Table Getpage Volume Report is displayed. From this panel, you can zoom to other reports for more detailed table statistics, index statistics, a CRUD matrix for a table, or a list of SQL statements for a table.
    For more information, see Identifying-What-If-Index-candidates.

Related topic

Using the Index Component

 

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