B2B_DATA_STORAGE Table Grows. Orphaning Records after Resubmit of Wire Message
Last updated on FEBRUARY 01, 2017
Applies to:Oracle SOA Suite - Version 18.104.22.168.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.
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