Access Path Performance Issues
Problem
A high percentage of access path performance issues and access path performance regressions can be resolved simply by making sure that you have a good, 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 DB2 maintenance level, the underlying root cause is still very often inadequate statistics, which result in an unstable access path that is vulnerable to change. For DB2® for z/OS® Version 8, IBM® provides a free tool called Statistics Advisor as part of the free IBM DB2 Universal Database™ for z/OS Visual Explain Version 8 product. This tool is designed to help users identify areas in which they may not have adequate statistics to enable the optimizer to make the best decisions when determining optimal access paths.
With the availability of Statistics Advisor, we are strongly suggesting to all DB2 Universal Database for z/OS Version 8 customers that they run Statistics Advisor (SA) prior to opening Version 8 access path-related PMRs or prior to sending recreate doc for Version 8 access path-related PMRs that have already been opened.
Solution
IBM DB2 Universal Database for z/OS Visual Explain Version 8 is available at http://www.ibm.com/software/data/db2/zos/osc/ve/index.html
See also, section "3.15.3 Statistics Advisor" in the IBM Redbook,
DB2 UDB for z/OS Version 8 Performance Topics, SG24-6465-00 This PDF can be downloaded from http://www.redbooks.ibm.com/abstracts/sg246465.html
To access SA in Visual Explain, select TUNE SQL from the Tools menu. Enter the problem SQL and hit the Analyze button to run SA. If SA returns RUNSTATS suggestions, run the suggested RUNSTATS and then re-run SA (by using the Analyze button) once more 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 now be suggested as a result of the statistics gathered in the first iteration. If SA makes additional RUNSTATS suggestions, run these as well.
Then remeasure the problem SQL. If the performance is still an issue, send the following set of documents for our access path recreates and cost analysis.
See informational APAR II11945 for file transferring instructions.
Apply V8 RUNSTATS APAR PK03469 for non-uniform distribution statistic issues. Ensure that this APAR is applied if you use any Version 8 COLGROUP stats.
Provide IBM Software Support with the following information:
Full EXPLAIN report including the query like the one below. If appropriate, send the explain output for the previous good access path as well to be used as a baseline for comparison.
EXPLAIN PLAN SET QUERYNO = value FOR
problem SQL goes here;
SELECT *
FROM PLAN_TABLE
WHERE QUERYNO = value
ORDER BY TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;The related DDL and catalog statistics so that we can re-create the access paths. You have two options for collecting the related DDL and catalog statistics for your
query:
Option 1: The recommended method is to use DB2 Visual Explain Version 8 for z/OS. The Service SQL function works for DB2 Version 6, DB2 Version 7, and
DB2 Version 8 and is much easier than DB2PLI8. This method is very easy if you already have DB2 Connect set up on a client at your shop. For more information about Visual Explain, see http://www.ibm.com/software/data/db2/zos/osc/ve/index.html
>From the main menu, select Tools -> Service SQL. Enter the ETR or PMR
number and use SQL statement as input. You can also manually add any views to the list. The tool generates appropriate DDL, STATS, and ZPARM information. Then click Send Files to FTP the files to IBM.
Option 2: Use DB2PLI8 to gather the DDL and stats. PLI8 output should be tersed before sending.
DB2PLI8 is an in-house program that isn't shipped with DB2. Two sets of
DB2PLI8 files are on our FTP site:
Site: testcase.software.ibm.com
Directory: s390/fromibm/db2
File DB2PLI8 JCL81 / DB2PLI8 LKED81 supports 5.1, 6.1, 7.1, and 8.1 in compatibility mode. 8.1 new-function mode requires file DB2PLI8 JCL81N /
DB2PLI8 LKED81N which is not downward compatible.
The DB2PLI8 file contains instructions for installation and execution. You receive bind warnings, but that is expected and OK.
They are both BINARY and NOT tersed and their DCB attributes are:
DCB=(LRECL=80,BLKSIZE=32720,RECFM=FB) Please download them as described in INFO APAR II11945: DB2 FTP INSTRUCTIONS for transmitting documentation to
DB2:
File Name Description
-------------- ---------------
DB2PLI8.JCL81 : JCL to run DB2PLI8 with documentation (how to)
DB2PLI8.LKED81 : JCL to install DB2PLI8
with DBRM and object module included
In order to generate DDL and DB2 statistics, please run DB2PLI8 program.
Please use 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
---------------------------------------------------------------
and create DD cards for
SQLPUN and DDLPUN with DCB attributes:
DCB=(LRECL=80,BLKSIZE=32720,RECFM=FB)
Please send the tersed files to our FTP site and update the PMR when they are available.
P.S. Please pass my address to anyone interested in DB2 HOTLINE - thank you.
With kind regards
Michael Dewert, Software Group EMEA DB2 SWAT TEAM MEMBER
