What is relatively static, usually small, very important to your system, and more common the more normalized your database? No, not a bug—a table, of course! A table (as defined for the purposes of this article) is an externally described file containing information that doesn’t change often. Some examples of tables are charge codes, type codes, or part numbers. These files have one record per valid code, and multiple applications within a system (perhaps even from multiple platforms) may access the table to validate the code used by that application. Tables might seem deceptively simple, but their use over time can present some challenges. The techniques I describe in this article will ensure that your tables remain secure and flexible throughout the useful life of the information itself.

Figure 1 describes the DDS for our sample table, PARTS. The first four fields are the reason for the table: the part code, price per unit, description, and a discount flag. The primary key is built on the code field. This allows for the most common use of the table: validating the part code keyed in by a user or sent to your company by, for example, electronic data interchange (EDI). In a large table, an additional logical file may be built over the description of the code to provide an alphabetical lookup for users who may not know the code.

Sounds simple, right? But what happens if part ABC has been used for six months at a price of $15 per unit and now the price must be increased to $15.50 per unit? Orders taken before a certain date (say, September 1) must be filled at the old price, so you can’t simply change the price in the ABC record. You could create a new part, ABCD, and instruct the staff to switch over to the new code. However, all of your suppliers would still be using the code ABC in their EDI transactions, and just because you are going to charge more for a part doesn’t mean they should have to change their use of your part codes.

Also consider this: What happens if Joe User decides to do some maintenance to the code table and changes the price to $150 per unit? How will you know who made this change, and more important, how are you going to control who is allowed to update the table records?

The truth is that with widely used tables, you usually need to control two things: who can change the codes and how long each code will be effective. The rest of the fields on the PARTS record allow you to address these issues. Depending on your specific

needs, you probably won’t use all the fields I’ve put on the PARTS file, but the following sections describe how and why each of them would be used.

Authority and Change Management

There are several ways to control the ability to change information on a table. The simplest way is to use an authorization list, which is a list of the users who have authority to an object. In this case, if specific users are on the authorization list (which may be associated with either the table itself or a CL program that calls up the maintenance program), they can add, change, or delete any record in the file. Keep in mind that if the authorization list is built on the program that does the maintenance, it won’t protect the table itself from any updates done via a file maintenance utility like DFU. Associating the authorization list with the table itself may be the more secure option.

If you implement record-level access, the OWNER field comes into play. In this case, the User Profile Name, which is stored in the OWNER field, is the only person allowed to change any information on the record itself. You might, for instance, use this approach if certain people in your organization are experts in certain types of parts. The maintenance program for the table must handle this record-level access checking. In addition, the lookup applications for the PARTS file should display the OWNER so that users who had a question or needed a change made to the record could contact the OWNER for more information. A program to reassign records owned by one user to another user is necessary for when your owners jump ship to another job. You may also want to design your maintenance program so that it can be called in “super user” mode (defined by a parameter passed to the program). In this mode, a user can change any record. Clearly, access to this mode needs to be controlled, probably through the use of an authorization list. After all, owners may want to take a vacation.

If you aren’t going to have record-level authority, you definitely want to include at least the USERCH and CHDATE fields, which contain the User Profile Name of the person who last updated the record and the date of the update. If it is important to know who initially created the record and when, include the USERCR and CRDATE fields on the record. Again, to enable communication between the users of the information in the table, your maintenance program should populate these fields, and your lookup application should display them.

In some cases, you may wish to limit access to only certain fields on the table. For instance, perhaps anyone can change or add records to the table, but you want only certain users to be able to change the active flag or the discount flag. One way to provide this capability is to create (you guessed it) another table! Figure 2 describes the DDS for the table PRTSAUTH, which allows field-level control of who can change what. To implement this strategy, your maintenance program needs to find the current user on the PRTSAUTH table and protect the input fields if that user is not authorized to change them. (You probably want to assume that if specific users are not on the table, they are not authorized.) The USER table would typically be maintained by a designated programmer or operator using a data file utility.

Aging and Obsolescence

We all want to age gracefully; why should the information in our PARTS table be any different? This is where the end effective date and the inactive fields come in. You may want to use one or the other, depending on the nature of change for the information you are storing in the table. In general, if you need to be able to change some information on the record and have the code itself stay the same, using the end effective date is a good solution.

But go back, for a moment, to the situation described earlier, in which the price of the part is going up. For historical reasons, you may not want simply to change the price on the day the new price is effective. For one thing, you couldn’t schedule a price increase

ahead of time—you’d have to be there on the day of the increase and make the change then. Also, you may need to reprint invoices or purchase orders as of a certain date in the past, and you don’t want the reprinted information to be different than what was originally printed. With the end effective date, you can have two records with the same code. The old information can be “retired” by specifying the date in the end effective date and creating a new record with an end effective date in the future. You will need to create a logical file keyed on the Part code (PARTCD) field and the End effective date (ENDEFF) field. To validate a part code, you would put the order date in the ENDEFF part of the KLIST, then do a SETLL with a KLIST and a READE with only the PARTCD value in factor 1. In this manner, your RPG program will retrieve the record appropriate for that date.

Figure 3 illustrates how you can use this end effective date to either hide or display the records that are no longer effective. You can use F8 as a toggle to control whether expired records are displayed. By default, records with end effective dates in the past are not displayed. If users wish to see expired records, they press F8 a second time, and these records are displayed. (Note that the field next to the F8 prompt indicates whether expired records are being displayed.)

The DDS and RPG code that process this screen are shown in Figures 4 and 5. The field name for the YES/NO displayed on the bottom of the screen is OSHOW; it is used to keep track of which mode the program is in. The program first executes the subroutine LODSFL with the OSHOW value set to NO, which causes a comparison to be made between today’s date in YYYYMMDD format, and the field ENDEFF, which is stored on PARTS the same way. If the record has expired, it is not loaded. The subfile is loaded from the beginning of the PARTS file, since the value of the field SRCH6A is blanks. A variable named GO controls the main loop (which writes and reads the display file records), changes the value of OSHOW, and calls LODSFL to reload the subfile when F8 is pressed.

For some types of table data, an inactive flag field is more appropriate than an end effective date. One example of this type of data could be a charge code table. Perhaps a new charge code is created that is supposed to supplant an existing one. For historical reasons, you may not want to delete the old code, but, instead, you may want simply to make it unusable in the future. One way to do this is to implement the use of the ACTIVE field. You could change the value for this flag to N and build a logical file that includes only records where ACTIVE = Y. This logical file would be used for programs that should find only active records. Of course, you can avoid building a logical file and check the value of ACTIVE in your validating program instead.

Last Used Date

There is one more field relating to the aging of records in the table that I have not yet discussed. The LUDATE is the date the record was last used. For very large tables, this can provide a valuable piece of information, since you may want to decrease the size of the table by purging records that have not been used in years. To implement this field, your application programs must update this date whenever a record for a particular code is accessed.

If you want to get really fancy with your table design, consider this: Build in the capability for the user who inactivates the code to enter some notes that might suggest the new code to use or tell inquiring users why the code was inactivated. Your inquiry program may still display the old code and these notes. You will find that users appreciate these kinds of communication facilities, especially in large shops that have many users dispersed over a wide geographic area.

Naturally, these features are easier to build in at the start of the project than to retrofit into existing applications. Any time you add a new field to a table, you must, of course, find out where that table is used. Each program must be evaluated to see if it needs to update or reference the new field. At the very least, you will want to recompile the

program after your change has been implemented. Assuming that your programs are using the externally defined table name, this is an easy thing to do. The Find String PDM (FNDSTRPDM) command allows you to find any string of characters in your source files. You can type in FNDSTRPDM on the command line and press F4 to complete the prompts. Or you can key in option 25 next to the file or member name in PDM and press Enter, and the system fills in some of the prompts for you. You can print the output from this command, and you can run it in batch mode.

Another issue to consider when you add a new field to a table record is the value you want this field to have initially. For instance, if you are adding an ACTIVE field, you will need a way to place a Y in this field as the default for all your records. You can perform this by using the DFT(‘Y’) keyword on the DDS for the new field or by writing a simple conversion program.

Function Before Beauty

These types of programming considerations are hardly glamorous; I think of them as the system design equivalent of cleaning out the garage. But take a moment and consider how good you feel when you get rid of a bunch of clutter and drive your car into an organized and practical environment. It makes all the work you did that weekend worthwhile. Dealing with authority issues and data obsolescence in your tables will make your system cleaner and save you (or your replacement) from maintenance headaches as the system ages.

A R PARTR

* Typical data for a parts table
A PARTCD 10A TEXT(‘PART CODE’)
A PRICE 9 2 TEXT(‘PRICE PER UNIT’)
A DESCR 30A TEXT(‘DESCRIPTION’)
A DISCNT 1A TEXT(‘DISCOUNT Y/N’) VALUES(‘Y’ ‘N’)

* Additional fields for improved table integrity
A ACTIVE 1A TEXT(‘ACTIVE?’) VALUES(‘N’ ‘Y’)
A ENDEFF 8 0 TEXT(‘END EFFECTIVE DATE CYMD’)
A USERCR 10A TEXT(‘CREATED BY’)
A CRDATE 8 0 TEXT(‘DATE CREATED’)
A USERCH 10A TEXT(‘CHANGED BY’)
A CHDATE 8 0 TEXT(‘DATE CHANGED’)
A OWNER 10A TEXT(‘OWNER’)
A LUDATE 8 0 TEXT(‘LAST USED’)

*

A K PARTCD

Figure 1: This DDS defines the PARTS table.

A R PRTSAUTHR
A USER 10A TEXT('USER PROFILE')
A AACTIV 1A TEXT('CAN CHANGE ACTIVE FLAG')
A APRICE 1A TEXT('CAN CHANGE PRICE')
A ADISCN 1A TEXT('CAN CHANGE DISCOUNT')
A K USER

Figure 2: PRTSAUTH is an example of a user authorization file.

Figure 3: You can construct an inquiry program to toggle between displaying and not displaying expired records.