RE: duplicate record Id's in multi user environment

["Richard Jackson" <richardjackson@xxxxxxxxxxxxxxxxxx>]

Ladies and gentlemen:

Please beware of this technique in a very high-volume situation - it will
work most of the time but it is a problem in high volume.

The physical IO created by writing out the data area cannot exceed the
ability of a single disk drive minus the other activity on that drive.  I
will post the arithmetic if you want to see it.  If the drive where the data
area is stored is dedicated to the data area (nothing else on it) and the
drive is RAID protected then the best that you can do is one read + latency
+ one write + latency.  On a model 6607 4 GB drive, you can assign about 100
values per second under those circumstances.  If the drive was dedicated and
either mirrored or unprotected, you could assign about 225 values per
second.  If the drive isn't dedicated and there is other traffic on it, the
rate could drop as low as 50 to 70 values per second.

This concern does not matter in most cases but if you need a very large
number of "unique numbers" during a short period of time, you need to use a
technique that does not require writing the value to disk or a technique
that can be multi-threaded.


Richard Jackson
mailto:richardjackson@richardjackson.net
http://www.richardjacksonltd.com
Voice: 1 (303) 808-8058
Fax:   1 (303) 663-4325

-|-----Original Message-----
-|From: owner-rpg400-l@midrange.com [mailto:owner-rpg400-l@midrange.com]On
-|Behalf Of Eric N. Wilson
-|Sent: Thursday, October 12, 2000 8:56 AM
-|To: Ray Nainy
-|Cc: RPG400-L@midrange.com
-|Subject: Re: duplicate record Id's in multi user environment
-|
-|
-|Hello Ray,
-|
-|Wednesday, October 11, 2000, 11:22:54 PM, you wrote:
-|
-|
-|> Hello,
-|
-|> What would be the best way to assign unique record Id's in a multi user
-|> environment? Since, we have about ten users who add records to same file
-|> almost at same time. Chances are high that more than one user
-|try to add a
-|> record at same time. The file should only have unique record Id's.
-|
-|> Our users access this file through a client server application
-|> (power builder). The client server application calls a stored procedure
-|> written in SQLRPGLE to add the records to file.
-|
-|> Any help is greatly appreciated.
-|
-|
-|
-|You could do what we do. Namely make a trigger program and attach it
-|to the insert event of the file.
-|Then inside the trigger program
-|     lock a data area,
-|     increment the value in the data area
-|     unlock data area and
-|     use the new value from the data area as your sequence number.
-|     Update the after image in the parameters
-|and that will give you what you need.
-|
-|Make sure you specify allow repeated change on the addpftrg command.
-|
-|Works very very well!
-|
-|
-|+---
-|| This is the RPG/400 Mailing List!
-|| To submit a new message, send your mail to RPG400-L@midrange.com.
-|| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
-|| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
-|| Questions should be directed to the list owner/operator:
-|david@midrange.com
-|+---

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---