B2B_DATA_STORAGE Table Grows. Orphaning Records after Resubmit of Wire Message
(Doc ID 1437223.1)
Last updated on JULY 01, 2018
Applies to:Oracle SOA Suite - Version 220.127.116.11.0 and later
Information in this document applies to any platform.
After resubmitting several B2B wire messages through the B2B console, the B2B_DATA_STORAGE table keeps growing even though purge is run on a regular bases. Following are the steps to verify the issue:
1) For simplicity, lets assume that the following B2B tables are empty:
2) Send a B2B inbound message through your system. Verify that the inbound message was delivered successfully.
3) Run the following sql queries on the DB soainfra schema:
Query 1: select count(*) from b2b_wire_message;
Query 2: select count(*) from b2b_business_message;
Query 3: select count(*) from b2b_app_message;
Query 4: select count(*) from b2b_data_storage;
4) The results of the queries should be:
Query 1: 1 record
Query 2: 1 record
Query 3: 1 record
Query 4: 4 records
For the same inbound message, B2B inserts 4 records in the B2B_DATA_STORAGE table. The ID column on each of these records will have the value that correspond to one of the following mappings:
5) Resubmit the inbound wire message. Verify that it was processed successfully.
6) Run the same sql queries mentioned in step 3)
7) The results should be as follows:
Query 1: 1 record
Query 2: 2 records
Query 3: 2 records
Query 4: 7 records
After the wire message is resubmitted, B2B does the following:
- Creates a new business message and application message, that is why the record count on the B2B_BUSINESS_MESSAGE and B2B_APP_MESSAGE tables increased by 1.
- Updates the PAYLOAD_STORAGE column in the B2B_WIRE_MESSAGE table, that is why the record count in the B2B_WIRE_MESSAGE table remains the same
- Creates 3 new records in the B2B_DATA_STORAGE table, one for each of the followings:
Now the B2B_DATA_STORAGE table has 3 more records and the count increased from 4 to 7.
8) Run the command line B2B purge utility, for example:
9) Run the same scripts as in step 3) above. The results should be:
Query 1: 0 record
Query 2: 0 record
Query 3: 0 records
Query 4: 1 records
Note that in QUERY 4 there is a record left that the purge utility didn't delete. This record is the one corresponding to the record that contains the old B2B_WIRE_MESSAGE.PAYLOAD_STORAGE value in the B2B_DATA_STORAGE table. The record that was created by B2B for the original inbound message before the wire messages was resubmitted.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!