RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
I cant say that I have experience with this. In our "GET.KEY" subroutines, we do a READU, check to see if the key exists and if not, increment the sequential key, write back to the control file and recordlocku the file we are generating the key for (along with checking the len() of the key and other particulars - barcoding, field widths on reports,screens, etc...) I was thinking if you did something similar, but use a READVL then others might not be locked out of the control file while the transaction is in place ? It was just a thought. If you have a properly structured "GET.KEY" routine and all of your programs check to see if the records exists before writing, then this should work without stepping on existing records. We dont use transaction logging here, so this may make no sense at all. Just throwing out an idea. I dont know if READVL is allowed in a transaction. Maybe not, then bad idea. Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stevenson, Charles Sent: Wednesday, June 15, 2005 11:45 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK From: Anthony Dzikiewicz > So, what if you change the READVU to a READVL ? I don't understand what READL buys me. (Not arguing, just looking to understand.) I thought you need a READU in order to do the write within the transaction. By the way, I've only been working with ISOMODE 1, if that matters. cds --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ -- Internal Virus Database is out-of-date. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.6 - Release Date: 6/8/2005 -- Internal Virus Database is out-of-date. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.6 - Release Date: 6/8/2005 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
From: Anthony Dzikiewicz > So, what if you change the READVU to a READVL ? I don't understand what READL buys me. (Not arguing, just looking to understand.) I thought you need a READU in order to do the write within the transaction. By the way, I've only been working with ISOMODE 1, if that matters. cds --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
This is a pretty good idea, Ken. Getting current date & time is probably cheaper than reading and incrementing a control record. If time is in milliseconds, uniqueness is even more likely so likelihood of locking a new record with SEED_VAL= "000" is good. date()*1 + time() might be faster than 2 fmts and concatenation. But now I'm nitpicking instead of looking at big picture. It satisfies a generation of meaningless keys. Sequential numbers are nicer just because they can hash so well in type-2 files. God help us if that matters. It doesn't satisfy OTHER people's Sarbanes-Oxley concerns about auditors wanting sequential numbers without unaccounted holes. Nor can it be retrofitted into existing structures with the control record like I first described. On the other hand, retrofitting TRANSACTIONs into existing systems is suspect to start with. All in all, a good approach and one we actually use on one of our sub-systems. One of our programmer/analysts is partial to it. cds From: Ken Wallis > I'm not really very fond of straight sequential IDs when the DBMS doesn't provide > inbuilt support for them, yeah, it's the pits. I don't know why the DBMS doesn't have this decades ago. > instead I like to do something like this: > > OPEN "","FILE" TO FILE_VAR THEN > SEED_HEAD = FMT(DATE(),"5'0'R"):FMT(TIME(),"5'0'R") > SEED_VAL="000" > GOT_LOCK = 0 > LOOP > SEED_KEY = SEED_HEAD:SEED_VAL > READVU DUMMY FROM FILE_VAR,SEED_KEY,0 LOCKED > NULL ;* need this or READVU will block > END THEN > RELEASE FILE_VAR,SEED_KEY of course, the release wont happen until after the transaction completes. One could look to see if record exists before attempting to lock it, but that's extra overhead. I'll bet my READSEQ/WRITESEQ variation on Ray Wurlod's idea would be faster than that. Or just live with this occasional hit on the THEN clause for records created and already released within the current second. > END ELSE > GOT_LOCK = 1 > END > UNTIL GOT_LOCK > SEED_VAL += 1 > SEED_VAL = FMT(SEED_VAL,"3'0'R") > REPEAT >* now do something which uses SEED_KEY - this is yours exclusively > END --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
Stevenson, Charles wrote: > 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. I'm not really very fond of straight sequential IDs when the DBMS doesn't provide inbuilt support for them, instead I like to do something like this: OPEN "","FILE" TO FILE_VAR THEN SEED_HEAD = FMT(DATE(),"5'0'R"):FMT(TIME(),"5'0'R") SEED_VAL="000" GOT_LOCK = 0 LOOP SEED_KEY = SEED_HEAD:SEED_VAL READVU DUMMY FROM FILE_VAR,SEED_KEY,0 LOCKED NULL ;* need this or READVU will block END THEN RELEASE FILE_VAR,SEED_KEY END ELSE GOT_LOCK = 1 END UNTIL GOT_LOCK SEED_VAL += 1 SEED_VAL = FMT(SEED_VAL,"3'0'R") REPEAT * now do something which uses SEED_KEY - this is yours exclusively END I wonder if a similar technique could be applied to your problem Chuck. Essentially, the read of the NEXT.ID value would simply get your multiple concurrent transactions all a start point from which they search upward until they find a unique value nobody else already has. Only at the completion of their work and just before the COMMIT statement they can attempt to READU the NEXT.ID value and WRITE back the value they last used. If they don't achieve a lock on this then they simply forget it because that means someone else has a lock and is moving the start point upwards. Cheers, Ken --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
So, what if you change the READVU to a READVL ? Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stevenson, Charles Sent: Friday, June 10, 2005 10:52 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK > One thing I would add to this is to always check to see > if the key exists on the file that you are writing to > before stepping on an existing record. I'm not concerned about fleshing out my little READVU SEQ.ID . . . WRITEV SEQ.ID+1 . . . Yes, you have to check for existing records and worry about the else clauses, etc.. Nevermind about that. That's for a different thread. The point of this thread is about how to best do it when TRANSACTION START & COMMIT are used. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.6 - Release Date: 6/8/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.6 - Release Date: 6/8/2005 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
How about replacing: > 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 with some variation of: OPENSEQ path TO ctrl.fseq ELSE ... READSEQ SEQ.ID FROM ctrl.fseq ELSE ... SEEK ctrl.fseq, 0, 0 ELSE ... WRITESEQ SEQ.ID+1 TO ctrl.fseq ELSE ... CLOSESEQ ctrl.fseq WRITE NEW.REC TO fvar, SEQ.ID All that would be inside the TRANSACTION START / COMMIT. It uses Ray's sequential I/O idea so that the write would happen immediately. OPENSEQ locks the control "item", CLOSESEQ releases it. A little timing test shows it is about 30% slower than the original. (My test did not do the WRITE NEW.REC TO fvar, SEQ.ID, just to be clear.) cds --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
> One thing I would add to this is to always check to see > if the key exists on the file that you are writing to > before stepping on an existing record. I'm not concerned about fleshing out my little READVU SEQ.ID . . . WRITEV SEQ.ID+1 . . . Yes, you have to check for existing records and worry about the else clauses, etc.. Nevermind about that. That's for a different thread. The point of this thread is about how to best do it when TRANSACTION START & COMMIT are used. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
I feel like Winnie-the-Pooh here, Ray. I'm not following you. Are you saying reserve a block of N IDs before starting the transaction, by incrementing by N the traditional next-seq-id control record thereby freeing the ctrl item for use by other processes? What's the point of the 2 sequential io text files? They are private to the particular process reserving said block of N IDs, right? Why not just manage in memory? "Christopher Robin tried to teach it to me once, but it didn't." "What didn't?" said Rabbit. "Didn't what?" said Piglet. Pooh shook his head. "I don't know," he said. "It just didn't. - A A Miln, "The House at Pooh Corner" cds -Original Message- From: Ray Wurlod One approach is to determine in advance how many keys you'll need then generate and store these in a text file. As you work through the main loop, read them from the text file and write the successfully-used ones into another text file. (Sequential file I/O is not counted as part of the transaction.) When it comes time to commit, you can grab the successfully used ones and do whatever you need to do with them. --- --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
One thing I would add to this is to always check to see if the key exists on the file that you are writing to before stepping on an existing record. Depending on how long your records live and how many characters you choose for your sequential key, you can theroetically have dups. We do exactly this kind of thing to generate inventory control numbers. We use 6 digits for our sequential number. So, when we hit 99 the number rolls back to 01. Those numbers are barcoded and the space for barcodes is limited and fixed. Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stevenson, Charles Sent: Friday, June 10, 2005 6:40 PM To: u2-users@listserver.u2ug.org Subject: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK 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/ -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.6 - Release Date: 6/8/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.6 - Release Date: 6/8/2005 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
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: 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/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
As a quick thought to this, could you use a multi-part sequential number that uses the port/user ID? Something like PORT.NO:".":SEQ.ID? Since you are asking with the thought that someone else may need a lock to get a new record key, I am assuming that John after Jane after Jack sequencing is not important, just assurance that record key duplication does not happen and that down to the second is not enough accuracy to use date/time as the record key. Depending on other possible needs, it may just be a necessary evil/delay to hold a record lock until a transaction completes. BobW > -Original Message- > From: [EMAIL PROTECTED] [mailto:owner-u2- > [EMAIL PROTECTED] On Behalf Of Stevenson, Charles > Sent: Friday, June 10, 2005 3:40 PM > To: u2-users@listserver.u2ug.org > Subject: [U2] Best practice for Sequential IDs using TRANSACTION START & > COMMIT/ROLLBACK > > 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/
Re: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/ROLLBACK
One approach is to determine in advance how many keys you'll need then generate and store these in a text file. As you work through the main loop, read them from the text file and write the successfully-used ones into another text file. (Sequential file I/O is not counted as part of the transaction.) When it comes time to commit, you can grab the successfully used ones and do whatever you need to do with them. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
