Extracting SQL and recommending an index

Use the following procedure to Extract SQL from one of the supported sources and generate an index recommendation for the associated workload.

Before you begin

  • Create the object set definition that identifies the objects for the sources that you want to include in the workload (as described in Working with object sets). You do not need to create object set definitions if your source is a SQL text data set or DBRM library source.
  • Gather the information for the source type of the workload for which you are Extracting data. For more information, see Description of source type information.

  • Consider the following for APPTUNE trace data set sources:

    Workload Access Path Compare Extracts static and dynamic SQL from APPTUNE archived trace data sets that contain the following data classes:

    • APSTMT (BMC IFCID 005), used to Extract the SQL text
    • (optional) APSTACC (BMC IFCID 307), used to determine the execution count

Note

If you want to Compare statements based upon certain types of collection data, you must ensure that you have set the collection keys in APPTUNE before the SQL statements are executed.

Dynamic Explain requests now use the ADMIN_EXPLAIN_MAINT IBM Db2 stored procedure. This stored procedure can create or update Explain tables to the correct format for the version of Db2 that you are running. It can also create the indexes recommended by IBM on the Explain tables.

Ensure that the ADMIN_EXPLAIN_MAINT stored procedure is installed correctly for successful Explains.

Installing the ADMIN_EXPLAIN_MAINT stored procedure

For Db2 Version 11 or later, use the following procedure to install the ADMIN_EXPLAIN_MAINT stored procedure:

  1. Create a JCL startup procedure for the IBM z/OS Workload Manager (WLM) environment, if you do not already have one.
  2. Set up the IBM stored procedure using the DSNTESR member from the Db2 DSNSAMP data set. Perform the following steps:
    1. Create the following global temporary tables:
      • SYSIBM.EXPLAIN_MAINT_SUMMARY
      • SYSIBM.EXPLAIN_MAINT_SQL
      • SYSIBM.EXPLAIN_MAINT_TB_NOT_UPGRADED
    2. Define the SYSPROC.ADMIN_EXPLAIN_MAINT stored procedure by specifying your WLM environment and the COLLID for the DSN% packages.
    3. Issue a GRANT ALL to PUBLIC authority to the global temporary tables.
    4. Issue a GRANT EXECUTE to PUBLIC authority to the stored procedure.
    5. Remove the comments from the bind statement and run the bind for the DSNADMEM package, specify the DSNDBRM Db2 library, and perform the bind.
  3. Activate the WLM environment.
  4. Start the stored procedure.

For more information, see the IBM Db2 for z/OS installation documentation.

For information about the authorities you require for Workload Advisor Explains, see Required-authorities-for-Workload-Advisor-Explains-and-the-Index-Advisor-feature

To Extract SQL and recommend an index

  1. Access the Workload Index Advisor.

    1. On the 

      SQL 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.
      The Index Advisor Menu is displayed.

      PSSWI100 --------------------- Index Advisor Menu -----------------------------
      Command ===>                                                                   
                                                                                    
      Select one of the following options and then press ENTER to continue.          
                                                                                    
         1 1. Extract SQL and         - Define workload, Create SQL file and         
              Recommindex               Recommend Indexes                            
                                                                                    
           2. Recommindex             - Recommend Indexes                            
                                                                                    
           3. Review Recommendations  - Generate Index Advisor reports               
                                                                                    
        Specify workload high level qualifier :                                      
         HLQ: PSS.V101TEST.WLCIX           (Max 26 characters)

    3. Select 1 (Extract SQL and Recommindex).

      Note

      Some SQL statements that are extracted might be unexplainable because of various contextual issues within the SQL text. These statements are reported in the Explain step as a part of the output of the Workload Compare or Index Advisor job. This doesn’t necessarily mean that a problem exists.

    4. Specify a workload HQL to be used for the repository and output files that contain the Extracted SQL text.This value must be a valid data set name qualifier and cannot exceed 26 characters. Low-level qualifiers are appended and the data sets are created if they do not already exist.
    5. Press Enter to display the Extract SQL for Workload panel.
  2. Identify the sources for the workloads from which you want to recommend indexes:

    1. In the Object Set Name field, use one of the following responses:

      • If you want to Explain SQL for a DBRM library or a SQL text data set, ignore this field.
      • If you want to Explain SQL for any other source type, type the name of the object set that contains the naming patterns you want to use for this workload.

      For more information about creating object sets, see Working-with-object-sets.

    2. In the Object Set Data Set field, use one of the following responses:
      • If you want to Explain SQL for a DBRM library or a SQL text data set, ignore this field.
      • For any other source type, type the name of the data set that contains your object set definitions.
    3. Select the source type for the workload for which you want to Extract SQL to Explain.
    4. If necessary, update the high-level qualifier for the workload in the HLQ field.
  3. Specify the parameters for the source type that you selected.The panels displayed depend on the source type you selected. You can use the panels to specify the source of the SQL text that you want to Extract for this workload.

    1. Complete the panels for your source type (as listed in the following table).

      Source type
      Panels to complete
      Related info
      DB2 catalog
      SQL Workload Source Extract from DB2 catalog
      APPTUNE trace data
      • SQL Workload Source Extract from APPTUNE Trace Data
      • SQL Workload Source - Archive Trace Data
      • SQL Workload Source - Add Archive Trace Data
      NoteThe product displays the SQL Workload Source - Archive Trace Data panel only if you specified Y in the List APPTUNE data set field on the previous panel. On this panel, select the trace data sets that you want to use and then press F3 to continue.
      To add a data set that is not displayed in the list, type A in any action field or on the Command line and type the data set name in the SQL Workload Source - Add Archive Trace Data panel.You might want to add an additional archive trace data set if it was not registered in the COPYDIR when created. The added archived trace data sets exist only in the list for the SQL Extraction. They are not saved in the APPTUNE archive directory.
      DBRM library
      SQL Workload Source Extract from DBRM library
      BMC Performance Database
      • Extract SQL for Performance Advisor Database
      • Extract parameters for Performance Advisor Database
      SQL text data set
      SQL Workload Extract from SQL Text Data Set
      Statement CACHE
      SQL Workload Source Extract from DB2 Statement CACHE

    2. After you finish entering your source information, press Enter to display the Recommindex panel.

       

      PSSWC125 ------------------------- Recommindex --------------------------------
      Command ===>                                                                   
                                                                                     
       Index Advisor Report Options : P                                              
                                                                                     
       Specify SQL workload qualifiers :                                             
        Explain type : AN     1. XD = Explain dynamic                                
                                      Plan Table Owner :          (USERID,authid)   
                                                                                     
                                                                                     
       Explain SSID :                                                         
                                                                                     
       Import DDL   :    
       DDL Option   : 1  1. Recommindex  2. DDL  3. Both
                                                
       Export DDL      :                        
       Export Option   : Export DDL where Savings >          0   
                                                                                 
       Remarks      :                       
  4. At the Recommindex panel, specify the Explain parameters for the operation that you want to perform:
    1. Specify whether you want to change the settings for reports in the Index Advisor Report Options field.If you specify Y (Yes), see Specifying-report-options-for-Index-Advisor-reports for instructions.
    2. In the Explain type field, type XD to select a dynamic Explain operation or XS to select static Explain operation. 
      Note Dynamic Explains (XD) use the DB2 optimizer to identify the access paths. This process evaluates access paths, SQL text, and key catalog statistics in real time.Static Explains (XS) report the access path information that was derived from the owner.PLAN_TABLE when you performed a bind with EXPLAIN(YES). This type of Explain is only valid for static SQL.
    3. If you selected dynamic Explain, in the Plan Table Owner field, type the user ID or authorization ID for the plan tables to be used.The default is BMC. 
      Tip For a dynamic Explain, the DB2 optimizer stores information in the plan tables of a specified owner. After reading and reporting on the access paths, the product deletes the rows from the plan tables. To prevent creation of multiple empty plan tables, all users must use the same plan table owner (such as BMC). Note that you must have authority to perform a SET CURRENT SQLID command to point to a plan table owner that is different from the current AUTHID. If you do not have this authority, the product uses the current AUTHID as the owner of the plan tables.BMC recommends that you point to BMC or empty plan tables. If your plan table has many rows, add the recommended indexes in the DAADB2IX member in the SAMP library to avoid performance problems.
    4. In the Explain SSID field, type the name of the Db2 subsystem on which the index recommendation operation is performed.
    5. In the Import DDL field, type the name of the data set containing the DDL statements for indexes that you want to change, delete, or drop for the workload during your analysis.You can generate these DDL statements with the What-If Index processing

    6. In the DDL Option field, specify:

      • 1 If you want to only generate the recommended index without using DDL
      • 2 If you want to use the DDL without recommending indexes
      • 3 If you want to generate the recommended indexes and apply the DDL to your analysis

      Option 1 is the default.

    7. (BMC.DB2.SPE2110) (Optional) In the Export DDL field, type the name of the data set to receive the index DDL statements for the indexes that are recommended with a savings value for the workload. You can generate the DDL statements from the Index Advisor report automatically.
    8. (BMC.DB2.SPE2110) In the Export Option field, specify:
      • Export DDL where Savings > :  Specify a value If you want to only generate the recommended index definitions where the savings value exceeds the threshold. The default savings value is 0. 
    9. (Optional) In the Remarks field, type text to help you identify the workload.You can enter up to 40 alphanumeric characters.
    10. Press Enter to display the Batch Job panel.
  5. Specify the batch JCL options. 
    Note When you perform an Extract on a workload to recommend an index, Index Advisor generates a JCL job for you to submit. This process is also used when Comparing workloads with the Workload Access Path Compare feature.
    1. Complete the Batch Job options. Related information appears in the Source Workload Extract panels by source table.
    2. Press Enter to complete the actions that you specified.


 

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