ACCESS PATH PROCESSING

Database file record access can be performed by record number, sequentially, or by access path. An index is used to process a file by its access path. The index used in an RPG program to access a database file can be a field, constant, or key list.

When a field or constant is used as an index in factor 1 of an input file, the entire composite key of the file is referenced. In other words, all key fields for the file are treated as one large key field by RPG. When a key list is used as the index by RPG, each key field must correspond to a key field in the database file. For example, the order history file consists of three fields: PART, CUST, and QTYORD. The access path for the file consists of two key fields: PART and CUST. The DDS to create this file is listed in Figure 11.13.

 .....Aan01n02n03R.Format++++.Len++TDPURowColKeywords++++++++++++++++++ 0001 A          R ORDREC                    TEXT('Order History') 0002 A            PART           3P 0       TEXT('Part Number') 0003 A            CUST           5P 0       TEXT('Customer Number') 0004 A            QTYORD         7P 0       TEXT('Quantity Ordered') 0005 A          K PART 0006 A          K CUST 

Figure 11.13: DDS for the order history file.

Assuming four records are written to the file—two for part number 100 and two for part number 200—the data in the file appears as listed in Table 11.27. RPG can process this file by its access path (in order according to the file's key fields) or sequentially (by the way the records are physically arranged).

Table 11.27: Order History File with Four Records

PART	CUST	QTYORD
100	1207	50
100	5340	1000
200	5381	65
200	5382	30

Figure 11.14 shows how the file ORDHIST is declared in a program. The file is declared as an input, full-procedural, externally described, keyed file.

 .....FFileName++IFEASFRlen+LKeylnKFDevice+.Functions++++++++++++++++++++++++++++ 0001 FORDHIST   IF   E           K DISK 0002 FREPORT    O    E             PRINTER OFLIND(*INOV) .....DName+++++++++++EUDS.......Length+TDc.Functions++++++++++++++++++++++++++++ 0003 D EoKey           S              1A   INZ(*OFF) 0004 D EoFile          S              1A   INZ(*OFF)       ************************************       *  Print each part number and the  *       *  customers that have ordered it. *       ************************************ .....CSRn01Factor1+++++++OpCode(ex)Factor2+++++++Result++++++++Len++DcHiLoEq.... 0005 C     PART#         KLIST 0006 C                   KFLD                    PART 0007 C                   DOU       EoFile = *ON 0008 C                   READ      ORDHIST                                58 0009 C                   EVAL      EoKey = *IN58 0010 C                   DOW       EoKey = *OFF 0011 C                   EXCEPT    DETAIL 0012 C     PART#         READE     ORDHIST                                58 0013 C                   EVAL      EoKey = *IN58 0014 C                   ENDDO 0015 C     PART#         SETGT     ORDHIST                          54 0016 C                   EVAL      EoFile = *IN54 0017 C          ENDDO .....OFormat++++DAddn01n02n03Except++++SpbSpaSkbSka............................. 0018 OREPORT    E            DETAIL         1 .....O..............n01n02n03Field+++++++++YB?End++PConstant/Editword+++++++++++ 0019 O                       PART             +   1 0020 O                       CUST             +   1 0021 O                       QTYORD        Z  +   1 

Figure 11.14: Processing by access path with a key list.

In Figure 11.14, the key list PART# is used to access the file ORDHIST. The key list consists of a single key field (PART). Because only one key field is specified for the key list PART#, only the first key field of the access path is referenced.

The READE operation (line 12) is used to access the file with the key list. Each time the READE operation is performed, the next record in the file where the key matches the value of the key list PART# is read. When no more keys match the key list value, resulting indicator 3 (indicator 58) is set on.

When a CHAIN operation is used with a partial key list, the first record in the file that matches the key list is returned to the program. For example, if the key list field PART equals 200, record 3 of the order history file (from Table 11.27) is retrieved.

Fields of a key list correspond to the key fields of an access path. For example, the order history file consists of two key fields: PART and CUST. A key list consisting of two key fields would correspond to those two access path key fields specified in the DDS for the file. Figure 11.15 contains the related DDS, RPG key list, and RPG file specifications.

Key field specification excerpt from the DDS of the ORDHIST file:

 .....Aan01n02n03R.Format++++.Len++TDPURowColKeywords++++++++++++++++++      A          K PART      A          K CUST 

Declaration of the ORDHIST file as a keyed file in the file specification:

 .....FFileName++IFEASFRlen+LKeylnKFDevice+.Functions++++++++++++++++++++++++++++      FOrdHist   IF   E           K DISK 

Key list definition used to access records in ORDHIST by key:

 .....CSRn01Factor1+++++++OpCode(ex)Factor2+++++++Result++++++++Len++DcHiLoEq....      C      PRTCST       KLIST      C                   KFLD                    PART      C                   KFLD                    CUST 

Use of the PRTCST key list to access the ORDHIST file in RPG:

 .....CSRn01Factor1+++++++OpCode(ex)Factor2+++++++Result++++++++Len++DcHiLoEq....      C     PRTCST        Chain     OrdRec                             71 

Figure 11.15: Associated file access source code.

A key list can contain as many key fields as the access path. It also can contain fewer key fields than the access path. It cannot, however, contain more key fields than the access path. Key lists and key fields can be used for all indexed file processing. They are flexible, clear, and provide a consistent method for specifying access path (index) structures.