Good afternoon,
I'm trying to achieve the following functionality and I would like an
opinion, advice from more experienced people:
- the goal is to provide a replication system which will publish messages to a
JMS queue ( OpenMQ) based on the changes that occur in the database tables.
* using a JMS queue will allow me loosely coupled system design
( I'm opened for suggestion here too )
E.g.
* tables - tBook ( the table which is representing a book entity),
tBookAuthor
* tables audit - tBook_aud, tBookAuthor_aud
* replication table : tReplicationMessage - this table holds the
operation type ( insert/update/delete ), the changed columns, the table name (
e.g. tBook), and the primary key.
* My current intention is to provide a stored procedure which
will publish the messages on the JMS queue ( triggered by the
tReplicationMessage table changes). The values that change will be taken from
the audit tables.
* Google protocol buffers will be used as messages format ( faster,
optimal solution)
What I don't like at this approach is the following case :
* 20 tables ( tBookFormat, tBookCategory etc), and each table with
more than 1000 changes per second will trigger too many Java processes ( my
stored procedure from tReplicationMessage table) to be executed for each
change. What is the right way of implementing a batch system in order to be
feasible( batch the messages, e.g. 5 messages, and fire a single Java process
to send data to the JMS queue) ?
What is wrong here and what should be the best approach with Derby DB ?
Most of the opened discussions from internet debate Oracle with its build
notification services, services which are missing on Derby, or ORM which
triggers JMS messages ( my replication system applied to the db layer, not at
ORM level e.g. connection.persist(), connection.close(),
replication.send(message); ! )
I hope someone could advice me, or point me into the right direction.
Regards,
George
