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 999999 the number rolls back to 000001. 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: [email protected] 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 [email protected] 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 [email protected] To unsubscribe please visit http://listserver.u2ug.org/
