Inbound Sequencing Causes Orphan Row in B2B_DATA_STORAGE Table (Doc ID 1540698.1)

Last updated on NOVEMBER 19, 2016

Applies to:

Oracle SOA Platform - B2B (Business to Business) - Version 11.1.1.6.0 and later
Information in this document applies to any platform.

Symptoms

On : 11.1.1.4.0 version, Core B2B Platform

ACTUAL BEHAVIOR
---------------
When sequenced inbound data moves from state MSG_WAIT_TRANSMIT to MSG_COMPLETE, a new record is created in the B2B_DATA_STORAGE table, leaving the original record orphaned.

EXPECTED BEHAVIOR
-----------------------
The original record should be retain to avoid performance issues due to a large number orphaned records.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1) Go to B2B Console -> Administration -> Configuration and set Inbound Dispatcher Count to 1.
2) Create a trading partner agreement for an EDI document through the B2B Console.
3) Create a new JMS listening channel (not internal).
4) Import a JMS message (with ACTION_NAME=TARGET:InboundSeq) with an EDI message in the body that will throw a data validation error. The data should fail and keep a record in B2B_SEQUENCE_MANAGER table.
5) Now import a JMS message with the same key (ACTION_NAME=TARGET:InboundSeq) that contains a valid EDI message in the body. The record should go the MSG_WAIT_TRANSMIT state.
6) Now run the below query and get the counts
SELECT COUNT(1) FROM B2B_DATA_STORAGE;
SELECT COUNT(1) FROM B2B_APP_MESSAGE;
SELECT COUNT(1) FROM B2B_BUSINESS_MESSAGE;
SELECT COUNT(1) FROM B2B_WIRE_MESSAGE;
7) Now run the below query and save the app message details
SELECT ID, CREATED, PAYLOAD, DOC_TYPE FROM B2B_APP_MESSAGE WHERE CREATED > SYSDATE-1/24 ORDER BY CREATED DESC;
Note down the PAYLOAD. Say PAYLOAD1.
8) Now run the below query and delete the earlier sequence manager entry.
DELETE FROM B2B_SEQUENCE_MANAGER WHERE MESSAGE_ID IS <message_id>;
COMMIT;
9) Now the MSG_WAIT_TRANSMIT record should go to MSG_COMPLETE state.
10) Now rerun the count queries again. You will see an increase in B2B_DATA_STORAGE alone.
SELECT COUNT(1) FROM B2B_DATA_STORAGE;
SELECT COUNT(1) FROM B2B_APP_MESSAGE;
SELECT COUNT(1) FROM B2B_BUSINESS_MESSAGE;
SELECT COUNT(1) FROM B2B_WIRE_MESSAGE;
11) Now rerun the app message query.
SELECT ID, CREATED, PAYLOAD, DOC_TYPE FROM B2B_APP_MESSAGE WHERE CREATED > SYSDATE-1/24 ORDER BY CREATED DESC;
You can see a new PAYLOAD is linked to the same B2B_APP_MESSAGE.
Note down the PAYLOAD. Say PAYLOAD2.

Now the data storage records corresponds to the PAYLOAD1 is orphan.
SELECT * FROM B2B_DATA_STORAGE WHERE ID = 'PAYLOAD1'

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms