How to create a logical file with data from more than one member

I wrote an earlier post about how to define a file twice in a RPG program so that I can use two members in the file. This led to a further discussion about could I combine data from those multiple members into one using a DDS logical file. This post demonstrates the results of those subsequent discussions.

I am going to use the same file as I did in the earlier post. If you have questions about file layouts, etc., check that post.

I am going to add a third member to the file, which will be called THIRD:

ADDPFM FILE(TESTFILE) MBR(THIRD) TEXT('Just added')

I can list the members in TESTFILE using the SYSMEMBERSTAT SQL View:

Finding logical files that exist in a different library to the physical

I am sure I am not the only person who wants any logical files in the same library as the physical file they are created over. I do realize that join logical files and SQL views can be built over files in different libraries, and they have always been handled in a case-by-case basis.

My senior System Administrator stumbled across a number of logical files which were in a different to their physical files, and wondered how many more there may be?

SQL to rescue and with a simple SQL statement I can give her that information.

Let me explain the scenario I will be using in these examples. I have a physical file, TESTFILE that exists in the library MYLIB. I built three logical files over it, each one was in a different library:

1. Logical file: TESTFILE1 in the library MYLIB
2. TESTFILE1 in the library QTEMP
3. TESTFILE1 in the library MYLIB2

I can display the database relationship between the physical file and these objects by using the Display Database Relations command, DSPDBR:

Fun with Logical Files

This is not going to be an article on how to code Logical Files. It is about how you can use some of the less well known keywords/functions to do some pretty cool, and fun, stuff with the fields within them.

Before I start coding Logical files I need a Physical file. In this article I am going to use the following file:

A..........T.Name++++++RLen++TDpB......Functions 01 A R TESTPFR 02 A PFLD1 3A 03 A PFLD2 2A 04 A PFLD3 30A 05 A PFLD4 5P 0 06 A PFLD5 10A 07 A PFLD6 L

I know I will not get any awards for originality for my choice of file and field names.

CHGPF there's a quirk that can bite your...

The Change Physical File (CHGPF) is a useful command that allows you to change many attributes of a physical file. Many IBM i (AS400) programmers use it to add fields or change the existing fields without having to recompile file. I do too, as I don't have to do the following:

• Delete all the dependent logical files.
• Make a copy of the phyiscal file with all the data in it.
• Delete the phyiscal file.
• Compile the physical file.
• Copy the data from the copied file into the pysical file.
• Delete the copied file.
• Compile all the dependent logical files.

Generally the rule is in this IT department is that all dependent logical files have the same record format name as the physical. But there are a few odd-ball logical files with a different record format name.

When the physical file was changed using the CHGPF command it caused strange errors in programs that used some of the dependent logical files.

After scratching my head for awhile I worked out what had happened.