JMS Queue - messages sequence

Hello,

[FONT=Arial]I am facing the below request about replication between two databases via WebMethods.

Replication from source system format to target system format is processed through 2 steps :
1 - The source database automatical replication system (ChangeDataCapture) send a JMS message each time a source database table is modified.
2 - A WebMethods service process messages and transforms source data into target objects which will be stored in the target database.
2 mechanisms must be taken into account to manage the translation on the fly. The JMS messages arrival order could not be the same than the source database transactions order :
- When an update is requested for an object which is not yet created or when a child object creation request comes before the associated master object creation, a replay mechanism must allow to put the JMS message concerning the child object on hold pending in order to be able to replay it later.
- Respect the update transactions chronology for a same object. For example, if two sucessives updates on the same source data (for example : Tel number changes, then the same tel number changes again), updates order to replicate in the facade database must be the same.
For instance, the only data into JMS data that could be useful to resequence messages is a timestamp which contains the transaction datetime on the source database.


It could be “easy” to design for a system where there’s a unique consumer.

Considering multithreaded services, there are multiple consumers. So it appears that this solution to garantee the transactions sequentiality is rather difficult to design and implement.

Have you some ideas or requirement that I could submit to my client before I engage myself into the development of a “labyrinthine system” ?
[/FONT]

Thank you in advance for your responses.

Christophe