RE: Direction for Keeping Web DB up to date

I have some transaction tables that get "replicated" from DB2 to SQL
Server. All have a "surrogate number" or identity column. On SQL Server
I store the table name and last surrogate number copied. In a
transaction, read and insert rows based on surrogate numbers greater
than the last stored, get the last surrogate number inserted, and update
the counter for the table.

All this happens from SQL Server side, so you could run an update job
every 5-10 minutes and should be low impact. Unless you are extremely
high volume (millions of transaction records per day).

Master files, control record files, etc., are probably small enough (in
row count) to not replicate every few minutes, and can replicate the
entire file. Set them up on a separate schedule.

We are manufacturing and have low-to-mid volume.

HTH,
Loyd

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Johnson
Sent: Thursday, August 06, 2009 5:40 PM
To: MIDRANGE-L@xxxxxxxxxxxx
Subject: Direction for Keeping Web DB up to date

We are in the process of deciding how to keep a MS SQL Server database
used
by our website more up to date. Right now all processing and data entry

happens on the i5. Then nightly there are jobs on the MS SQL Server that

pulls data over in mass. They basically wipe the tables on there side
and
reload them. We are looking to do away with this process and do
something
more 'real-time'. This will apply to about 20 tables.

Two suggestions currently on the table are Triggers and Journals. We
already have triggering happening on 3 of the tables that is used for a
different process. There are currently no tables that are journaled.
We
are also concerned with which one will have a bigger impact on system
performance.

Triggers: I was thinking that I could write the trigger programs to
write
entries to data queue(s). These dtaqs are watched by 'maintenance'
programs
that would update the MS SQL Tables 'directly' using JDBC. We already
have
separate RPGIV programs doing this type of thing to MySql. So we have
expertise in house to handle this coding. And we can add any needed
business rules into these RPGIV programs.

Journaling: We would have to add journaling to the needed tables. We are

thinking and have in mind a 3rd party tool that would 'watch' these
journals and apply any needed changes to the MS SQL server side. There
is
no in-house journaling experience. And we are constrained by what the
tool
can and cannot do.


Thoughts?

Thanks,
Scott J.