JR50002: There is SqlIntegrityConstraintViolationException with the table ATTRVALDESC when approving a Websphere Commerce task group.

7.0.0.9 for Developer Express
7.0.0.9 for Developer Professional
7.0.0.9 for Developer Enterprise
7.0.0-WS-WCServer-FP009

APAR status

• Closed as program error.

Error description

• The symptom of the problem is that with the
  approveUniqueIndexViolationAction="MergeNew" or
  "MergeNewAndUpdate"
  set in the managed resource configuration files, a unique index
  violation exception such as the following may still occur:
  
  [11/9/12 14:01:49:667 EST] 0000022d CommerceSrvr  E
  com.ibm.commerce.context.content.objects.schema.AbstractContentR
  esourceSchemaImpl
  commitManagedResourceDataSQL99Standard(Connection connection,
  ContentData contentData, ManagedResourceMetaData
  managedResourceMetaData) CMN0001E: The database returned the
  following error code:
  MERGE INTO WCS.ATTRVALDESC DEST USING (SELECT ATTRVAL_ID,
  LANGUAGE_ID, ATTR_ID, VALUE, VALUSAGE, SEQUENCE, STRINGVALUE,
  INTEGERVALUE, FLOATVALUE, QTYUNIT_ID, IMAGE1, IMAGE2, FIELD1,
  FIELD2, FIELD3, OPTCOUNTER FROM WCW102.ATTRVALDESC WHERE
  CONTENT_STATUS <> 'D' AND (( (CONTENT_TASKGRP = 'G_15004'))))
  SRC ON (SRC.ATTRVAL_ID = DEST.ATTRVAL_ID AND SRC.LANGUAGE_ID =
  DEST.LANGUAGE_ID) WHEN MATCHED THEN UPDATE SET DEST.ATTR_ID =
  SRC.ATTR_ID, DEST.VALUE = SRC.VALUE, DEST.VALUSAGE =
  SRC.VALUSAGE, DEST.SEQUENCE = SRC.SEQUENCE, DEST.STRINGVALUE =
  SRC.STRINGVALUE, DEST.INTEGERVALUE = SRC.INTEGERVALUE,
  DEST.FLOATVALUE = SRC.FLOATVALUE, DEST.QTYUNIT_ID =
  SRC.QTYUNIT_ID, DEST.IMAGE1 = SRC.IMAGE1, DEST.IMAGE2 =
  SRC.IMAGE2, DEST.FIELD1 = SRC.FIELD1, DEST.FIELD2 = SRC.FIELD2,
  DEST.FIELD3 = SRC.FIELD3, DEST.OPTCOUNTER = SRC.OPTCOUNTER WHEN
  NOT MATCHED THEN INSERT (DEST.ATTRVAL_ID, DEST.LANGUAGE_ID,
  DEST.ATTR_ID, DEST.VALUE, DEST.VALUSAGE, DEST.SEQUENCE,
  DEST.STRINGVALUE, DEST.INTEGERVALUE, DEST.FLOATVALUE,
  DEST.QTYUNIT_ID, DEST.IMAGE1, DEST.IMAGE2, DEST.FIELD1,
  DEST.FIELD2, DEST.FIELD3, DEST.OPTCOUNTER) VALUES
  (SRC.ATTRVAL_ID, SRC.LANGUAGE_ID, SRC.ATTR_ID, SRC.VALUE,
  SRC.VALUSAGE, SRC.SEQUENCE, SRC.STRINGVALUE, SRC.INTEGERVALUE,
  SRC.FLOATVALUE, SRC.QTYUNIT_ID, SRC.IMAGE1, SRC.IMAGE2,
  SRC.FIELD1, SRC.FIELD2, SRC.FIELD3, SRC.OPTCOUNTER)
  com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2
  SQL Error: SQLCODE=-530, SQLSTATE=23503,
  SQLERRMC=WCS.ATTRVALDESC.F_3562, DRIVER=4.12.56
   at com.ibm.db2.jcc.am.hd.a(hd.java:672)
   at com.ibm.db2.jcc.am.hd.a(hd.java:60)
   at com.ibm.db2.jcc.am.hd.a(hd.java:127)
  
  The Task Group will be placed in the Commit Failed state when
  viewed from CMC wokspaces tool.
  
  The problem is specific to tables with multiple primary keys
  with corresponding foreign keys to
  such to child tables such as ATTRDESC to ATTRVALDESC and
  ATTRIBUTE to ATTRVALUE.
  The actions to merge a unique index collision where both
  conflicting records are in a workspace
  is not corrected requiring manual intervention.  This APAR will
  allow the existing settings to correct this
  situation and navigate down any child table records.
  

Local fix

Problem summary

• USERS AFFECTED:
  Websphere commerce users  on v7.0 who use Workspace taskgroup
  approval process
  
  PROBLEM ABSTRACT:
  There is SqlIntegrityConstraintViolationException with the table
  ATTRVALDESC when approving a Websphere Commerce task group.
  
  BUSINESS IMPACT:
  Unable to publish taskgroup data
  
  RECOMMENDATION:
  

Problem conclusion

• A code fix has been added to APAR JR45726  to add the logic to
  merge/delete with cascade through all child tables
  with multiple primary keys.
  
  In addition, performance enhancements were made that will bypass
  checks with foreign key references to
  tables that are not changed by business users and have
  bootstrapped data (such as LANGUAGE or CURRENCY)
  to reduce number of queries.
  
  -------------------------------------------------------------
  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 EDITION

• Fixed component ID

  5724I3800

Applicable component levels

• R700 PSY

     UP