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!
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 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
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd 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 in the words of the most interesting man in the world: Stay thirsty my friend.
AS400 Technical Interview Question and Answer – AS400 Database
How to Install IBM Access Client Solutions (ACS)
IBM i Data Obfuscation – Making Data Foggy Murky and Squinty
How to rename Fresche (BCD) Presto Library – XL_PRESTO
What is AS400 modernization?
IBM i ACS 5250 EMULATOR FONT – and other ridiculous mumbo jumbo
IBM i SQL statement to convert or compare hundred year date format
How to compare ‘100 Year Date’ to a Timestamp – aka – Weird AS400 iSeries Date formats
Modernize AS400 iSeries Queries – Convert to IBM i SQL