|
Joep,
This is very much a real life problem. Maybe I started with providing too
little information.
The idea of data queue is not only to serialize things, but it can also be
used to multi-thread processing.
Current Scenario:
There is a Member File, with Member Id as Unique key. The key is not being
changed or records are not being deleted from this file.
This file has a Trigger defined for *INSERT & *UPDATE.
The Trigger program writes a record to a Data Queue. This is optimum as we
don't want to hold up any program making an update to the Member file any
longer than necessary, while the Trigger program does whatever processing
it needs to do. So any Trigger program should do minimum processing.
The program that reads the data queue, does a lot of processing creating
multiple other files. So we need to run multiple instances of this program
to keep up with what is being written to the data queue. Currently, the
files being written are simple "flat" files so running multiple instances
does not pose any issues.
Currently there is no "audit" information on the Member file about which
fields changed and when they changed etc.
Problem:
A new Client (US Federal Government) requires detailed audit information on
the Member file. They assess a penalty of $1 Million for every missed or
incorrect audit information (I don't know if and how they enforce it, but
we are told that is what the contract says).
To be accurate, the audit records need to be in exact sequence of how the
updates to the Member file occurred. (@Buck: If they are not in the update
sequence, then they would be considered out of sequence).
Approach to solving the problem:
The simplest way of capturing Audit information would be to capture Before
and/or After information on every update to the file. As the Member File is
updated from lots of programs, the most logical place would be to use a
Trigger on the file.
Once it was decided to use the trigger, you would always consider using an
existing trigger program (or set of programs), if possible.
That is why I was trying to see if there is a fool-proof way of writing
these audit records in sequence in already existing multi-threaded programs.
I did not "reject" any suggestions. I had asked for a couple of
clarifications in one of my replies:
About Chuck's suggestion for locking record with '99999':
If a record is locked, and 2 other requests are made for the same record,
which one will get the record once it is released? The one which made the
request first or is it "unpredictable"? If it is unpredictable, then this
is not fool-proof to get records in correct sequence. If it is FIFO, then
this would work perfectly.
About Scott's suggestion to use SQL Identity:
/Quote/ I am not familiar with using SQL, but are you all saying that if
this file were to be defined in DDL, then system will take care of
inserting the records in correct sequence, even if more than one additional
request was made while the first one was still being completed? If so, will
that command also return the Sequence number just inserted, so that I can
use it for some other purpose? /Unquote/
Not knowing the answers to these questions, or if the answers turn out to
be unfavorable, I will probably go with Booth's suggestion:
Modify the trigger program to write to another data queue, and create a
brand new program to process that data queue, running only one instance of
it.
Vinay
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 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 [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
