Chuck, The method I use may work for you, and not waste IDs (accountants hate missing sequential numbers). Call a NEXT.AVAIL.ID routine passing a GET code which reads and locks the ID control item pops an ID off the unused ID stack (explained below) or picks up the next ID and increments it in the control item writes the control item (the ID is now yours only) passes back the ID Verify the ID is not already on file if it is, repeat until you have a good ID Do your thing If the transaction is successful, you're done If not, call the NEXT.AVAIL.ID routine with a PUT code which reads and locks the control item pushes the returned ID onto the unused ID stack writes the control item You're done Regards, Charlie Noah Inland Truck Parts In a message dated 6/10/2005 7:44:58 PM Central Standard Time, [EMAIL PROTECTED] writes:
Well, there's no law that says that you can't "waste" id's. Get the next id & update before entering the transaction, I'd say. Or, if your transactions are in batches, get the id, add a hundred(or some other arbitrary but large enough number), and use all of those sequentially in this process. Here's one I encountered recently that I don't recommend: randomly generate each of 8 integers, then read the file to see if it already exists. If it does, generate 8 more... there were times where it took 20 minutes to find an unused one. The idea was to avoid this kind of bottleneck, but once files started to contain millions of records, there was a problem. Solution? prefix with a julian date... "Our greatest duty in this life is to help others. And please, if you can't help them, could you at least not hurt them?" - H.H. the Dalai Lama "When buying & selling are controlled by legislation, the first thing to be bought & sold are the legislators" - P.J. O'Rourke Dan Fitzgerald >From: "Stevenson, Charles" <[EMAIL PROTECTED]> >Reply-To: u2-users@listserver.u2ug.org >To: <u2-users@listserver.u2ug.org> >Subject: [U2] Best practice for Sequential IDs using TRANSACTION START & >COMMIT/ROLLBACK >Date: Fri, 10 Jun 2005 18:39:30 -0400 > >For files with sequential numbers as record IDs, it is common practice >to use a control item (in dictionary or in a special control table) to >get the next sequential id counter, then write back the incremented >counter ASAP so it is available for the next process that needs to do >the same. Some variation on this theme (probably involving a utility >subroutine): > READVU SEQ.ID FROM ctrl.fvar, ctrl.id, n ... > WRITEV SEQ.ID+1 TO ctrl.fvar, ctrl.id, n > WRITE NEW.REC TO fvar, SEQ.ID > > === > BUT: > === > >If that happens inside an explicit transaction bounded by TRANSACTION >START and TRANSACTION COMMIT (or TRANSACTION ROLLBACK), the Sequential >ID control record will not actually be written and/or released until all >updates are done/ditched during commit/rollback. If the transaction is >extensive and involves many updates, this could be a serious bottleneck. > >Making sure that you read the next sequential id as close to the moment >of executing the commit would be helpful, but that is not always an >option in complicated transactions. > >What is the best practice for handling this? What is your experience? >Bottlenecks? > >Ideally, I know what I'd prefer: I wish U2 would handle sequential ids >internally for variants of Type-2 or dynamic SEQ.NUM files. That should >have happened a couple decades ago in PI and Pick. But back to the real >world. What is the best way to handle sequential IDs when using >explicit TRANSACTIONs? > >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/ ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
