COST REPORT DOES NOT DISPLAY THE TOTAL COST

Document ID : KB000008415
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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

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).