Troubleshooting access-path-related performance problems

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

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.

• 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
  Service SQL functionality is available in both 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.
  

  DB2 versions	Tool to use
  Version 9.1 and Version 8 (including Version 8 compatibility mode)	Use the latest version of Optimization Service center for DB2 for z/OS to capture information about a problem SQL statements and the environment of the statements. See Sending an SQL query to IBM Software Support" for more information.
  Version 7 and earlier	Use Visual Explain for DB2 for z/OS. See Capturing environment details for more information.

  
  Important recommendations:
  • 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. 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.
  • 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 DB2PLI8 program
  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 ftp.software.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

  
  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,bbbbbbbbbb,eeeeeeeeee,DDL,EDITED') where creator.PLAN_TABLE -> explained output progname           -> 1-8 character program name (% = all) bbbbbbbbbb         -> 1-10 digit beginning QUERYNO range eeeeeeeeee         -> 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.