Re: Direction for Keeping Web DB up to date

Updating the records using triggers would work but I am wary of using
triggers without my mediator.

I think the better solution is to use the Journals and use the RTVJRNE
command submitted to a batch job.

Every time an entry of the proper type is added, RTVJRNE call the exit
program. You could then directly update the MS SQL tables.

I have an example program for the exit if you are interested.

On Thu, Aug 6, 2009 at 4:39 PM, Scott Johnson <sjohnson@xxxxxxxxxxxxxxx>wrote:

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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.