SE49279: CMVC 213753 - PROVIDE OPTION TO REPLACE UPDATE SQL STATEMENTS FOR PX_PROMOARG TABLE WITH DELETE AND INSERT

7.0.0-WS-WCServer-FP009

APAR status

• Closed as program error.

Error description

• This APAR addresses two problems related to the PX_PROMOARG
  database table.
  
  1) With previous releases of WebSphere Commerce, there was extra
  code required by the product when managing entries in Oracle
  database tables with CLOB columns. To insert a row, first a row
  is created with an empty CLOB, then a subsequent update is made
  to set the contents into the CLOB column. To update a row, first
  the row is updated to have a empty CLOB, then a subsequent
  update is made to set the contents into the CLOB column. With
  the currently supported version of Oracle, this extra step is no
  longer required and a single insert or a single update can be
  used to manage CLOB columns.
  
  2) When there is high volume of traffic on a Commerce site, many
  locks may occur on the PX_PROMOARG database table. This can
  result in decreased site performance. If certain requirements
  are met (see below), then an alternate code path can be enabled
  that bypasses creating and updating entries in the PX_PROMOARG
  database table.
  

Local fix

Problem summary

• USERS AFFECTED:
  WebSphere Commerce 7.0 users who use Promotions and have a large
  number of concurrent shoppers, operating with an Oracle
  database.
  
  PROBLEM ABSTRACT:
  Improve performance related to create/update of the PX_PROMOARG
  database table.
  
  BUSINESS IMPACT:
  Locking in CLOBs could lead to an outage as the system waits or
  times out waiting for updates to the PX_PROMOARG table.
  
  RECOMMENDATION:
  

Problem conclusion

• This fix addresses two problems related to the PX_PROMOARG
  database table.
  
  1) For Oracle databases only, the performance related to
  managing CLOB columns is improved.
  The code for the PromotionArgumentSessionBeanPersistenceManager
  was updated to perform a single insert or a single update when
  managing entries in the PX_PROMOARG database table. This occurs
  every time the promotion engine is called to evaluate a shopping
  cart.
  The code for the
  PromotionRewardOptionSessionBeanPersistenceManager was updated
  to perform a single insert or a single update when managing
  entries in the PX_RWDOPTION database table. This occurs when
  adding and updating free gifts in a shopping cart.
  
  2) For customers who meet the following requirements:
  a) There is no custom code that relies on records in the
  PX_PROMOARG database table.
  b) The VoucherAdjustment promotion adjustment is not used. This
  adjustment can offer a new coupon to a customer when a promotion
  is applied and an order is submitted. This adjustment is not
  used out of the box in V7 and would only be used through a
  customization.
  c) V7 Feature Pack 1 or higher is installed with the foundation
  component enabled.
  
  If these requirements are met, then you can optionally enable a
  code path that will not persist records to the PX_PROMOARG
  database table. This can result in improved site performance.
  See the Additional Instructions in the APAR readme for details
  on how to enable this code path.
  
  -------------------------------------------------------------
  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 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 EDIT AS4

• Fixed component ID

  5724I3803

Applicable component levels

• R701 PSY

     UP