Comparing two or more workloads

Use this procedure to compare two or more Explain results and generate a report.

Before you begin

You must Extract and Explain the SQL before running the comparison reports.

To compare access paths and generate reports

On the
SQL Performance for DB2
main menu, select A (Performance Advisors) and press Enter.
On the Performance Advisors panel, select 1 (Workload Access Path Compare) and press Enter.
Generate a workload comparison:
1. Select 3 (Compare access paths).
2. Verify that the workload HLQ is the same as the one used when performing the procedure Explaining-a-workload-a-second-time.
3. Press Enter to display the SQL Workload Compare panel.This panel lists all workloads that were previously explained and that exist for the specified workload HLQ.
  PSSWC130 -------------------- SQL Workload Compare ---------- Row 1 to 2 of 2
  Command ===>                                               Scroll ===> CSR
  
  Processing mode : O (O = Online, B = Batch)
                    _ Report options
  
    Baseline (0) workload and one or more comparison workloads (1-9)
  
     Actions: V D 0-9
       Explain SQL      Explain Source
       type    source   DB2     DB2    Remarks
       ------ -------- ------ ------ ----------------------------------------
  _    XD     CATALOG   DEDR   DEDR   DEDR V8V9DICI.V8V9DIFF.%
  _    XD     CATALOG   DEBF   DEBF   DEBF V8V9DICI.V8V9DIFF.%
  ******************************* Bottom of data ********************************
Specify report options and select the workload files:
1. In the Processing mode field, type O to run the comparison online or B to run the comparison in batch.
  Success
  Tip
  Consider using batch processing for extremely large workloads. Processing large workloads online can take a significant amount of time.
2. (optional) To specify report options, type any character in the Report options field.For more information about setting the options, see Specifying-report-options-for-workload-comparisons.
3. Select a workload to use as a baseline by typing 0 (zero) in the action field beside the workload.
  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. (optional) Select up to nine additional workloads to compare by typing digits from 1 through 9 in the action field beside the comparison workloads.If you do not enter additional workloads, no comparison is made, but you can still view the baseline in the report.
  For information about how comparisons are made, see Comparing-results.
5. Press Enter.The next panel varies depending on your entry in the Processing mode field:
  - If you specified B (batch) in the Processing mode field, the Batch Job panel is displayed.
    Use this panel to generate, edit, and submit JCL to perform the comparison and generate the batch report. For more information about the options on the Batch Job panel, see the Batch Job options table.
    For information about the resulting batch report, see Sample-batch-report-for-Workload-Access-Path-Compare.
  - If you specified O (online) in the Processing mode field, the SQL Workload Compare Report panel is displayed.

If you generated an online report, review the report on the SQL Workload Compare Report panel. To locate specific workloads, see the tip to Step 4.c.

PSSWC140           SQL Workload Compare Report
Command ===>                                                  Scroll ===> CSR

    Actions: S T H
   Plan     Program                          Weighted Difference
   Name     Name     Queryno       Cost Diff Cost Diff In
   -------- -------- ------------- --------- --------- ------------------------
   DCIINSTL AINTEPL     139,   139     29325     29325 STATS INDEX
   DCIINSTL AINTEPL     642,   642       240       240 STATS INDEX
   DCIINSTL AINTEPL     183,   183       213       213 PATH STATS INDEX
   DCIINSTL AINEBMCL    848,   848        15        15 STATS INDEX
   DCIINSTL AINTEPL     517,   517        13        13 STATS
   DCIINSTL AINESYNO    829,   829        13        13 STATS
   DCIINSTL AINESYNO    823,   823         8         8 STATS
   DCIINSTL AINTEPL     717,   717         1         1 STATS INDEX
******************************** Bottom of Data *******************************

The following table describes how to use the report.

Action	Steps
Show detailed information for a workload	Type S next to the statement for which you want to show detailed information and press Enter. The SQL Workload Compare Report is displayed. PSSWC142 SQL Workload Compare Detail Report Command ===> Scroll ===> CSR More: > Actions: S H EXPL SRC STMT LABL SSID SSID NAME COLLID VERSION STMTNO COST ---- ---- ---- -------- ------------ -------------------------- ------ ------ XD01 DEBF DEBF AINTEPL 183 25 XD00 DEDR DEDR AINTEPL 183 4 QB PL M TB AC MT I NEW COMP P C MX LCK J P J LABL BL NO E NO TY CL TABLE INDEX O UJOG UJOG F F SQ MOD C R T This panel shows detailed information for the workload selected. Success Tip Use S or H in the action field to show or hide detailed information for a specific entry. For information about the fields in the this panel, see Workload-Access-Path-Compare-and-Index-Advisor-report-fields. To locate specific workloads, see the tip in Step 4.c.
Hide detailed information for a workload	Type H next to the entry for which you want to hide details and press Enter.
Show the SQL text for a statement	Type T next to the statement for which you want to show SQL text and press Enter.
Show information for all entries	Type S ALL on the Command line and press Enter.
Hide information for all entries	Type H ALL on the Command line and press Enter.

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.

Comparing two or more workloads

Before you begin

To compare access paths and generate reports

SQL Performance for DB2 13.1

On this page