IBM Support

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. Even when the access path regression was triggered by a change in the DB2 maintenance level, in many cases, the underlying root cause is still inadequate statistics. The result is an unstable access path that is vulnerable to change.

Answer

The Statistics Advisor is an free optimization tool to help identify when the DB2 optimizer does not have adequate statistics to determine the most favorable 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 Service Requests, and before sending recreate documentation for existing access-path-related Service Requests.

For information on running Statistics Advisor from InfoSphere Optim Query Workload Tuner for DB2 z/OS, see:

https://www.ibm.com/support/knowledgecenter/SS7L9Q_4.1.1/com.ibm.datatools.qrytune.sngqrync.doc/topics/genrecsstats.html

To download Statistics Advisor, see: https://www.ibm.com/developerworks/downloads/im/data/

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, then run the suggested RUNSTATS jobs.
  3. Re-run Statistics Advisor after the suggested RUNSTATS have been run. When run a second time, the tool may have additional suggestions related to data correlation and skew statistics. The additional RUNSTATS suggestions are based on the 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 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 performance issue is not resolved by Statistics Advisor, send the following information to IBM Software Support:
  • The full EXPLAIN report including the results of the queries below. If appropriate, also send the EXPLAIN output for the previous good access path to be used as a baseline for comparison.
SELECT *

FROM PLAN_TABLE

WHERE QUERYNO = value

ORDER BY ORDER BY EXPLAIN_TIME, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;

SELECT *

FROM DSN_PREDICAT_TABLE

WHERE QUERYNO = value

ORDER BY ORDER BY EXPLAIN_TIME, QUERYNO, QBLOCKNO, PREDNO;

SELECT *

FROM DSN_DETCOST_TABLE

WHERE QUERYNO = value

ORDER BY EXPLAIN_TIME, QUERYNO, QBLOCKNO, PLANNO;

SELECT *

FROM DSN_FILTER_TABLE

WHERE QUERYNO = value

ORDER BY EXPLAIN_TIME, QUERYNO, QBLOCKNO, PLANNO;


  • The related DDL statements, catalog statistics, and DSNZPARMS to enable IBM Software Support to reproduce the access paths.

ADMIN_INFO_SQL:

As of DB2 10 for z/OS, the automated method for capturing this documentation is with the stored procedure ADMIN_INFO_SQL. The stored procedure is supplied with DB2, and can be called either in batch, or by using InfoSphere Optim Query Tuner's "Query Environment Capture" function. The ADMIN_INFO_SQL is the recommended approach to collect DDL statements, catalog statistics, and EXPLAIN data (referenced by IBM Software Support as Service SQL). The stored procedure standardizes the format of the documentation for problem analysis.

Option 1: Use the InfoSphere Optim Query Workload Tuner for DB2 z/OS to collect Service SQL

Document information

More support for: DB2 for z/OS
Performance

Software version: 10.0, 11.0

Operating system(s): z/OS

Reference #: 1206998

Modified date: 04 April 2017


Translate this page: