Troubleshooting access-path-related performance problems

Technote (FAQ)


Question

How can I troubleshoot access-path-related performance problems?

Cause

A high percentage of access path performance issues and access path performance regressions can be resolved simply by making sure that you have a complete, current, and accurate set of statistics from the RUNSTATS utility to support the query or queries in question. Even in cases where the access path regression was triggered by a change in the DB2 maintenance level, the underlying root cause is still inadequate statistics in many cases. The result is an unstable access path that is vulnerable to change.

Answer

You can download free optimization tool Data Studio which provides a statistics advisor.

The statistics advisor is designed to help you identify when DB2 does not have adequate statistics to enable the optimizer to determine the optimal access path. With the availability of the statistics advisor, all DB2 for z/OS customers should run statistics advisor before contacting IBM Software Support to open access-path-related PMRs, and before sending recreate documentation for existing access-path-related PMRs.

For information on running statistics advisor in Data Studio or Optim Query Tuner, see "Running analysis tools and advisors on single SQL statements".

Tuning your queries with statistics advisor
To improve the access paths selected by DB2:

  1. Run the statistics advisor.
  2. If statistics advisor returns RUNSTATS suggestions, run the suggested RUNSTATS jobs
  3. Re-run statistics advisor again after the suggested RUNSTATS have been run. The tool might have additional suggestions related to data correlation and skew statistics the second time through that can be suggested as a result of the statistics gathered in the first iteration.
  4. If statistics advisor makes additional RUNSTATS suggestions, run these jobs too.
  5. Measure the performance of the problem SQL statement again. If the performance is still problematic, send information, as described below, about the query and how to recreate the DB2 environment to IBM Software Support. See INFO APAR II11945 for detailed information about how to transmit the information to IBM Software Support.

Information to send to IBM Software Support
If your access path problems are not resolved by statistics advisor, send the following information to IBM Software Support:
  • The full EXPLAIN report including the results of the query below. If appropriate, also send the EXPLAIN output for the previous good access path to be used as a baseline for comparison.
    EXPLAIN PLAN SET QUERYNO = value FOR

    {problem SQL  statement;}

    SELECT *
    FROM PLAN_TABLE
    WHERE QUERYNO = value
    ORDER BY TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;
  • The related DDL statements and catalog statistics to enable IBM Software Support to re-create the access paths. As of DB2 10 for z/OS, the only supported method for capturing these docs is the stored procedure ADMIN_INFO_SQL shipped with DB2. Data Studio and Optim Query Tuner's "Query Environment Capture" function call this stored procedure under the covers. You have several options for collecting the related DDL statements and catalog statistics for your query:

    Option 1: Use the Service SQL function (for all versions of DB2)
    Service SQL functionality is available in Data Studio, Optim Query Tuner, and Optimization Service Center for DB2 for z/OS. The Service SQL function is an easy option if you already have DB2 Connect set up on a client at your shop.
    Tool Supported DB2 Versions
    Data Studio or Optim Query Tuner
    See "Query Environment Capture" for more information.
    DB2 11, DB2 10, and DB2 9
    Optimization Service Center
    See "Sending an SQL query to IBM Software Support" for more information.
    DB2 9

    Important recommendations:
    • Use version 4.1 or newer of either Data Studio or Optim Query Tuner to collect complete documents.
    • Always specify the Parallelism option when you capture service SQL information, even if your system does not use parallelism. The Parallelism option ensures that important additional statistics are included in the service SQL information.
    Option 2: Use the built in ADMIN_INFO_SQL stored procedure directly
    The ADMIN_INFO_SQL stored procedure captures statistics and DDL about a DB2 subsystem and returns the results in a data set or as a result set. ADMIN_INFO_SQL, calling program DSNADMSB, and sample JCL job DSNTEJ6I are shipped with DB2.

    In order to use this option, please ensure PM73555 is applied.

    To execute ADMIN_INFO_SQL using built in program DSNADMSB, prepare a job for running DSNADMSB. The easiest way to do that is to customize a copy of sample job DSNTEJ6I, which is in data set prefix.SDSNSAMP. The job prologue has detailed instructions on how to customize the job. DSNTEJ6I calls DSNADMSB which executes SYSPROC.ADMIN_INFO_SQL.

    See "DSNADMSB" for more information including usage and input parameters.

    Detailed information on stored procedure ADMIN_INFO_SQL

    Option 3: Use the DB2PLI8 program (for DB2 9 only)
    DB2PLI8 is program that you can use to gather the DDL and statistics. DB2PLI8 only supports DB2 9 and is not shipped with DB2 for z/OS. However, you can download the DB2PLI8 files.
               
    To generate DDL statements and DB2 statistics:
    1. Download the files from public.dhe.ibm.com/s390/db2/db2per/. Refer to the following table to determine which files to download for your version of DB2 for z/OS.

    The following table describes the files for each version.
    File name Description
    DB2PLI8.JCLxx JCL to run DB2PLI8 with instructions for installation and execution. Bind warnings are expected and OK.
    DB2PLI8.LKEDxx JCL to install DB2PLI8 with DBRM and object module included
    Where xx is the suffix for the version of DB2:
    • '91N' for Version 9.1 new-function mode
    • '91' for Version 9.1 compatibility mode

    Each of these files provide a different mechanism to obtain the same documents. Installations without a PL/I compiler should use the LKEDxx file to generate DB2PLI8 documents.

    The two files for each version are BINARY and not tersed. Their DCB attributes are:
    DCB=(LRECL=80,BLKSIZE=32720,RECFM=FB)


    2. Run the DB2PLI8 program. and specify the EDITED keyword.

    ------------------------------------------------------------
    RUN  PROGRAM DB2PLI8  PLAN DB2PLI8  -
    LIB('DSNXXX.RUNLIB.LOAD') -
    PARM('/creator.PLAN_TABLE,progname,bbbbb,eeeee,DDL,EDITED')
    where
    creator.PLAN_TABLE -> explained output
    progname           -> 1-8 character program name (% = all)
    bbbbb              -> 1-10 digit beginning QUERYNO range
    eeeee              -> 1-10 digit ending QUERYNO range
    DDL                -> DDL information for creating objects
    EDITED             -> output is edited for DB2level 2 use
    ------------------------------------------------------------

    3. Terse the files, upload them to FTP site, and update the PMR when the files are available, as described in INFO APAR II11945.

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS
Performance

Software version:

9.0, 10.0, 11.0

Operating system(s):

z/OS

Reference #:

1206998

Modified date:

2014-03-28

Translate my page

Machine Translation

Content navigation