My Oracle Support Banner

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

Last updated on SEPTEMBER 04, 2018

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.