Re: Direction for Keeping Web DB up to date

[Alan Campin <alan0307d@xxxxxxxxx>]

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.