IBM Support

JR49826: Repeated SQL queries when using "coordinator" price rules

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When using a "coordinator" price rule, the following SQL
    statements are repeatedly queried during "add to cart" and other
     price calculation scenarios:
    
     // SELECT PRICERULE.PRICERULE_ID, PRELEMENT.PRELEMENT_ID,
    PRELEMENT.IDENTIFIER, PRELEMENT.DESCRIPTION,
     //    PRELEMENT.PRELETEMPLATE_ID, PRELEMENT.PRICERULE_ID,
    PRELEMENT.PARENT, PRELEMENT.SEQUENCE,
     //    PRELEMENT.FIELD1, PRELEMENT.FIELD2, PRELEMENT.FIELD3,
    PRELEMENT.OPTCOUNTER,
     //    PRELETEMPLATE.PRELETEMPLATE_ID, PRELETEMPLATE.IDENTIFIER,
    PRELETEMPLATE.STOREENT_ID,
     //    PRELETEMPLATE.DESCRIPTION, PRELETEMPLATE.PRELETPLTGRP_ID,
    PRELETEMPLATE.FIELD1,
     //    PRELETEMPLATE.FIELD2, PRELETEMPLATE.FIELD3,
    PRELETEMPLATE.OPTCOUNTER, PRELETEMPLATE.RUNTIMEXML,
     //    PRELETPLTGRP.PRELETPLTGRP_ID, PRELETPLTGRP.IDENTIFIER,
    PRELETPLTGRP.DESCRIPTION,
     //    PRELETPLTGRP.OPTCOUNTER FROM PRICERULE, PRELEMENT,
    PRELETEMPLATE, PRELETPLTGRP
     //    WHERE PRICERULE.PRICERULE_ID = PRELEMENT.PRICERULE_ID AND
    PRELEMENT.PRELEMENT_ID in ( ? )
     //    AND PRELETEMPLATE.PRELETEMPLATE_ID =
    PRELEMENT.PRELETEMPLATE_ID
     //    AND PRELETPLTGRP.PRELETPLTGRP_ID =
    PRELETEMPLATE.PRELETPLTGRP_ID
     //    AND PRICERULE.MARKFORDELETE = 0
     //    -- WC_DSL
    '/PriceRule[PriceRuleElement[PriceRuleElementIdentifier[(UniqueI
    D=)]]]+IBM_Admin_PriceRuleElementsDetails'
    
     // SELECT PRELEMENTATTR.PRELEMENT_ID, PRELEMENTATTR.NAME,
    PRELEMENTATTR.VALUE, PRELEMENTATTR.PROPERTIES,
     //    PRELEMENTATTR.SEQUENCE, PRELEMENTATTR.OPTCOUNTER FROM
    PRELEMENTATTR
     //    WHERE PRELEMENTATTR.PRELEMENT_ID = ?
     //    -- WC_DSL
    '/PRELEMENTATTR[PRELEMENT_ID=]+IBM_Admin_Details'
    
     // SELECT PRELEMENT.PRELEMENT_ID, PRELEMENT.IDENTIFIER,
    PRELEMENT.DESCRIPTION,
     //    PRELEMENT.PRELETEMPLATE_ID, PRELEMENT.PRICERULE_ID,
    PRELEMENT.PARENT, PRELEMENT.SEQUENCE,
     //    PRELEMENT.FIELD1, PRELEMENT.FIELD2, PRELEMENT.FIELD3,
    PRELEMENT.OPTCOUNTER,
     //    PRELETEMPLATE.PRELETEMPLATE_ID, PRELETEMPLATE.IDENTIFIER,
    PRELETEMPLATE.STOREENT_ID,
     //    PRELETEMPLATE.DESCRIPTION, PRELETEMPLATE.PRELETPLTGRP_ID,
    PRELETEMPLATE.FIELD1,
     //    PRELETEMPLATE.FIELD2, PRELETEMPLATE.FIELD3,
    PRELETEMPLATE.OPTCOUNTER,
     //    PRELETEMPLATE.RUNTIMEXML, PRELETPLTGRP.PRELETPLTGRP_ID,
    PRELETPLTGRP.IDENTIFIER,
     //    PRELETPLTGRP.DESCRIPTION, PRELETPLTGRP.OPTCOUNTER FROM
    PRELEMENT, PRELETEMPLATE,
     //    PRELETPLTGRP WHERE PRELEMENT.PRICERULE_ID in ( ? ) AND
    PRELEMENT.PARENT = ?
     //    AND PRELETEMPLATE.PRELETEMPLATE_ID =
    PRELEMENT.PRELETEMPLATE_ID
     //    AND PRELETPLTGRP.PRELETPLTGRP_ID =
    PRELETEMPLATE.PRELETPLTGRP_ID
     //    ORDER BY SEQUENCE ASC
     //    -- WC_DSL '/PriceRuleElement[PRICERULE_ID= and
    PARENT=]+IBM_Admin_Details'
    
     // SELECT PRELEMENTATTR.PRELEMENT_ID, PRELEMENTATTR.NAME,
    PRELEMENTATTR.VALUE, PRELEMENTATTR.PROPERTIES,
     //    PRELEMENTATTR.SEQUENCE, PRELEMENTATTR.OPTCOUNTER FROM
    PRELEMENTATTR
     //    WHERE PRELEMENTATTR.PRELEMENT_ID = ?
     //    -- WC_DSL
    '/PRELEMENTATTR[PRELEMENT_ID=]+IBM_Admin_Details'
    

Local fix

Problem summary

  • USERS AFFECTED:
     Websphere Commerce users on v7.0 who use coordinator price
    rules
    
     PROBLEM ABSTRACT:
     Repeated SQL queries when using "coordinator" price rules
    
     BUSINESS IMPACT:
     Performance for price rule calculation is slowed because of
    repeated queries to the database
    
     RECOMMENDATION:
    

Problem conclusion

  • The coordinator price rule implementation has been changed to
    use the object cache with JNDI name dmap/PR_Cache to avoid
    unnecessary queries to the database.
    
     -------------------------------------------------------------
     The latest available maintenance information can be obtained
    from the Recommended Fixes for WebSphere Commerce technote:
     http://www.ibm.com/support/docview.wss?rs=3046&uid=swg21261296
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR49826

  • Reported component name

    WC BUS DEV ED W

  • Reported component ID

    5724I3900

  • Reported release

    700

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-03-31

  • Closed date

    2014-09-29

  • Last modified date

    2014-09-29

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

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

Fix information

  • Fixed component name

    WC BUS DEV ED W

  • Fixed component ID

    5724I3900

Applicable component levels

  • R700 PSY

       UP

[{"Business Unit":{"code":"BU055","label":"Cognitive Applications"},"Product":{"code":"SSYT2H","label":"WebSphere Commerce Developer Enterprise"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.0","Line of Business":{"code":"LOB31","label":"WCE Watson Marketing and Commerce"}}]

Document Information

Modified date:
11 December 2021