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.

To create a physical database file, enter your source statements in a DDS source file member. For example, 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. 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, see Figure 1. This file has built into it the fields and their attributes. 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 record 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.

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