IBM Support

IT36298: EDI_RECONCILE997.ERR BECAUSE OF DEADLOCKS ON THE CORRELATION_SET TABLE WITH MSSQL

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • When process multiple inbound 997 interchanges from the same
    Trading Partner at the same time the customer continually
    receives MSSQL Deadlock errors on the CORRELATION_SET table.
    Therefore causing EDI Reconciliation errors and outbound
    documents are not successfully acknowledgedWhen process
    multiple inbound 997 interchanges from the same Trading Partner
    at the same time the customer continually receives MSSQL
    Deadlock errors on the CORRELATION_SET table. Therefore causing
    EDI Reconciliation errors and outbound documents are not
    successfully acknowledgedThe deadlock is happening when more
    then one of the following update queries run:Update
    CORRELATION_SET set VALUE=@P0 , REC_TIME=@P1, VALUE_UPPER=@P2
    where OBJECT_ID=@P3 and NAME=@P4 and TYPE=@P5
    MSSQL Deadlock
    Reports have been uploaded to the case.
    The customer is able to
    duplicate this deadlock scenario on both their Production and
    Test system.I have also been able to duplicate this on one of
    our in house installs of B2Bi and MSSQL
    At first the customers
    deadlocks were on the SCI_IDX_49 index which is a non unique,
    non clustered index. Therefore we disabled the SCI_IDX_49 index
    but now they are seeing the deadlock on the SCI_PK_133 index
    which is the clustered/unique PK
    Customer and I are seeing the
    same errors so below I have pasted the errors from my in house
    testing:
    The EDI Reconcile Report has the following
    error:
    Group Level Acknowledgement Reconciliation
    Details:Sender ID = PETTESTReceiver ID = PETTESTFunctional ID
    Code = INControl Number = 4Status = AERROR: Exception while
    reconciling group.
    
    The EDI.log and System.log have the
    following error:
    [2021-03-17 13:17:01.906] ERROR 000510150006
    EDI.EDI_RECONCILE997.ERR_Reconcile997_exception4 [Reconcile997]
    Exception while reconciling group [2021-03-17 13:17:01.906]
    ERROR [1616001421906] Transaction (Process ID 59) was
    deadlocked on lock resources with another process and has been
    chosen as the deadlock victim. Rerun the
    transaction.[2021-03-17 13:17:01.907] ERRORDTL
    [1616001421906]com.microsoft.sqlserver.jdbc.SQLServerException:
    Transaction (Process ID 59) was deadlocked on lock resources
    with another process and has been chosen as the deadlock
    victim. Rerun the transaction. at com.microsoft.sqlserver.jdbc.S
    QLServerException.makeFromDatabaseError(SQLServerException.java:
    259) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextR
    esult(SQLServerStatement.java:1547) at com.microsoft.sqlserver.j
    dbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLSer
    verPreparedStatement.java:548) at com.microsoft.sqlserver.jdbc.S
    QLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPre
    paredStatement.java:479) at com.microsoft.sqlserver.jdbc.TDSComm
    and.execute(IOBuffer.java:7347) at com.microsoft.sqlserver.jdbc.
    SQLServerConnection.executeCommand(SQLServerConnection.java:2713
    ) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeComm
    and(SQLServerStatement.java:224) at com.microsoft.sqlserver.jdbc
    .SQLServerStatement.executeStatement(SQLServerStatement.java:204
    ) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.exe
    cuteUpdate(SQLServerPreparedStatement.java:426) at com.sterlingc
    ommerce.woodstock.workflow.CorrelationImpl.updateValue(Correlati
    onImpl.java:3117) at
    

Local fix

  • STRRTC - B2BISFG-21315
    OAQ / OAQ
    Circumvention : None
    

Problem summary

  • Problem Description:
    
    
    
    When processing multiple inbound 997 interchanges from the same
    Trading Partner at the same time, the customer continually
    receives MSSQL Deadlock errors on the CORRELATION_SET table.
    This causes EDI Reconciliation errors and outbound documents are
    not successfully acknowledged.
    
    
    
    The deadlock occurs when more than one of the following update
    queries run:
    
    
    
    Update CORRELATION_SET set VALUE=@P0 , REC_TIME=@P1,
    VALUE_UPPER=@P2 where OBJECT_ID=@P3 and NAME=@P4 and TYPE=@P5
    
    
    
    Platforms Affected:
    
    All Platforms
    
    
    
    Users Affected:
    
    All customers using B2BI using standards
    

Problem conclusion

  •     Resolution Summary:
    
    A code fix is provided.
    
    When getting a deadloack error while updating a column in a
    database table, B2BI will retry the update for configurable
    number of times, default is 100 times, before it conclude the
    deadlock is persistent and an error should be written out.
    
    
    
    Delivered in:
    
    6000305
    
    
    
    All future Fix Packs can be found in the
    
    https://www.ibm.com/support/pages/node/6194265.
    

Temporary fix

  • No workaround
    

Comments

APAR Information

  • APAR number

    IT36298

  • Reported component name

    STR B2B INTEGRA

  • Reported component ID

    5725D0600

  • Reported release

    603

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2021-03-19

  • Closed date

    2021-10-05

  • Last modified date

    2021-10-22

  • 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

    STR B2B INTEGRA

  • Fixed component ID

    5725D0600

Applicable component levels

[{"Line of Business":{"code":"LOB02","label":"AI Applications"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS3JSW","label":"Sterling B2B Integrator"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"603"}]

Document Information

Modified date:
23 October 2021