.st0{fill:#FFFFFF;}

What is a physical and logical file in the AS400 Database 

 November 12, 2008

By  NickLitten

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

NickLitten


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

Get In Touch

I’m always looking for awesome input, feedback and critique!

>