Problem

-- Sequenced messages stuck in process status after applying B2Bi 2.1.0_SP3

-- Caused by: <openjpa-0.0.0-rnull fatal store error> org.apache.openjpa.persistence.EntityExistsException: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=2;BBINTER.SEQUENCEIDS, DRIVER=4.17.29 {prepstmnt 529472387 INSERT INTO SequenceIds (OID, AllocationTime, IsCompleted, MessageOID, SeqID, SeqNumber) VALUES (?, ?, ?, ?, ?, ?) [params=?, ?, ?, ?, ?, ?]} [code=-803, state=23505]SQLCA OUTPUT[Errp=SQLDMISR, Errd=-2146893819, 5, 0, 0, -1246, 0]
DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=2;BBINTER.SEQUENCEIDS, DRIVER=4.17.29
FailedObject: com.cyclonecommerce.tradingengine.transport.system.production.sequencer.SequenceId@49baa019
at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4856)
at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4831)
at org.apache.openjpa.jdbc.sql.DB2Dictionary.newStoreException(DB2Dictionary.java:593)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:78)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:143)
at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:79)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:99)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:87)
at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:550)
at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:106)
at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:59)
at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:103)
at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:76)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:742)
at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:131)
at org.apache.openjpa.datacache.DataCacheStoreManager.flush(DataCacheStoreManager.java:668)
at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:131)
... 24 more
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=2;BBINTER.SEQUENCEIDS, DRIVER=4.17.29 {prepstmnt 529472387 INSERT INTO SequenceIds (OID, AllocationTime, IsCompleted, MessageOID, SeqID, SeqNumber) VALUES (?, ?, ?, ?, ?, ?) [params=?, ?, ?, ?, ?, ?]} [code=-803, state=23505]
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:281)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:257)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$1000(LoggingConnectionDecorator.java:72)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:1199)
at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:291)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1774)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:267)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:118)
... 36 more

Resolution

* This is a DB2 specific issue that should be fixed in B2Bi 2.1.0_SP4. While native fix is not available a manual workaround exists:

delete and recreate this index in order to remove the uniqueness constraint:
<Index name="IX_SequenceIds_02" unique="false">
<Column name="MessageOID" data_type="BIGINT" column_size="19" decimals="0" nullable="false" ordinal_position="4"/>
</Index>