B2B_DATA_STORAGE Table Grows. Orphaning Records after Resubmit of Wire Message (Doc ID 1437223.1)

Last updated on APRIL 17, 2023

Applies to:

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:

• b2b_wire_message
• b2b_business_message;
• b2b_app_message;
• b2b_data_storage;

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:

• B2B_BUSINESS_MESSAGE.PAYLOAD_STORAGE
• B2B_APP_MESSAGE.PAYLOAD
• B2B_WIRE_MESSAGE.PACKED_MESSAGE
• B2B_WIRE_MESSAGE.PAYLOAD_STORAGE

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:

• B2B_BUSINESS_MESSAGE.PAYLOAD_STORAGE
• B2B_APP_MESSAGE.PAYLOAD
• B2B_WIRE_MESSAGE.PAYLOAD_STORAGE

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.