Re: Question on File locking

It sounds to me what you're describing is Journaling.

Trevor Briggs
Analyst/Programmer
Lincare, Inc.
(727) 431-1246
TBriggs2@xxxxxxxxxxx


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Vinay Gavankar
Sent: Wednesday, August 13, 2014 11:44 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Question on File locking

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

On Wed, Aug 13, 2014 at 8:31 AM, Joep Beckeringh<joep.beckeringh@xxxxxxxxxx

wrote:

Vinay,

Are you presenting a logic puzzle just for fun, or is this a real life
situation?

Your problem was that different instances of a program writing to the
same file might try to write the same sequence number to that file.

You were offered three valid solutions:
- Scott suggested to use SQL IDENTITY
- Booth suggested serializing the writes by using a data queue
- Chuck suggested to lock the initial row in a set instead of the last
row

For reasons unknown to us you decided to ignore Scott’s and Chuck’s
suggestions and pursue the data queue idea. But now you complicate
things further by having multiple instances of the program reading the data queue.
The whole idea of a queue is to serialize things. You can have
multiple jobs writing to the same queue; by having one job reading and
processing the queue there is always just one job writing to the file.

Joep Beckeringh



Op 13 aug. 2014, om 13:00 heeft Vinay Gavankar<vinaygav@xxxxxxxxx>
het volgende geschreven:

I also need the records to be written in the sequence they came in
(thru the data queue).

Here is a scenario where the records may not be in sequence:

Let us say the Audit file already has seq 99998 for ID ABC.

Then there are 3 instances of the main file update for ID ABC one
after

the

other in the data queue, which get picked up by three different
instances of the program in question.

The first instance of the program gets the first record, and goes
into

the

loop to determine the lowest sequence (which is 99998 as of now) and

write

a new record (which would be 99997).

BEFORE the 99997 record is written by the first instance of the
program, the second instance of the program gets the second record,
and goes into the loop to determine the lowest sequence (which is
still 99998) and

write

a new record (which would be 99997).

The first instance of the program now completes it loop and writes
the
99997 record successfully.

The second instance is still in it's loop.

Now, the third instance of the program gets the third record, and
goes

into

the loop to determine the lowest sequence (which is 99997 now) and
write

a

new record (which would be 99996).

The second instance now completes it write of 99997, finds an error,
and goes back in the loop. Now, it finds 99997 as the last record
and intends to write 99996.

The third instance completes its loop and writes 99996 successfully.

The second instance encounters one more error on write, and in the
next loop writes 99995.

Here, I have all 3 records written successfully, but are out of sequence.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.

************************************************************************************************************************************************************************************************************
This message originates from Lincare Holdings Inc. It contains information which may be confidential or privileged and is intended only for the individual or entity named above.
It is prohibited for anyone else to disclose, copy, distribute or use the contents of this message.
All personal messages express views solely of the sender, which are not to be attributed to Lincare Holdings Inc., and may not be copied or distributed without this disclaimer.
If you received this message in error, please notify us immediately at MailAdmin@xxxxxxxxxxx or (800) 284-2006.
************************************************************************************************************************************************************************************************************