Where can I get a report of the data in my DB2 Plan Table in a well formatted report?

Document ID : KB000011330
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

The DB2 Plan Table is the location where application performance data from a DB2 BIND is stored during the compilation of a program. This data is used by Plan Analyzer when Enhanced Explain functions are carried out on a plan/package and provides expert analysis of this data for a users' benefit.

It is also useful for an expert DB2 user to view this data in its raw form rather than using an Enhanced Explain.

Question:

Where can I get a report of the data in my DB2 Plan Table in a well formatted report?

Environment:
Db2 for z/os
Answer:

Some reports are available in the OH Optimization Hints menu item.

A PLAN_TABLE "creator" is selected on the "PPA Optimization Hints" screen and so as long as you have authorization it is possible to view the contents of PLAN_TABLES created under other userids as well as your own.

When the correct plan table has been found the "X" line command can be used to list the contents based on your requirements or to drill down to each level using the "X" command.

These screens report on the actual contents of the current PLAN_TABLE with no analysis. The data can be viewed and printed/saved right down to the QUERYNO/QBLOCKNO level formatted as a report with headings.

At the QUERYNO level, there are also these reports :

ACC          - Short Access report 
CMPO         - Compare Access report changes

Each screen report can be saved using the QFILE command to a dataset with a record length that is long enough (500 at least) or printed with the QPRINT command to a place as per the GLOBAL PROFILE Print Parameters.

Additional Information:

Implement a PLAN_TABLE Optimization Hint