Mediator Resequencing - simple example

Ok, so we lost against Spain - but at least now the world knows "The Fields of Athenry"

Her is a simple example of Mediator re-sequencing - a not so well known SOA Suite feature.
Very interesting for AIA style integrations e.g. multiple order updates in EBS being passed on to Siebel.

I have the following simple scenario -
Order processing - orders are defined as follows -

Notice the key fields - orderID / msgID. There can be multiple messages for the same orderID. Unfortunately they do not always come in the required sequence. For example we could receive -
orderID=1 msgID=3
orderID=1 msgID=2
orderID=1 msgID=1

Fortunately we have the Mediator which can do the re-sequencing for us, so that our output will be as follows -
orderID=1 msgID=1 orderID=1 msgID=2
orderID=1 msgID=3

To illustrate this, I create the following composite -

ReadOrder - is a ReadFileAdapter based on the XSD above.
WriteOrder - is a WriteFileAdapter based on the XSD above.

The Mediator is configured as follows -

Resequencing level is set to component, I could also set this to operation. This is useful when a mediator supports multiple operations and resequencing is not required for all of them.

I set the mode to standard - others are Best Effort and FIFO.

I set the Group By to orderID and the sort criteria (ID) to msgID. In other words, for each orderID - sort the messages in msgID order starting with msgID 1. Note the increment setting is also equal to 1.

Deploy and test.

 

I drop the orders into the inbox in the order - order1-3 (orderID=1/msgID=3), order1-2, order1-1.

I then re-test with the next 3 in the sequence order1-6 (orderID=1/msgID=6), order1-5, order1-4

I then view the outbox -

The meta-data is stored in the MEDIATOR_RESEQUENCER_MESSAGE table for Standard re-sequencing.

The last 6 entries are from my test today.

Now If I re-submit (orderID=1/msgID=6), order1-5, order1-4 - I get an error 

Checking the WLS log...

Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (DEV_SOAINFRA.MEDIATOR_RESQ_MSG_UNIQUE1) violated

Another interesting table is MEDIATOR_GROUP_STATUS -

Note the entries for /ResequenceRoute - my Mediator.

So we see the next expected sequence id = 7.

The following blog post explains purge strategies for the above tables -

SOA Purge Strategies

We can purge the completed messages from the MEDIATOR_RESEQUENCER_MESSAGE table

There are my messages with status=2 which signals that they are completed.

btw. the relevant purge scripts are available at -

\Oracle_SOA1\rcu\integration\soainfra\sql\soa_purge\mediator