COST REPORT DOES NOT DISPLAY THE TOTAL COST

book

Article ID: 8415

calendar_today

Updated On:

Products

CA Bind Analyzer for DB2 for z/OS CA Detector CA SQL-Ease for DB2 for z/OS CA Sysview Performance Management Option for DB2 for z/OS CA Database Detector for DB2 for z/OS CA Plan Analyzer for DB2 for z/OS CA Subsystem Analyzer for DB2 for z/OS

Issue/Introduction

Plan Analyzer Enhanced Explain COST REPORT  doesn't display the Total Cost. The column Total Cost Has  N/A. What is wrong?.

Environment

Release:
Component: PPADB2

Resolution

The packages being explained were bound with EXPLAIN(NO) which indicates to PPA that the Plan Table information is missing. This message in the explain output indicates that PPA had to perform an EXPLAIN PACKAGE to get the access plan information. 

 >>> 

The following packages were bound with EXPLAIN(NO), or PLAN_TABLE information was not available at the time the explain was executed. An "EXPLAIN PACKAGE" statement has been executed to capture the access plan stored in the DB2 directory to obtain Plan Analyzer CURRENT Explain information. As a result, only the access path is available for reporting. The HINT_USED field in the SQL Statement Detail Report will then contain a value of "EXPLAIN PACKAGE:" . 

>>>

The EXPLAIN PACKAGE obtains the access path information from the DB2 directory where DB2 stores the information during a bind. Unfortunately, only the PLAN_TABLE is obtainable from the DB2 directory and none of the cost information is available. In order to get the cost information, the package must be bound with EXPLAIN(YES).