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.
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Johnson
Sent: Thursday, August 06, 2009 5:40 PM
Subject: Direction for Keeping Web DB up to date
We are in the process of deciding how to keep a MS SQL Server database
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
reload them. We are looking to do away with this process and do
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.
are also concerned with which one will have a bigger impact on system
Triggers: I was thinking that I could write the trigger programs to
entries to data queue(s). These dtaqs are watched by 'maintenance'
that would update the MS SQL Tables 'directly' using JDBC. We already
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
no in-house journaling experience. And we are constrained by what the
can and cannot do.
This thread ...
Re: Direction for Keeping Web DB up to date, (continued)
This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact