B2B_DATA_STORAGE Table Grows. Orphaning Records after Resubmit of Wire Message

(Doc ID 1437223.1)

Last updated on FEBRUARY 01, 2017

Applies to:

Oracle SOA Suite - Version 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:

Note: please notice that the above are <table>.<column> pairs. For example: B2B_APP_MESSAGE.PAYLOAD

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:

  1. 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.
  2. 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
  3. Creates 3 new records in the B2B_DATA_STORAGE table, one for each of the followings:


Note: the PACKED_MESSAGE value for the new wire messages reminds the same. No need to create a new record in the B2B_DATA_STORAGE table

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