PM58743: 1. EXPLAIN ACCESS PATH MESSAGE for QBLOCK_TYPE PRUNED. 2. USING THE PACKAGE BIND TIME FOR A PLAN TABLE SELECT.

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: 1. Explain access path message for      *
  *                         qblock_type PRUNED.                  *
  *                         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.       *
  *                      2. Using the package bind time for a    *
  *                         plan table select.                   *
  *                         The plan table is searched by using  *
  *                         the bind time of the package.        *
  *                         The DB2 system catalog might contain *
  *                         a former package version that is     *
  *                         activated by the DB2 command         *
  *                         REBIND SWITCH.                       *
  ****************************************************************
  * RECOMMENDATION: Apply this PTF                               *
  ****************************************************************
  PROBLEM SUMMARY:
  1. Explain access path message for qblock_type PRUNED.
     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.
  2. Using the package bind time for a plan table select.
     The plan table is searched by using the bind time of the
     package. There might be multiple occurrences of the package
     with different bind times. For example, the DB2 system
     catalog might contain the latest package that is created by
     the DB2 command BIND or REBIND. It might also contain a
     former package version that is activated by the DB2 command
     REBIND SWITCH.
  
  PROBLEM CONCLUSION:
  1. The access path message for qblock_type value PRUNED is:
     THE ACCESS PATH IS PRUNED - PREDICATE IS ALWAYS FALSE
  2. The package bind time is used to select a plan table row.
  
  KEYWORDS:
  0. BATCH-EXPLAIN ISPF-OLM-EXPLAIN
  1. PLAN_TABLE QBLOCK_TYPE PRUNED PREDICATE-FALSE
  2. PLAN_TABLE-SELECT PACKAGE-BINDTIME
  

Problem conclusion

• 1. The access path message for qblock_type value PRUNED is:
     THE ACCESS PATH IS PRUNED - PREDICATE IS ALWAYS FALSE
  2. The package bind time is used to select a plan table row.
  
  KEYWORDS:
  0. BATCH-EXPLAIN ISPF-OLM-EXPLAIN
  1. PLAN_TABLE QBLOCK_TYPE PRUNED PREDICATE-FALSE
  2. PLAN_TABLE-SELECT PACKAGE-BINDTIME
  

Temporary fix

Comments

APAR Information

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

  PM49833

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

  UK76749 UK76750

Modules/Macros

• DGO@TPT1 DGO@TPT2 DGO@TPT3 DGO@TPT4 DGO@TPT5
  DGO@TPT6 DGO@TPT7 DGO@TPT8 DGO@TPT9 DGOMTBUF DGOMYEDY DGOMYEXX
  DGOMYE00 DGOMYE01 DGOMYIP0 DGOMYOPT DGOMYX00 DGOYBLOK DGOYB100
  DGOYCKPT DGOYCNDX DGOYDB2I DGOYFFML DGOYFIST DGOYFPCK DGOYFRCA
  DGOYGPCK DGOYJOIN DGOYLCEP DGOYLINE DGOYMAIN DGOYPATH DGOYPHDR
  DGOYPLAN DGOYPLA1 DGOYPLA2 DGOYPSUM DGOYRNM0 DGOYRNM1 DGOYRPT0
  DGOYRPT1 DGOYRTB0 DGOYRTC0 DGOYR100 DGOYR200 DGOYSEEE DGOYSERV
  DGOYSPCK DGOYSQMF DGOYSTAB DGOYSUMM DGOYSVRS DGOYTPT0 DGOYTPT1
  DGOYTPT2 DGOYTPT3 DGOYTPT4 DGOYTPT5 DGOYTPT6 DGOYTPT7 DGOYTPT8
  DGOYTPT9 DGOYXPCK DGOYXPC1 DGOYXPC2 DGOYXPC3 DGOYXPC4 DGOYXPC5
  DGOYXPLC FPEY09   FPEY10
  

Fix information

• Fixed component name

  OM XE DB2PE/PM

• Fixed component ID

  5655OPE00

Applicable component levels

• R51J PSY UK76750

     UP12/03/03 P F203

• R510 PSY UK76749

     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.