AS400 Showcase Article - Data Navigation

When computers first came out, they were called computers because their primary function was number crunching. As data storage methods improved, the industry became known as data processing. With the advent of sophisticated database techniques the industry has become known for its information retrieval technology. The key to information retrieval is the data navigation used to find desired data.
The AS/400 has a relational database built into the operation system. If you have been creating physical files on the AS/400, you have been using DB2/400. With DB2 you can create logical views that let you access the physical file in various sequences while selecting or omitting records from the view. You can even logically join different files.
Records written to any physical file is written in arrival sequence. When you write a record to a physical file, you are adding that record to the end of the file. This is true even if the file has key fields defined. When you display the file using the display physical file command, DSPPFM, you will see the newest records at the end of the file.
If you write an RPG program that reads a keyed file, but you don't specify in the File Specification that you intend to read in keyed sequence, your program will read the records in arrival sequence. That is, the first records written to the file will be the first ones read. This is true even if you are reading a logical file. That's right. If you read a logical file but fail to specify that you want to read the records in keyed sequence, your program will still retrieve records in the original physical file sequence.
If you do specify that you want to read the records in keyed sequence, the program will read them in order by the key. It is as though the AS/400 sorts the records first. It is actually using an indexed key structure to retrieve the records in order by the keys used to define the file.
If you want to randomly retrieve a single record, you usually use the CHAIN command. The essential information needed by an RPG program to CHAIN to a record is:

1. the file name

2. the value of the key fields that you want to retrieve

3. an indicator to tell if the CHAIN was successful

Figure 1 shows the key code snippets for chaining to a customer record that is keyed on the customer number. In this case, assume that there is a display file with a field named DSCUST. The user keys the desired customer number in field DSCUST. The program then CHAIN's to the CUST file using the value in DSCUST. Inexperienced programmers sometimes get confused here. The field specified in the FACTOR-1 area is the field that holds the customer number that you want. Depending on how you have defined your fields on the display, this may not be the same name as the key field. The AS/400 looks at the value in this field and uses it to see if it can find that customer. If the record is found, indicator 90 is set off. If it is not found, the indicator is set on.
The second snippet of code expands on the first by reading a file with three key fields. Here the program is trying to CHAIN to an order file which is keyed by customer number, order number and date. Since there is room to enter only one value in the FACTOR-1 area, you must build a key list which defines the location of the three fields that hold the values that you want to use as a key. The code shows how you would define a key list named ORDKL1. It has the name of three fields from the display file that have the customer number, order number and date that you want to use as a key.
You may want to read all orders for a customer. This would be valuable for a sub-file display or a report. Figure 2 shows how to do this. Imagine that you are manually going through a box of orders that are sorted by order number, customer number and then date. If you wanted all orders for a customer, you would flip through the file box until you reached the first order for the customer. The set lower limit opcode, SETLL, performs this very function. It sets the location of the file at the first record that matches the partial key that you specify in the SETLL opcode. The indicator will be set on if at least one record exists that matches the partial key. Otherwise the indicator will be set off.
The SETLL opcode doesn't actually read the record. Once you have established the lower limit, use the "read equal" opcode, READE. The READE will actually read the record. The DOWEQ loop in Figure 2 reads all records that match the customer number. It is as though you have removed all orders for this customer and put them in a separate stack. Using the READE, you read through the stack until you are at the end. Indicator 91 will be set on when there are no more orders for this customer.
You can even read information in reverse order using the opposite opcodes. These are the set greater than opcode, SETGT and the read prior opcode, READP.

Reviewing the essential data navigation opcodes:
READ-   sequentially read the next record
CHAIN-   randomly read a record matching the value in FACTOR-1          
SETLL-   set a pointer to the first matching record
READE-   read a record after a SETLL