Criteria for SQL Source Cache

Document ID : KB000106255
Last Modified Date : 13/07/2018
Show Technical Document Details
Question:
We ran DBSQLPR with the following options/query...
 
AUTHID=DSG 
DATASEPARATOR=| 
INPUTWIDTH=80 
NOECHO 
NOFORMFEED 
NOTYPE 
PAGELEN=2147483647 
PRTFILE=SQLDATA 
PRTWIDTH=1500 
PRTY=3 
ROWLIMIT=999999999 
SQUISH 

SELECT CPF.CHN_CD "CHN-CD", 
PSR.HI_PDST_CD DGG, 
PSR.LO_PDST_CD PG, 
PROD.PROD_CD "PROD-CD", 
CPDEG.MIN_GRD "MIN-GRD" 
FROM DSG.PROD_SET_RLT PSR INNER JOIN DSG.PROD_MEM PM 
ON PM.CO_CD = PSR.CO_CD 
AND PM.PDST_ARA_CD = PSR.PDST_ARA_CD 
AND PM.PDST_TYP_CD = PSR.LO_PDST_TYP_CD 
AND PM.PDST_CD = PSR.LO_PDST_CD 
INNER JOIN DSG.PRODUCT PROD 
ON PROD.CO_CD = PM.CO_CD 
AND PROD.PROD_CD = PM.PROD_CD 
AND (PROD.PROD_TYP_CD = '01' OR PROD.PROD_TYP_CD = '05') 
AND (PROD.CURR_STS_CD = '02' OR PROD.CURR_STS_CD = '05' OR 
PROD.CURR_STS_CD = '06') 
INNER JOIN DSG.CHN_PROD_FCST CPF 
ON CPF.PROD_CD = PROD.PROD_CD 
AND CPF.INTRL_DST_IND = 'Y' 
INNER JOIN DSG.CNPD_EL_GRD CPDEG 
ON CPDEG.CHN_CD = CPF.CHN_CD 
AND CPDEG.PROD_CD = CPF.PROD_CD 
AND CPDEG.GRD_TYP_CD = '02' 
WHERE PSR.CO_CD = '01' 
AND PSR.PDST_ARA_CD = '01' 
AND PSR.HI_PDST_TYP_CD = '11' 
AND PSR.LO_PDST_TYP_CD = '02' 
UNION 
SELECT CPF.CHN_CD, 
PSR.HI_PDST_CD, 
PSR.LO_PDST_CD, 
PROD.PROD_CD, 
COALESCE(CPGEG.MIN_GRD,'09') 
FROM DSG.PROD_SET_RLT PSR INNER JOIN DSG.PROD_MEM PM 
ON PM.CO_CD = PSR.CO_CD 
AND PM.PDST_ARA_CD = PSR.PDST_ARA_CD 
AND PM.PDST_TYP_CD = PSR.LO_PDST_TYP_CD 
AND PM.PDST_CD = PSR.LO_PDST_CD 
INNER JOIN DSG.PRODUCT PROD 
ON PROD.CO_CD = PM.CO_CD 
AND PROD.PROD_CD = PM.PROD_CD 
AND (PROD.PROD_TYP_CD = '01' OR PROD.PROD_TYP_CD = '05') 
AND (PROD.CURR_STS_CD = '02' OR PROD.CURR_STS_CD = '05' OR 
PROD.CURR_STS_CD = '06') 
INNER JOIN DSG.CHN_PROD_FCST CPF 
ON CPF.PROD_CD = PROD.PROD_CD 
AND CPF.INTRL_DST_IND = 'Y' 
AND NOT EXISTS(SELECT CHN_CD FROM DSG.CNPD_EL_GRD CPDEG 
WHERE CPDEG.CHN_CD = CPF.CHN_CD 
AND CPDEG.PROD_CD = CPF.PROD_CD 
AND CPDEG.GRD_TYP_CD = '02') 
LEFT JOIN DSG.CNPG_EL_GRD CPGEG 
ON CPGEG.CHN_CD = CPF.CHN_CD 
AND CPGEG.PLGP_CD = PM.PDST_CD 
AND CPGEG.GRD_TYP_CD = '02' 
WHERE PSR.CO_CD = '01' 
AND PSR.PDST_ARA_CD = '01' 
AND PSR.HI_PDST_TYP_CD = '11' 
AND PSR.LO_PDST_TYP_CD = '02' 
QUERYNO 10002; 

The query was not not accepted for Source Cache. Please can you explain why? The documentation does not provide sufficient explanation of the source cache selection/rejection criteria. 
Environment:
z/OS 
CA Datacom/SQL 15.1  
Answer:
It's the COALESCE predicate that's making this query ineligible for the Source Cache. 
Additional Information:
Our Online documentation will be updated soon with:    
 
Queries not cached
Queries with the following syntax are not cached:
  1. Special Registers SPLREG_DCM_TSN and SPLREG_DCM_MUF_NAME
  2. Labeled Durations
  3. predicates comparing between UNSIGNED NUMERIC and CHAR data types
  4. Expressions containing only literals. Examples:
  5. column1 = 3 * 4
  6. coalesce(column1, '09')  ← Each operand is an expression, so '09' is a literal-only expression
  7. column1 LIKE 'XXX%' ← converted to column1 = 'XXX'
  8. column1_length_3 = '1234' ← special processing used when the literal is longer than the column

 For more information on SQL SOURCE CACHE see https://docops.ca.com/ca-datacom/15-1/en/getting-started/getting-started-with-sql-in-ca-datacom/sql-source-cache