Skip to Content

Extracting and Explaining a workload

Use this procedure to define a workload by using the Workload Access Path Compare component, and then Extract and Explain the SQL text for a workload from a specific source type.

Warning

Note

Some SQL statements that have been extracted may not be explainable due various contextual issues within the SQL text. These statements are reported in the explain step as part of the output of the Workload Compare or Index Advisor job and does not necessarily mean that a problem exists.

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
Warning

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 and Explain SQL from a workload

  1. Access the Workload Access Path Compare component:

    1. On the 

      SQL Performance for DB2

       Main Menu, select A (Performance Advisors) and press Enter.

    2. On the Performance Advisors panel, select 1 (Workload Access Path Compare) and press Enter.The Workload Access Path Compare Main Menu is displayed. 

      PSSWC100 -------------- Workload Access Path Compare Menu ---------------------
      Command ===>                                                                   
                                                                                    
      Select one of the following options and then press ENTER to continue.          
                                                                                    
         1 1. Extract and Explain SQL - Define workload                              
                                                                                    
           2. Explain SQL             - Explain SQL from Extract step                
                                                                                    
           3. Compare access paths    - Compare Explain results & generate report    
                                                                                    
        Specify workload high level qualifier :                                      
         HLQ: RDAVEM.WLC                   (Max 26 characters)
    3. Select 1 (Extract and Explain SQL).
    4. Specify a workload HLQ to use 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. The product appends low-level qualifiers and creates the data sets if they do not already exist.

    5. Press Enter to display the Extract SQL for Workload panel. 

      PSSWC105 ------------------ Extract SQL for Workload  ------------------------
      Command ===>                                                                  
                                                                                   
         _ Select to edit an Object Set Definition                                  
             Object Set Name : MYOBJECTSET       (blank to add new, % to show list)
             Object Set Data Set : RDAVEM.TEST.OBJSETDB                             
                                                                                   
           Select one of the following Workload sources and press Enter to continue
                                           Object Set Types Used                    
                                           --------------------------               
              _ DB2 catalog                PL, PG, DB, TS, TB                   
              _ APPTUNE trace data         PL, PG, CL, US, CR, DB, TS, TB       
              _ DBRM library               None                                     
              _ BMC Performance Database   PL, PG, CL, US, CR, DB, TS, TB       
              _ SQL Text data set          None                                     
              _ Statement CACHE            PG, US                               
                                                                                   
        Specify workload high level qualifier :                                     
         HLQ: RDAVEM.WLC                   (Max 26 characters)
  2. Identify the source for the workload from which you want to Extract SQL:
    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 that you want to use for this workload.
    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 for the workload for which you want to Extract SQL to Explain.
    4. If necessary, update the HLQ for the workload in the HLQ field.
  3. Specify the parameters for the source type that you selected.Depending on the source type that you selected, various panels are displayed. Use the panels to specify information about the source of the SQL text that you want to Extract for this workload.

    1. Complete the panels for your source type (as described in Source Workload Extract panels by source 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
      Warning

      Note

      The 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. Press Enter to display the SQL Workload Explain panel. 

      PSSWC120 -------------------- SQL Workload Explain ----------------------------
      Command ===>                                                                   
                                                                                    
       Specify SQL workload qualifiers :                                             
        Explain type : XD     1. XD = Explain dynamic                                
                                      Plan Table Owner : BMC       (USERID,authid)   
                              2. XS = Explain static from bind with EXPLAIN(YES)     
                                                                                    
        Explain SSID : DEBF                                                          
                                                                                    
        Remarks      : Extract and Explain V8V9DIFF on DEBF
  4. Specify the type of Explain operation that you want to perform.
    1. In the Explain type field, type XD to select a dynamic Explain operation or XS to select static Explain operation. 
      Warning
      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.
    2. If you selected dynamic Explain, in the Plan Table Owner field, enter the user ID or authorization ID for the plan tables to be used.The default is BMC. 
      Success
      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.
    3. (optional) In the Remarks field, type text to help you identify the workload.You can enter up to 40 alphanumeric characters.

    4. Press Enter to display the Batch Job panel. 

      PSSPB000 -------------------------- Batch Job ---------------------------------
      Command ===>                                                                   
                                                                                    
      Specify a partitioned (include member) or sequential JCL data set.             
                                                                                    
      JCL Data Set . . . 'RDAVEM.SQLXPLR.CNTL(SQLX####)'                             
      Current Counter    24    (Replaces #### in member name, then incremented)      
                                                                                    
      Set JCL Options    N     (Y/N - Change options for creating JCL)               
      Build Job  . . . . Y     (Y/N - Create JCL, save in JCL data set)              
      Edit Data Set  . . Y     (Y/N - Edit JCL data set)                             
      Submit . . . . . . N     (Y/N - Submit JCL data set)
  5. Specify the batch JCL options and submit the job.When you perform an Extract and Explain, an Explain, or a Compare operation, Workload Access Path Compare generates a job for you to submit. The Batch Job panel enables you to set options and build and edit the JCL before executing the jobs.

    1. Complete the Batch Job options as described in the Batch Job options table.

      Field

      Action

      JCL Data Set

      Specify the data set to which Workload Access Path Compare writes the generated JCL.

      You can type the name of a partitioned or sequential file, or you can specify the TEMP keyword. If you specify TEMP, Workload Access Path Compare uses the data set that is specified in ZTEMPF.

      Warning

      Note

      If any of the data sets do not exist, the product displays the Allocate Data Set panel, enabling you to allocate a new data set.

      Current Counter

      Enter any value from 1 through 9999 to replace the #### suffix in the PDS member name.

      If needed, Workload Access Path Compare adds leading zeros to fill the four-digit suffix.

      Set JCL Options

      (optional) Specify Y to review or change options for creating JCL.

      For information about setting JCL options, see the BMC AMI SQL Explorer for Db2.

      Build Job

      Specify whether to generate the JCL into the data set that you specified in the JCL Data Set field.

      Warning

      Note

      After you build and edit the JCL the first time, an asterisk (*) apears in both the Build Job and Edit Data Set fields. The asterisk ensures that you do not accidentally regenerate and overwrite existing JCL.

      Edit Data Set

      Specify whether to edit the data set online.

      If you specify Y, the JCL is displayed in an ISPF edit session.

      Submit

      Specify whether to submit the JCL for execution.

      BMC recommends that you specify N the first time the Batch Job panel is displayed. After the product generates the JCL, make any necessary changes to your data set. When the Batch Job panel is displayed again, specify Y in the Submit field to execute the JCL.

    2. Press Enter to complete the actions that you specified.

Related topic

Comparing-access-paths-for-workloads



 

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

SQL Performance for DB2 12.1