An Idea For Processing Trigger Files

[Poddys]

I haven't used trigger files before, but I wondered what people think of the method below, instead of using data queues.

I have about 20 files that I need to synchronize with their equivalents in a MySQL database. I already have a PHP script that reads all the data and creates each MySQL table in full, but we really need something that will keep the tables in sync and just process the changes.

Obviously the trigger programs need to be fast, since the I/O operation is not completed until the trigger program is complete.

I was thinking to use an RPG trigger program (ideally a single one which would work for each table), which would read the trigger buffer, and write the file, action and key fields to a logfile, rather than to a data queue.

The logfile would have fields for the file, action and the key fields (5 I think is the max keys on any file), the key values stored as text, and it would also have a timestamp field that would be populated once the action had been processed.

I was going to use a scheduled PHP script to read the logfile, and for each record it would perform the action on the file in MySQL. By using PHP I should be able to avoid dealing with alpha/numeric conversion and field lengths, and can hopefully have just 1 script to deal with any file.

The logfile will serve as an audit trail of all file updates, and if anything goes wrong with the program that processes it, in particular if it tries to update MySQL on a server that can't be contacted, the change requests will remain until everything is working again. Actioned logfile entries will be purged after a given time to keep the file small.

I am assuming that PHP can't read data queues, so by writing to a logfile instead, that widens the scope for how to process the data.

Any thoughts on this as against other methods that people have used?

[chris_hird]

Re: An Idea For Processing Trigger Files

Easycom allows you to read data queues in PHP :-)

Chris...

[tomliotta]

Re: An Idea For Processing Trigger Files

If you're going to write a physical log file anyway, why wouldn't you just journal the files? You could then process journal entries for the synchronization and gather everything you'd need. The journal entry process could pull large blocks of journal entries and make the record data available to any process that needed it.

[Poddys]

Re: An Idea For Processing Trigger Files

I haven't used journalling before either, believe it or not.

That might be simpler, if having idenfitied the files to be journalled, it was only necessary to create processes that read the journals. Will look into that, thanks.

[chris_hird]

Re: An Idea For Processing Trigger Files

If your MySQL database is on the same IBM i, the trigger program could call PHP CLI to write the MySQL DB? Writing a journal reader and apply process could get quite complex where the trigger option might be simple yet elegant??

Chris..

[tomliotta]

Re: An Idea For Processing Trigger Files

If there is no prior experience with processing journals, then triggers will be easier to get started. It takes work to get the general procs created if they aren't already around.

Timing needs to be considered. At some point, records will need to be pulled from the log file, and triggers might be writing to it at the same time. When records are pulled, they probably will need to be deleted, or at least flagged to avoid processing them again. (And they'll probably need to be deleted eventually.) I suppose that individual records could be pulled then deleted/flagged, one at a time.

But I suppose the triggers could all add a timestamp to each record. Then the MAX() timestamp could be retrieved from the log file and only records less-than-or-equal would be processed and deleted as a set. Any new records would have a newer timestamp and wouldn't get caught in the deletions until the next cycle.

As long as incoming and outgoing don't interfere and records aren't lost, triggers can work.

[Poddys]

Re: An Idea For Processing Trigger Files

Thanks Chris/Tom.

We have a trigger program "almost" working as I write. This definitely seems to be the simplest solution, and we are looking at a single program that writes the file, action and key field fields to the logfile, calculating a unique sequence number for each new record to ensure they are processed in sequence (although in our case this isn't absolutely necessary).

The logfile has a status on each record that will be updated when that record is updated, as well as a timestamp which will be set on update.

The PHP script will process all records that are waiting for update, and should anything fail we have the ability to reset the status/timestamp to get the PHP script to re-process the records.

For simplicity the logfile is only storing the record keys, and the PHP script will use these to retrieve the data from the iSeries record and will replicate it on the MySQL table.

There are about 20 files, but I think we can do this using 1 RPG program to handle the triggers and write the logfile, and then 1 PHP script to process the logfile.