IBM Support

JR45726: CMVC 225015 - FIX THE SQLINTEGRITY VIOLATION EXCEPTION WITH THE T ATTRVALDESC AND TO IMPROVE PUBLISHING PERFORMANCE

Direct link to fix

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • \line The symptom of the problem is that with the
    approveUniqueIndexViolationAction="MergeNew" or
    "MergeNewAndUpdate" \line set in the managed resource
    configuration files, a unique index violation exception such as
    the following may still occur:\line \line [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: \line 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)\line
    com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2
     SQL Error: SQLCODE=-530, SQLSTATE=23503,
    SQLERRMC=WCS.ATTRVALDESC.F_3562, DRIVER=4.12.56\line \tab at
    com.ibm.db2.jcc.am.hd.a(hd.java:672)\line \tab at
    com.ibm.db2.jcc.am.hd.a(hd.java:60)\line \tab at
    com.ibm.db2.jcc.am.hd.a(hd.java:127)\line \line The Task Group
    will be placed in the Commit Failed state when viewed from CMC
    wokspaces tool.\line \line The problem is specific to tables
    with multiple primary keys with corresponding foreign keys to
    \line such to child tables such as ATTRDESC to ATTRVALDESC and
    ATTRIBUTE to ATTRVALUE.\line The actions to merge a unique index
     collision where both conflicting records are in a
    workspace\line is not corrected requiring manual intervention.
    This APAR will allow the existing settings to correct this\line
    situation and navigate down any child table records.\line \line
    

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

  • e 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 number

    JR45726

  • Reported component name

    WC BUS DEV ED W

  • Reported component ID

    5724I3900

  • Reported release

    700

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / Pervasive

  • Submitted date

    2013-02-13

  • Closed date

    2013-06-06

  • Last modified date

    2013-06-06

  • 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:
07 December 2021