COST: (MS) and COST: (SU) values were displayed as N/A.

Document ID : KB000123472
Last Modified Date : 22/12/2018
Show Technical Document Details
Issue:
I ran a Future Explain job. Though a PLAN_TABLE and a DSN_STATEMNT_TABLE was created automatically, the values for the "COST: (MS)" and "COST: (SU) were displayed as N/A in the "ACCESS PATH ANALYSIS" report.
In the joblog, the following messages were displayed in.
-- RSYS002.PLAN_TABLE TABLE WAS AUTO-CREATED AND FORMAT IS V12.
-- RSYS002.PLAN_TABLE_HINT_IX INDEX WAS AUTO-CREATED.
-- RSYS002.DSN_STATEMNT_TABLE TABLE WAS AUTO-CREATED AND FORMAT IS V12.
-- RSYS002.DSN_STMT_TABLE_IX INDEX WAS AUTO-CREATED.
-- RSYS002.DSN_PREDICAT_TABLE TABLE WAS AUTO-CREATED AND FORMAT IS V12. .......
.......(Omitted...)

In the "ACCESS PATH ANALYSIS" report, the following were displayed.
ACCESS PATH ANALYSIS:

COST: (MS) N/A (SU) N/A (TC) +.60730 E+01
...(Omitted...)

On the other hand, if I ran a same Future Explain job after I created a PLAN_TABLE and a DSN_STATEMNT_TABLE, it seemed that the values for the "COST: (MS)" and "COST: (SU) was displayed in the "ACCESS PATH ANALYSIS" report.
In the joblog, the following messages were displayed in.
-- RSYS002.PLAN_TABLE EXIST AND THE EXPLAIN TABLE FORMAT IS V12.
-- RSYS002.DSN_STATEMNT_TABLE EXIST AND THE EXPLAIN TABLE FORMAT IS V12.
-- RSYS002.DSN_PREDICAT_TABLE EXIST AND THE EXPLAIN TABLE FORMAT IS V12.
-- RSYS002.DSN_FILTER_TABLE EXIST AND THE EXPLAIN TABLE FORMAT IS V12.
-- RSYS002.DSN_STMT_TABLE_IX INDEX WAS AUTO-CREATED.
-- RSYS002.DSN_PREDICAT_TABLE_IDX3 INDEX WAS AUTO-CREATED.
-- RSYS002.DSN_FILTER_TABLE_IDX2 INDEX WAS AUTO-CREATED.

In the "ACCESS PATH ANALYSIS" report, the following were displayed.
ACCESS PATH ANALYSIS:

COST: (MS) 1 (SU) 11 (TC) +.60730 E+01

Why are the values for the "COST: (MS)" and "COST: (SU)  displayed as N/A in the case of the PLAN_TABLE and DSN_STATEMNT_TABLE are auto-created?
Environment:
z/OS V2.1, DB2 V12, PPADB2 V19
Cause:
This problem is caused by the missing culumn FUNCTION_LVL within a auto-created DSN_STATEMNT_TABLE.
This is related to STAR problem PPADB2 963, which is fixed in r20.
This is related to STAR problem PPADB2 1011, which is fixed in r19.



 
Resolution:
This problem can be avoided by the workaround. After a DSN_STATEMNT_TABLE is manual-created, please run a EXPLAIN job.

For R19.0,  SO06752 solves this problem. (It was created for R19.0 at Dec 22, 2018. )
For R20.0,  SO04462 solves this problem. (It was created for R19.0 at July 24, 2018. )