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()*10000 + 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 [email protected] To unsubscribe please visit http://listserver.u2ug.org/
