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?
DB2 for z/os
Release: R20
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.