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 11.1.1.4.0 and later
Information in this document applies to any platform.

Symptoms

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.

 

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