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

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

book

Article ID: 11330

calendar_today

Updated On:

Products

Plan Analyzer for DB2 for z/OS

Issue/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.

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

Environment

DB2 for z/os

Release: R20

Resolution

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 authid's as well as your own.

When the correct plan table has been found the "X" Explode 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 

Access Path Analysis:

 Cost: (ms) N/A              (su) N/A              (tc)   0.250

 PQbkNo: 0        PPlnNo: 0     QblkNo: 1     PlanNo: 1     MxOpSq: 0
 Access: IXDATA   TSLock: IS    Prefet:       QblkTy: SEL   TBType: TABLE
 Tb#1  : authid.CARS
 CorrNm: A
 Index : authid.CARS_IX
 MCols : 1        DIRECT:
 GrpMbr: DB4G     PgeRng:       ColFnE:       WhnOpt:       PriAcc:
 Encode: EBCDIC   SCCSID: 1027  MCCSID: 5035  DCCSID: 4396  VI_ACT:

 PQbkNo: 0        PPlnNo: 0     QblkNo: 1     PlanNo: 2     MxOpSq: 0
 Access: IXONLY   TSLock: IS    Prefet:       QblkTy: SEL   TBType: TABLE
 Method: Nested Loop Join       JoinTy: Inner MergJC:
 Tb#2  : authid.CARS1
 CorrNm: B
 Index : authid.CARS_IX1
 MCols : 1        DIRECT:
 GrpMbr: DB4G     PgeRng:       ColFnE:       WhnOpt:       PriAcc:
 Encode: EBCDIC   SCCSID: 1027  MCCSID: 5035  DCCSID: 4396  VI_ACT:


CMPO         - Compare Modified Access Path

 Old: Source:                          New: Source:
      Expl Type:                            Expl Type:
      Collection: TESTPRGA                  Collection: TESTPRGA
      Package:    TESTPRGA                  Package:    TESTPRGA
      Version:    2019-11-27-23.21.+        Version:    2019-11-27-23.21.+
      Copyid:     CURRENT                   Copyid:     CURRENT
      Statement:  94        (0)             Statement:  94        (0)

Access Path Analysis:

 Cost: (ms) N/A              (su) N/A              (tc)   0.250

 PQbkNo: 0        PPlnNo: 0     QblkNo: 1     PlanNo: 1     MxOpSq: 0
 Access: IXDATA   TSLock: IS    Prefet:       QblkTy: SEL   TBType: TABLE
 Tb#1  : authid.CARS
 CorrNm: A
 Index : authid.CARS_IX
 MCols : 1        DIRECT:
 GrpMbr: DB4G     PgeRng:       ColFnE:       WhnOpt:       PriAcc:
 Encode: EBCDIC   SCCSID: 1027  MCCSID: 5035  DCCSID: 4396  VI_ACT:

 PQbkNo: 0        PPlnNo: 0     QblkNo: 1     PlanNo: 2     MxOpSq: 0
 Access: IXONLY   TSLock: IS    Prefet:       QblkTy: SEL   TBType: TABLE
 Method: Nested Loop Join       JoinTy: Inner MergJC:
 Tb#2  : authid.CARS1
 CorrNm: B
 Index : authid.CARS_IX1
 MCols : 1        DIRECT:
 GrpMbr: DB4G     PgeRng:       ColFnE:       WhnOpt:       PriAcc:
 Encode: EBCDIC   SCCSID: 1027  MCCSID: 5035  DCCSID: 4396  VI_ACT:

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

Create and Apply a PLAN_TABLE Optimization Hint