Manage Learn to apply best practices and optimize your operations.

Database serial numbers help identify records in a file

If you have ever designed database files for a complex system, then you are no doubt painfully aware of how cumbersome a unique key structure can get. The AS/400 rises to this challenge by allowing lengthy key lists and generous field lengths for individual key fields. But as the number of items in a key list grows bigger, so does the difficulty of working with the file. One excellent solution to this problem is provided by the concept of database serial numbers. A database serial number is an absolutely unique key (I use a 15-digit numeric field) that identifies each record in a file. When I say "absolutely unique," I mean unique across the entire system. No other record in any file in any library has the same serial number. Here's how it works:

Set up a 15-digit data area (I call it SERIAL) in a library that is accessible to every user and that will be saved along with your data files during each nightly backup. Whenever a new record is added to a file do the following: access this data area, increment the serial number, assign it to your new record, then post the new number back to the data area. Then create a new logical index over each file based on this unique serial number. Using my naming conventions, if the file is called INVOICE, the serial-number logical file is called INVOICE0. The '0' at the end of the logical file name tells me that it is an index based on this unique serial number field. A related file, such as an invoice detail file, might carry the serial number of the invoice header as an informational data field, but each detail record will also have its own absolutely unique serial number.

Once this serial number index has been established over each file, accessing or updating any specific record becomes much easier to do. Because the serial number index is based on only one field, RPG requires no key list to retrieve or update a record. This feature is ideal for a subfile program that reads a sequenced file but posts information back to individual records. Better yet, you now have a common-sized key field in every file that can be used to link to other service programs. In my systems, I have one program for entry of notes relating to any database record. The key to the note record is the 15-digit serial number and a sequence number. Another great benefit is that because the serial numbers are assigned in order, they provide you with a way to track the true sequence of events in a file or a system. Try it -- you'll find it really simplifies your programs.


About the author: Laird Scranton owns Concise Systems in Albany, N.Y. He has 25 years' experience as an RPG programmer, data processing manager and software consultant for IBM AS/400 and System/3x computers, writing custom software and supporting business software packages for more than 250 companies of all sizes in New York and New England.


From Mike Gertz:
It's a nice technique, and one I've used before, but you need to understand the risks too.�Data Area access is fast, but it does take some time.�If you use this technique with a relational database and have a number of jobs doing a lot of writes, you can run into a locking issue on the Data Area.

From Bob Leroux:
I think you have to be careful with this, as the data area could become a bottleneck if high numbers ore records are added to your files.�Your throughput might dramatically decrease.

From Ren� Veenstra:
You should make sure you have an exclusive lock on the SERIAL data area during the entire sequence: read data area, increment serial number, write back number. If you don�t have an exclusive lock on the data area, another user might read the data area before you post back your new serial number and that will leave you and the other user with the same value and your numbers are no longer unique.

From Stan McPartland:
After reading the tip "Database serial numbers help identify records in a file," I had to check the calendar to make sure it wasn't April first. Not only does this technique nullify all attempts at good relational database design, but it will cause significant performance problems in a system with any kind of transactional volume.

From Vern Borts:
Excellent tip. I don't know how much we will actually be able to use it, since we try to keep our development to a minimum, but I like the idea.

From Pascal Jacquemain:
This tip is interesting, but would probably have to be acted upon at the time the database is designed. It would be very difficult for a company like mine, with a huge number of physical files on a number of systems, to suddenly implement such a solution. However, this tip reinforces programming trends which I think are positive, that is to centralize I/Os in sub-procedures rather than having dozens of programs able to create, update or even read records from the same file.

Dig Deeper on Data backup, storage and retrieval on iSeries