What is a physical and logical file in the AS400 Database

It’s 2008 and the world of Information Technology is a rapidly maturing environment. The old AS400 computer system (I say old because it was replaced by the iSeries machines ten years ago, but lets not go off on that rant again 🙂 ) is still searched and talked about in IT. This morning I was asked “What is so special about the AS400 database?” – so rather than correct my colleagues terminology, lets try to answer the questions simply. The AS400 database is basically just like any other database. If you understand SQL think of an AS400 Physical File as a SQL-TABLE, an AS400 logical file as a SQL-VIEW+INDEX (all rolled into one) and basically there you have it!

What is the AS400 Database?

The AS400 has a database which stores data in files. These are called physical files.

The Physical file stores the actual data.

The data is written to a physical file in arrival sequence. This data can be accessed by RRN (relative record number) where the first entry on the file (also known as a table) is relative record number 1. ie: the first record in the file. The next record is RRN=2 then RRN3 and so on.

If you want to access the data using a sequence of something on the file (for example CUSTOMER sequence) then you would design and create a logical view of this physical file specifying that you want it sequenced by customer.

The Logical File is a sequenced VIEW of the data in the Physical File.

Logicals can be used for updating, creating and deleting data within the physical that it is scoped over.

Join Logicals – are an advanced form of logical file selecting and sequencing data from more than one physical.

Wait – there’s more

You can also add a KEY (the keyed sequence field used in a logical) to a physical file itself.

So a non-keyed FIFO Physical file definition might look like this

A       R RECLIBTBL
A         PRJCDE      3A   COLHDG('PROJECT CODE')
A         PRJDESC    50A   COLHDG('DESCRIPTION')
A         USRLIB   2750A   COLHDG('USER LIBL') 
A         SYSLIB    165A   COLHDG('SYSTEM LIBL') 
A         CURLIB     10A   COLHDG('CURRENT LIBRARY') 
A         CHGUSER    10A   COLHDG('LAST CHANGED BY') 
A         CHGDATE      L   COLHDG('LAST CHANGED ON')
A         CHGTIME      T   COLHDG('LAST CHANGED AT')

This same Physical file when defined to have a key sequence of the Project Code (PRJCDE) might look like this:

A                          UNIQUE
A       R RECLIBTBL
A         PRJCDE       3   COLHDG('PROJECT CODE')
A         PRJDESC     50   COLHDG('DESCRIPTION')
A         USRLIB    2750   COLHDG('USER LIBL')
A         SYSLIB     165   COLHDG('SYSTEM LIBL')
A         CURLIB      10   COLHDG('CURRENT LIBRARY')
A         CHGUSER     10   COLHDG('LAST CHANGED BY')
A         CHGDATE       L  COLHDG('LAST CHANGED ON')
A         CHGTIME       T  COLHDG('LAST CHANGED AT')
A       K PRJCDE

A logical over the same File sorting by Current Library (CURLIB) would look like this:

A       R RECLIBTBL        PFILE(MODLIBTBL)
A       K PRJCDE