Troubleshooting: Customer with many shopping cart items cannot receive promotion

When a customer tries to add many items that qualify for a promotion to the shopping cart, the customer receives an error message.

Problem

When this situation occurs, the customer receives one of the following messages:

  • DB2:
    CMN3101E The system is unavailable due to "CMN3201E".
  • Oracle:
    A generic error has occurred.

On the server, the following error message appears in the SystemOut.log file at WC_profiledir/logs/server1:

  • DB2:
    E com.ibm.commerce.order.commands.PromotionEngineOrderCalculateCmdImpl invokePromotionEngine CMN3201E: 
    An unknown error occurred while the Rules System was processing..com.ibm.commerce.marketing.promotion.runtime.PromotionRuntimeException: 
    com.ibm.commerce.marketing.promotion.runtime.PromotionRuntimeException: 
    com.ibm.commerce.marketing.promotion.persistence.PersistenceObjectWriteException: 
    COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0302N  
    The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use.  
    SQLSTATE=22001
  • Oracle:
    E com.ibm.commerce.webcontroller.WebControllerHelper rollbackService CMN0409E: 
    The following error has occurred during processing: "javax.ejb.TransactionRolledbackLocalException:  ; 
    nested exception is: javax.transaction.TransactionRolledbackException: 
    Transaction is ended due to timeout".javax.ejb.TransactionRolledbackLocalException:  ; 
    nested exception is: javax.transaction.TransactionRolledbackException: 
    Transaction is ended due to timeout
    javax.transaction.TransactionRolledbackException: Transaction is ended due to timeout

This situation is rare and would typically require the customer to have hundreds of items in the shopping cart that qualify for a promotion with adjustment type of IndividualAffectedItems. For example:

  • For promotions created in Management Center, this situation can occur with "Fixed amount off each catalog entry X" promotions and "Percentage off each catalog entry X" promotions.

Reason

When the promotions engine evaluates a customer order for a promotion, it creates a promotion argument that contains all the validation results. This argument can become large when the promotion engine is applying the adjustment to each item in a shopping cart that includes many items that qualify for the promotion. The PX_PROMOARG table has a limitation for how large the serialized argument object can be.

  • For DB2 systems, if the maximum size is exceeded, the argument cannot be saved in the database, and the error condition occurs.
  • For Oracle systems, the operation to insert the large argument in the database might eventually time out, and the error condition occurs.

In either case, the customer is not able to add the large number of items to the shopping cart and receive the promotion.

Solution

For DB2, ask your database administrator to increase the maximum size of the PX_PROMOARG.DETAIL column so it can store larger promotion arguments.

For Oracle, this situation is a limitation; even with an increased size of the PX_PROMOARG.DETAIL column, the operation to insert the large argument in the database might time out.