db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From George Toma <toma.georg...@yahoo.com>
Subject Re: Derby replication system - Need help
Date Sun, 16 Mar 2014 15:19:36 GMT


Hi all,


 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



On Sunday, March 16, 2014 11:53 AM, George Toma <toma.george89@yahoo.com> wrote:
 
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
Mime
View raw message