November 12

0 comments

What is a physical and logical file in the AS400 Database

By NickLitten

November 12, 2008

#database, #db2, #snippet

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

AS400 DatabaseA 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')

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
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

>

Snug CBD

 20% Discount

I have partnered with SNUG CBD givING you Organic CBD
20% discount code "NL20"