IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

 

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 number

    PM58743

  • Reported component name

    OM XE DB2PE/PM

  • Reported component ID

    5655OPE00

  • Reported release

    510

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-02-22

  • Closed date

    2012-03-02

  • Last modified date

    2012-04-03

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

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSAV2B","label":"IBM Db2 Buffer Pool Analyzer for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"5.1.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCT4H5","label":"IBM Tivoli OMEGAMON XE for Db2 PE \/ PM \/ BPA"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"5.1.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
03 April 2012