IBM Support

JR40019: CMVC 211681 - ENHANCE THE CATEGORY LEVEL PROMOTION SQL QUERY LAUNCHED WHEN VIEWING SHOPPING CART.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • It is observed that a query to check whether there are any
    category level promotions, is getting fired for every item in
    the shopping cart, instead of executing only once when checking
    out an order. Here is an example of the SQL being executed
    multiple times:
    
    SELECT COUNT(T0.PX_PROMOTION_ID) FROM PX_PROMOTION T0, PX_GROUP
    T1, CLCDPROMO T2, CATGPCALCD T3 WHERE
    T0.PX_GROUP_ID=T1.PX_GROUP_ID AND
    T0.PX_PROMOTION_ID=T2.PX_PROMOTION_ID AND
    T2.CALCODE_ID=T3.CALCODE_ID AND T0.STOREENT_ID IN
    (('10655'),('10101')) AND T0.STATUS =1 AND T0.CDREQUIRED=0 AND
    T0.TYPE=0 AND T0.STARTDATE <= (5/16/11 6:29 PM) AND T0.ENDDATE
    >= (5/15/11 6:30 PM) AND T1.GRPNAME IN
    (('ProductLevelPromotion'),('OrderLevelPromotion'),('ShippingPro
    motion')) AND T1.STOREENT_ID IN (('10655'),('10101')) AND
    T3.STORE_ID IN (('10655'),('10101'))
    
    Since this SQL is executed multiple times, performance could be
    degraded unnecessarily.
    

Local fix

Problem summary

  • USERS AFFECTED:
    WebSphere Commerce users on v6 and v7 who use category-level
    promotions
    
    PROBLEM ABSTRACT:
    Query to check whether there are any category level promotions
    getting fired for every item in the shopping cart, instead of
    executing only once
    
    BUSINESS IMPACT:
    Performance impact when evaluating a promotion
    
    RECOMMENDATION:
    

Problem conclusion

  • APAR JR40019 resolves this issue by changing the code that
    generates this SQL query so that it would only be executed once
    per shopping cart rather than for every orderitem added.
    
    -------------------------------------------------------------
    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

    JR40019

  • Reported component name

    WC BUS DEV ED W

  • Reported component ID

    5724I3900

  • Reported release

    600

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / Installability

  • Submitted date

    2011-07-06

  • Closed date

    2011-09-01

  • Last modified date

    2011-09-01

  • 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

  • R600 PSY

       UP

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSYT2H","label":"WebSphere Commerce Developer Enterprise"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"6.0","Edition":"","Line of Business":{"code":null,"label":null}}]

Document Information

Modified date:
01 September 2011