Troubleshooting access-path-related performance problems

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.

You can download free optimization tools such as Data Studio, Optimization Service Center for DB2 for z/OS, and Visual Explain for DB2 for z/OS which provide 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 more information about using the statistics advisor function, see the the following IBM Redbooks:

• DB2 9 for z/OS: New Tools for Query Optimization Chapter 9
• DB2 UDB for z/OS Version 8 Performance Topics, SG24-6465 Section 3.15.3

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 statics 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. You have two 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, Optimization Service Center for DB2 for z/OS and Visual Explain for DB2 for z/OS. The Service SQL function is much easier than using DB2PLI8 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 10, DB2 9, and Version 8
  Optimization Service Center See "Sending an SQL query to IBM Software Support" for more information.	DB2 9 and Version 8
  Visual Explain See "Capturing environment details" for more information.	Version 8 and earlier

  
  Important recommendations:
  • Do not use Visual Explain for DB2 for z/OS to capture service SQL information for DB2 9 for z/OS because it does not collect complete version 9 statistics.
  • For SQL queries that run on DB2 9 for z/OS, specify "Version 9 new-function mode" for the Convert to Version option to ensure that complete and valid version 9 statistics are included with the service SQL information.
  • 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.
  • For DB2 10 for z/OS, use version 2.2.1 or newer of either Data Studio or Optim Query Tuner to collect complete documents.
  
  Option 2: Use the DB2PLI8 program (for DB2 Version 8 and DB2 9)
  DB2PLI8 is program that you can use to gather the DDL and statistics. DB2PLI8 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 '81N' for Version 8 new-function mode '8MIG' for Version 8 compatibility mode '81' for Version 7 and earlier

  
  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.
  
  Option 3: Use the built in ADMIN_INFO_SQL stored procedure (for DB2 10 and DB2 9)
  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 available for DB2 10 for z/OS and DB2 9 for z/OS.
  
  In order to use this option, please ensure PM39871 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