PHYSICAL FILES
Full-procedural file processing provides access to disk files and workstation files. Disk files contain data and are referred to as database files. Database files are files that have been defined to the operating systems by the data definition services. This allows RPG programs to access the file with operating-system services. Database files consist of a group of related data records. Data records consist of one or more fields. Database files are defined with a data definition language such as SQL—which is available on most computer systems—and DDS, which is available on the AS/400 for data definition.
To define a file using DDS, source code describing the file must be created. File definition source is created in much the same way as an RPG source program; a source-code editor is used to create data definition source code. For example, a file named CUSTMAST, consisting of five fields and a key field, can be created with the DDS source code shown in Figure 11.2.
 |
.....Aan01n02n03R.Format++++.Len++TDPURowColKeywords++++++++++++++++++ 0001 A R CUSTREC TEXT('Customer file') 0002 A ACTNBR 5P 0 TEXT('Account Number') 0003 A CSTNAM 30 TEXT('Customer Name') 0004 A CSTADR 30 TEXT('Street Address') 0005 A CSTCTY 20 TEXT('City') 0006 A CSTSTE 2 TEXT('State') 0007 A K ACTNBR |
Figure 11.2: DDS source to define a database file.
In Figure 11.2, line 1 specifies the name CUSTREC for the record format of the file. Lines 2 to 6 define each field of the file. Line 7 specifies that the field ACTNBR (account number) is used as a key field.
Similar to program source code, data-definition source code must be compiled before it can be used by RPG. On the IBM AS/400, DDS is compiled to create a file object. This file object is referred to as a database file.
When the DDS shown in Figure 11.2 is compiled, a file consisting of five fields is created. When the file object is created, only its description is actually created. There is no data in a newly created file object; data must be added to the file through some other method.
Assume that four records have been added to the CUSTMAST database file. These records are accessible by RPG and other high-level languages. Table 11.2 lists the file CUSTMAST after four records have been added.
| ACTNBR | CSTNBR | CSTADDR | CSTCTY | CSTSTE |
|---|---|---|---|---|
| 01207 | Skyline Pigeon Co. | Kauai Blvd. | Maui | HI |
| 05320 | Perlman-Rocque | 103rd Street | Lemont | IL |
| 05340 | Champion Parts | 22nd Street | Oak Brook | IL |
| 05381 | Luna Spacecraft | 12 Artemis Drive | Geneva | IL |
The field ACTNBR (account number) is the key field for the file. Therefore, when the file is accessed through its key, it is arranged in order of the data stored in the ACTNBR field. Additionally, a record can be accessed randomly by its key-field value. For example, to retrieve the record for "Luna Spacecraft," the key field ACTNBR is set equal to 5381. See Figure 11.3.
|
.....FFileName++IFEASFRlen+LKeylnKFDevice+.Functions++++++++++++++++++++++++++++ 0001 FCUSTMAST IF E K DISK .....CSRn01Factor1+++++++OpCode(ex)Factor2+++++++Result++++++++Len++DcHiLoEq.... 0002 C *LIKE DEFINE ACTNBR ACTKEY 0003 C ACCT KLIST 0004 C KFLD ACTKEY 0005 C Z-ADD 5381 ACTKEY 0006 C Acct CHAIN CustRec 54 0007 C MOVE *IN54 NOTFOUND 1 0008 C . 0009 C . (the program continues) 0010 C .
|
Figure 11.3: Random access of a database file using the CHAIN operation.
In Figure 11.3, line 1 defines CUSTMAST as an externally described, full-procedural, keyed file. Line 2 defines the ACTKEY field as having the same attributes as the ACTNBR field. ACTNBR is defined in the CUSTMAST file. Line 3 defines the ACCT key list (which is used to access the file randomly by key). Line 4 defines the sole key field ACTKEY.
Line 5 initializes the ACTKEY field with the value of the key to be retrieved. Line 6 randomly accesses the CUSTMAST file using the key list ACCT. If a record exists with a key that matches the key list specified in factor 1, processing continues. If a record with a matching key doesn't exist, indicator 54 is set on.
Figure 11.3 illustrates random file access using a key list. Because the file CUSTMAST has only one key field, a key list is optional. The field ACTKEY could have been used in factor 1 of line 6, producing the same results. When a file's index is made up of multiple key fields, however, a key list is the easiest method to access its records.
Multiple Key Fields
Sometimes more than one key field is necessary for file access. For example, if the customer master file listed in Table 11.2 requires an access path by CSTSTE (state) and ACTNBR, two key fields are necessary.
Figure 11.4 illustrates the DDS for a multi-key database file. Line 7 contains the primary key field and line 8 contains the secondary key field. When records from this file are retrieved, they are ordered by state, and then by account number within the state.
|
.....Aan01n02n03R.Format++++.Len++TDPURowColKeywords++++++++++++++++++ 0001 A R CUSTREC TEXT('Customer file') 0002 A ACTNBR 5P 0 TEXT('Account Number') 0003 A CSTNAM 30 TEXT('Customer Name') 0004 A CSTADR 30 TEXT('Street Address') 0005 A CSTCTY 20 TEXT('City') 0006 A CSTSTE 2 TEXT('State') 0007 A K CSTSTE 0008 A K ACTNBR |
Figure 11.4: Database file with multiple key fields.
Table 11.3 lists how the database records in the database file are ordered when the file is indexed by the CSTSTE and ACTNBR fields.
| ACTNBR | CSTNBR | CSTADDR | CSTCTY | CSTSTE |
|---|---|---|---|---|
| 01207 | Skyline Pigeon Co. | Kauai Blvd. | Maui | HI |
| 05320 | Perlman-Rocque | 103rd Street | Lemont | IL |
| 05340 | Champion Parts | 22nd Street | Oak Brook | IL |
| 05381 | Luna Spacecraft | 12 Artemis Drive | Geneva | IL |
When the file CUSTMAST is processed, both key fields can be used to access the file or only the primary key can be used. For example, to retrieve the record for "Perlman-Rocque," the primary key field must be set to IL, and the secondary key field must be set to 05320. See the example source code shown in Figure 11.5.
|
.....FFilename++IFEASFRlen+LKeylnKFDevice+.Functions++++++++++++++++++++++++++++ 0001 FCustMast IF E K Disk Rename(CustMast : Customer) .....DName+++++++++++EUDS.......Length+TDc.Functions++++++++++++++++++++++++++++ 0002 D State S Like(CstSte) 0003 D AcctKey S Like(ActNbr) 0004 D NotFound S 1A INZ(*OFF) .....CSRn01Factor1+++++++OpCode(ex)Factor2+++++++Result++++++++Len++DcHiLoEq.... 0005 C ACCT KLIST 0006 C KFLD STATE 0007 C KFLD ACCTKEY 0008 C EVAL AcctKey = 5360 0009 C EVAL State = 'IL' 0010 C ACCT CHAIN CUSTMAST 71 0011 C EVAL NotFound = *IN71 0012 C . 0013 C . (the program continues) 0014 C .
|
Figure 11.5: Random access of a database file with multiple key fields.
Figure 11.5 illustrates random file access using two key fields. A key list is the preferred method for accessing files by key when multiple key fields exist for a file.
When multiple key fields exist, the number of key fields on a key list can be less than or equal to the number of key fields that make up the access path. When the number of key fields of a key list is less than the number of key fields for the file, that key list is called a partial key list.
Partial key lists are useful when non-unique primary keys exist in the file. For example, in the file CUSTMAST, three records exist for the state of Illinois. If the RPG program must process all records for the state of Illinois, a partial key can be used to position the file to the first record with CSTSTE equal to IL, and then read all records where CSTSTE equals IL. See Figure 11.6.
|
.....FFileName++IFEASFRlen+LKeylnKFDevice+.Functions++++++++++++++++++++++++++++ 0001 FCustMast IF E K Disk Rename(CustMast : Customer) .....DName+++++++++++EUDS.......Length+TDc.Functions++++++++++++++++++++++++++++ 0002 D State S Like(CstSte) 0003 D AcctKey S Like(ActNbr) 0004 D NotFound S 1A INZ(*OFF) .....CSRn01Factor1+++++++OpCode(ex)Factor2+++++++Result++++++++Len++DcHiLoEq.... 0005 C ByState KLIST 0006 C KFLD State 0007 C Eval State = 'IL' 0008 C ByState Chain Customer 71 0009 C EVAL NotFound = *IN71 0010 C Dow NotFound = *OFF 0011 C EXSR PRINT 0012 C ByState ReadE Customer 75 0013 C EVAL NotFound = *IN75 0014 C endDo 0015 C . 0016 C . (the program continues) 0017 C .
|
Figure 11.6: Random access of a database file by partial key.
In Figure 11.6, the partial key list BYSTATE (line 5) is used to access records in the CUSTMAST file. The access path for CUSTMAST consists of two key fields: CSTSTE and ACTNBR. The key list BYSTATE is made up of the single key field STATE. This provides file access by only the CSTSTE key field.
Line 2 defines the STATE field with the same attributes as the field CSTSTE (the primary key field for the file CUSTMAST). Line 5 defines the BYSTATE key list with one key field STATE (line 6). No other key fields are specified. Therefore, the key list becomes a partial key list.
Line 7 sets the STATE field equal to IL. This sets the value of the key list, which is used later to access the file. Line 8 chains to the file using the BYSTATE key list. A partial key list is used. Therefore, if records with duplicate keys exist, the first record in the file whose key matches the key list is retrieved.
Lines 10 to 14 read all records in the file where the key equals that of the key list. Line 12 retrieves the next record where the key equals the value of the partial BYSTATE key list. When no more records exist that match the key list, resulting indicator 3 (indicator 75) is set on.
Partial key file processing is common in the modern RPG language. For example, in a manufacturing application a product structure file typically contains two key fields—the item number and the subitem number.
When an item is manufactured, it is assembled into a "finished good" using subitems. A product-structure file contains the finished-goods assembly. Table 11.4 lists a typical product-structure file.
| ITMNBR | SUBITM |
|---|---|
| 101 | 127 |
| 101 | 501 |
| 101 | 602 |
| 201 | 127 |
| 201 | 333 |
| 201 | 402 |
| 201 | 602 |
| 450 | 101 |
| 450 | 333 |
The ITMNBR field is the primary key and the SUBITM field is the secondary key. In order to process an item, a partial key list is used. See Figure 11.7.
|
.....FFileName++IFEASFRlen+LKeylnKFDevice+.Functions++++++++++++++++++++++++++++ 0001 FProdStructIF E K DISK Rename(ProdStruct : Products) .....DName+++++++++++EUDS.......Length+TDc.Functions++++++++++++++++++++++++++++ 0002 D NotFound S 1A INZ(*OFF) .....CSRn01Factor1+++++++OpCode(ex)Factor2+++++++Result++++++++Len++DcHiLoEq.... 0003 C Primary KList 0004 C KFld Item 0005 C Eval Item = 201 0006 C Primary Chain Products 71 0007 C Eval NotFound = *IN54 0008 C Dow NotFound = *OFF 0009 C ExSr Print 0010 C Primary ReadE Products 75 0011 C Eval NotFound = *IN75 0012 C endDo 0013 C . 0014 C . (the program continues) 0015 C .
|
Figure 11.7: Sequential access by key of the product structure file.
Figure 11.7 processes the PRODSTRUCT file. Item number 201 is moved into the key list PRIMARY (line 5). That key list is used to retrieve the first record (subitem 127) in the product-structure file for item 201 (line 6). The DO WHILE loop (lines 8 to 12) processes the remainder of the product structure with the READE operation.
EAN: 2147483647
Pages: 156