Monday, June 18, 2012

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 -



Bruce Kissinger said...

Great example. Thanks for sharing this.

Niall Commiskey said...

my pleasure

Sachin Jain said...

Very clear explanation of sequencing feature. Thanks for sharing knowledge.


Sachin Jain said...

Very Clear explanation of sequencing feature. Thanks for the insight.



Very interesting explored the database table for re sequence