Thursday, February 4, 2010

(AS/400 For Dummies) Creating Logical File

After Creating a physical file we can now create logical file. What is a Logical File? A logical file is a database file that logically represents one or more physical files. It contains a description of how data is to be presented to or received from a program. This type of database file contains no data, but it defines record formats for one or more physical files. Logical files let users access data in a sequence and format that is different from the physical files they represent. From the perspective of the SQL interface, logical files are identical to views and indexes.

Logical files have 5 DDS levels, levels 1-4 are similar to the DDS levels of physical file. In this chapter I will only explain the 5th DDS level.
1) File Level DDS
2) Record Format Level DDS
3) Field Level DDS
4) Key Field Level DDS
5) Select/Omit Level DDS. The system can select and omit records when using a logical file. This can help you to exclude records in a file for processing convenience or for security.

Let's try creating our first simple logical file. We will use PFILE01 that we created during our PF creation. Just follow these simple steps:
1) Record Format Level DDS. In position 6 of the SEU body enter "A" then press F4, for Name Type Column enter "R" (to indicate a record format name), then on the Name column input "PFILER1" (this one is your record format name). In the functions column enter this keyword; PFILE(PF Name), this will identify the physical file being referenced and it is a required keyword. After you are done hit the ENTER key. It should look like this:
A R PFILER1 PFILE(PFILE01)

2) Key field Level DDS. In position 6 of the SEU body enter "A" then press F4, for Name Type Column enter "K" (to indicate a key field), then on the Name column input "P1LNME" (this represents your first key field). After you are done hit the ENTER key. It should look like this:
A K P1LNME

3) Select/Omit Level DDS. Now let's try to filter some records in our file. In position 6 of the SEU body enter "A" then press F4, for Name Type Column enter "S" (to select specific records), then on the Name column input "P1AGE" (this represents your field to select from). On the functions column type CMP(LT 40), this will enable us to select all records with P1AGE less than 40. After you are done hit the ENTER key. It should look like this:
A S P1AGE CMP(LT 40)

Here's what we have created:
0000.01 A*
0000.02 A* RECORD FORMAT LEVEL
0001.00 A R PFILER1 PFILE(PFILE01)
0001.01 A*
0001.02 A* KEY FIELD LEVEL
0002.00 A K P1LNME
0002.01 A*
0002.02 A* SELECT/OMIT LEVEL
0003.00 A S P1AGE CMP(LT 40)

You Might Also Want To Visit The Following Tutorial Posts:

AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...