PM49833: EXPLAIN ACCESS PATH MESSAGE IS INVALID IF THE PLAN TABLE QBLOCK_TYPE VALUE IS PRUNED.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• v420 customer running batch EXPLAIN gets error message:
  .
  THE OPERATION IS INSERT, UPDATE, OR DELETE WITHOUT USE OF INDEX
  PAGE RANGE SCAN WILL NOT BE USED
  .
  Customer is using the following EXPLAIN statement:
  .
  EXPLAIN SQLSTATEMENT
  (
  SELECT
   AGENT.AGCY_ID    AS AGENCY_ID,
   AGENT.PROD_CD    AS PRODUCER_CODE,
   POLICY.POLICY_NR AS POLICY_NUMBER,
   POLICY.OWNR_NAME AS OWNER_NAME,
   VALUE( VALUE(AGT_POS_NR/AGT_POS_NR ,0) * POLICY.LOAN_DUE_AM,0)
                   AS PREMIUM,
   POLICY.SYST_ID   AS LOB,
  'LOB_NAME'       AS LOB_NAME
  .
   FROM DB2PADM.VUL_AGENT_UCV AGENT
     ,DB2PADM.VUL_WEB_SEARCH_UCV POLICY
  .
   WHERE POLICY.POLICY_NR   = AGENT.POL_NR
      AND POLICY.POL_STATUS  = 0
      AND POLICY.SYST_ID    IN ('LCS','CYB','SPL','BRK'
                   'GDI','BDI','MDI','LTC')
     AND DATE(POLICY.ISS_DT) BETWEEN '2000-01-01' AND '2011-07-01'
  .
     AND ((               'P' =  'A'
        AND AGENT.PROD_CD  IN ('1053401')
       )
  .
  .
     ORDER BY PREMIUM DESC
     WITH UR
     ;
     ) SSID(DB2P) SQLID(DB2PADM) LEVEL(DETAIL)
     EXEC
  .
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: OMEGAMON XE for DB2 PE/DB2PM                 *
  *                 users of component                           *
  *                 - Explain                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: For component Batch-Explain and         *
  *                      ISPF-OLM-Explain the Explain access     *
  *                      path message is invalid if the plan     *
  *                      table qblock_type value is PRUNED.      *
  *                      This occurs if the predicate of an      *
  *                      SQL statement is always FALSE.          *
  ****************************************************************
  * RECOMMENDATION: Apply this PTF                               *
  ****************************************************************
  PROBLEM SUMMARY:
  For component Batch-Explain and ISPF-OLM-Explain
  the Explain access path message is invalid
  if the plan table qblock_type value is PRUNED.
  This occurs if the predicate of an SQL statement
  is always FALSE.
  
  PROBLEM CONCLUSION:
  The access path message for qblock_type value PRUNED is:
  THE ACCESS PATH IS PRUNED - PREDICATE IS ALWAYS FALSE
  
  KEYWORDS:
  BATCH-EXPLAIN ISPF-OLM-EXPLAIN
  PLAN_TABLE QBLOCK_TYPE PRUNED PREDICATE-FALSE
  

Problem conclusion

• The access path message for qblock_type value PRUNED is:
  THE ACCESS PATH IS PRUNED - PREDICATE IS ALWAYS FALSE
  
  KEYWORDS:
  BATCH-EXPLAIN ISPF-OLM-EXPLAIN
  PLAN_TABLE QBLOCK_TYPE PRUNED PREDICATE-FALSE
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  PM58743 UK76747 UK76748

Modules/Macros

• DGO@BLOK DGO@CNDX DGO@MAIN DGO@XPCK DGO@YE01
  DGO@YX00 DGOMYE01 DGOMYX00 DGOYBLOK DGOYCNDX DGOYJOIN DGOYMAIN
  DGOYPSUM DGOYSTAB DGOYSUMM DGOYSVRS DGOYXPCK FPEY09   FPEY10
  

Fix information

• Fixed component name

  OM XE DB2PE/PM

• Fixed component ID

  5655OPE00

Applicable component levels

• R42J PSY UK76748

     UP12/03/03 P F203

• R420 PSY UK76747

     UP12/03/03 P F203

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.