Showcase Article - Physical and Logical Files
The AS/400 database, DB2/400 is part of the AS/400 operating system. It is a relational database and has features similar to other databases you may have used such as Microsoft Access, Oracle or Dbase. The standard method of creating files is to define the specifications of the file using DDS. Of course DDS is used for other things on the AS/400 like defining display files and print files.Create a Physical File
To create a physical database file, enter your source statements in a DDS source file member. For now, name the member "CUS" for customer file. Make sure the member type is "PF". This tells the compiler that this is for a physical file. Enter the records in Figure 1 to define a simple customer file. Notice that the first record has an "R" in position 17. This means that this line is specifying the record name, in this case "CUREC". The "TEXT" is optional but helps to document the file.
After the line naming the record, each line describes a field. The field name is followed by the field length and its data type. The three most used data types are "A" for alpha or character, "S" for numeric and "P" for packed decimal. For numeric fields, you must also specify the number of decimal positions. So here, the company number field is a three digit number with no decimal places. The customer number and zip code are also numeric fields with no decimal places. The credit limit is a numeric, packed decimal field with 9 digits, of which two are after the decimal point. The rest of the fields are character fields.
Once you have entered the DDS source code, you must compile it. You do this by keying option 14 next to your member name on the PDM screen. If you pay attention you will see that the AS/400 is actually executing the CRTPF (Create Physical File) command. It knows to use this command because the member type of the source code is "PF".
You now have a database physical file. This file has built into it the fields and their attributes.
Create a Physical File With Key Fields
Let's modify this file definition to add key fields. If a physical file has key fields, programs can randomly access the records or read them sequentially in the order of the key fields. You can see in Figure 2 that it is simple to add key fields.
The "UNIQUE" record at the beginning of the source is used if you want the AS/400 to insist on having no records with duplicate keys. This is optional.
At the end of the source code, there are two lines with "K" in position 17. These lines define the key fields. So, this file will build a key using the company number and then the customer number. Further, it will not allow a duplicate company number / customer number records to be written.
The records are written to the file in arrival sequence. If you read the data by specifying keyed access, the records will read as though they have been sorted by company number and customer number. Also, your programs can randomly retrieve records. For example, a "CHAIN" instruction in RPG can now randomly read the record for a specific company number / customer number.
Create a Logical File To Resequence Order of Records
Now for the real magic. In only a few lines of source code, you can create a logical file. This is a way of reading the file with a different key. A logical file is a list of key values that point to the physical file. It does NOT contain copies of the data in the physical file.
To create a logical file, again enter your source statements in a DDS source file member named "CUS01". This time, make sure the member type is "LF". Enter the source code in Figure 3.
The first line tells the compiler that this logical file will resequence the records in the physical file named in the "PFILE" specification. In this case it is the "CUST" file. Also, notice that here we are using the same record name as the physical file, "CUREC".
The only remaining lines are like the last two lines of the physical file source. They name the fields that you want to use as keys.
Again compile the file by using option 14. This time, the AS/400 will execute the "CRTLF" (create logical file) command. You now can read the customer file in city / state sequence simply by reading the file "CUS01" in keyed sequence.
Create a Logical File To Select and Resequence Records
Finally, look at Figure 4. The "S" type record will select only records that have customer status equal to "A". Also, this logical file will read the records in customer name sequence.
So, a program reading this file in keyed sequence, will read the records alphabetically by customer name and will read only records of active customers.
Figure 1 - DDS for Physical File Without Key
A R CUREC TEXT('CUSTOMER FILE')
A CUCO 3S 0 TEXT('COMPANY #')
A CUSTS 1A TEXT('STATUS CODE')
A CUNUM 5S 0 TEXT('CUSTOMER #')
A CUNAME 30A TEXT('CUSTOMER NAME')
A CUADR 30A TEXT('CUSTOMER ADDRESS')
A CUCITY 18A TEXT('CUSTOMER CITY')
A CUSTAT 2A TEXT('CUSTOMER STATE')
A CUZIP 9S 0 TEXT('CUSTOMER ZIP')
A CUCRLM 9P 2 TEXT('CUST CREDIT LIMIT')
Figure 2 - DDS for Physical File With Key Fields
A UNIQUE
A R CUREC TEXT('CUSTOMER FILE')
A CUCO 3S 0 TEXT('COMPANY #')
A CUSTS 1A TEXT('STATUS CODE')
A CUNUM 5S 0 TEXT('CUSTOMER #')
A CUNAME 30A TEXT('CUSTOMER NAME')
A CUADR 30A TEXT('CUSTOMER ADDRESS')
A CUCITY 18A TEXT('CUSTOMER CITY')
A CUSTAT 2A TEXT('CUSTOMER STATE')
A CUZIP 9S 0 TEXT('CUSTOMER ZIP')
A CUCRLM 9P 2 TEXT('CUST CREDIT LIMIT')
*
A K CUCO
A K CUNUM
Figure 3 - DDS for Logical File to Resequence Order of Records
A R CUREC PFILE(CUS)
A TEXT('CUST BY CITY / STATE')
*
A K CUCITY
A K CUSTAT
Figure 4 - DDS for Logical File to Select and Resequence
A R CUREC PFILE(CUS)
A TEXT('CUST NAME/ACTIVE ONLY')
A K CUNAME
*
A S CUSTAT COMP(EQ 'A')