But his comments have generated a modification idea to my earlier
suggestion. If, before the TRANSACTION START command, you get a "next
available ID" then use that as a "root" value, you can then create a
sequential number with an internal sequential number. IE:
ROOT.KEY=''
LOOP UNTIL ROOT.KEY
RELEASE ;* make sure all locks are released
READU ROOT.KEY FROM CF, "NEXT.ROOT.KEY" THEN
READ JUNK FROM OUTFILE, ROOT.KEY:"*1" THEN
WRITE ROOT.KEY+1 ON CF, "NEXT.ROOT.KEY"
ROOT.KEY=''
END
END
REPEAT
WRITE ROOT.KEY+1 ON CF, "NEXT.ROOT.KEY"
TRANSACTION START
SUB.KEY = 1
EXIT.FLAG=''
LOOP
OUTRCD = ''
[do whatever builds OUTRCD]
WRITE OUTRCD ON OUTFILE, ROOT.KEY:"*":SUB.KEY
SUB.KEY += 1
[whatever to determine if time to exit]
UNTIL EXIT.FLAG REPEAT
TRANSACTION COMMIT ;* or whatever logic to rollback.
This is actually a modification of another suggestion to lock a "block"
of numbers, too. Difference is a block of numbers would be a maximum
number of records that could be created, like 123400 to 123499 if
blocking to a factor of 100. With a multi-part key, using the asterisk,
you no longer have the maximum number block. It could go 10, 100, or
even 10,000 if need be. You could even use something like ROOT.KEY:"*0"
if you like as a sort of "header" record to keep track of how many
records there should be for the ROOT.KEY family. This should keep the
auditors happy, "Transactions" sequential, and cross-transaction locking
to an extremely short interval.
Also, by using READU without the LOCKED clause, the program will wait
that short interval until the record is available.
As a side comment, this method will also make it easy to keep records in
a sorted, associated order. You could even get a count of process runs
by selecting @ID = "[*1" or SUB.KEY = "0" if you set the dict record.
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Stevenson,
Charles
Sent: Sunday, June 12, 2005 1:26 PM
To: u2-users@listserver.u2ug.org
Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION
START & COMMIT/RO...
Charlie,
I don't see how that addresses the TRANSACTION problem.
If the call to your NEXT.AVAIL.ID routine happens after TRANSACTION
START, the id control item will not be updated nor will its lock be
released until TRANSACTION COMMIT (or rollback) is executed, even though
the NEXT.AVAIL.ID routine says to write and release it. This is a
potential bottleneck if other concurrent processes are wanting to do the
same, waiting for that control item.
cds
-------
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
-------
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
