Finding which fields were defined using a reference field

In my opinion one of the best things about the DDS database is the use of Reference fields. I can define a field in a one file, a "Reference file", and then use it to define fields in other files. These fields will inherit the properties of the "Reference field".

If I need to make a change to the database, changing the size on one field, I can compile all the files that use the "Reference field" and the change will made to the file.

But how can I know which files use a particular "Reference field"?

Fortunately a SQL View gives me the information I need to do this.

Using SQL to check for level check, reuse deleted, and file size

There are three things about data physical files I always like to keep track of:

1. Level check status
2. Will file reuse deleted records
3. What is the maximum number of records the file can contain

For years I have used the Display File Description command, DSPFD, to create a lists of the files in a library, or libraries, and then read that file to check the fields for that information. As I can execute CL commands in ACS's Run SQL Scripts I can do the following:

01 CL:DSPFD FILE(MYLIB/*ALL) TYPE(*ATR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(QTEMP/DSPFD_ATR) ; 02 SELECT PHLIB,PHFILE,PHDTAT,PHLVLC,PHRCDC,PHRUSE FROM QTEMP/DSPFD_ATR ;

Removing deleted records faster than RGZPFM

The subject of this post is not new, but I thought I would share this as this is the quickest way I know to get rid of millions of deleted records taking up space in your files. The last time I used this method was with a file that contained 1 million "active" and 11 million deleted records. The application owner of this file had a fixed amount of time to remove the deleted records in their weekly maintenance "window". Having performed tests using RGZPFM she found that it took longer than the allowed, and came to me for ideas.

RGZPFM FILE(BIGFILE)

The part of this process that many people forget is all the access paths are reorganized too. In this case there were a plethora of logical files built over this file, I forget exactly how many but too many for my liking.

What was my suggested alternative?

Variable length field in DDS file

This all started as a question from a member of another programming team at work. They had been asked to add a new field to an existing file. Most of the time this field would be empty, but it could contain up to 2,000 characters of data in some cases.

"It would be easy with a SQL table," they said "I could just make a new column VARCHAR (variable length character field) and make the default value null. But I don't know how to do that with a DDS field."

By using a variable length character field will mean that the field will not always be 2,000 characters. It will be as long as the data within it. And when there is no data in the field it will take up zero space. What a disk space savings.

Fortunately doing this with a DDS file is as easy as it is with a SQL table using the right keywords when defining the file. And it is just as easy to handle the variable length and null value in a RPG program. I decide to create my example file and the program on an IBM i partition that is running 7.2 just to show that there is nothing from a newer release need to do this.

Using reference fields

When I first started programming on the AS400 the biggest improvement I encountered, compared to the System/36, was the external database. No longer did I have to have file specifications in my RPG programs, and I now had the ability to define fields by referencing another. Over the years I have used the referencing ability whenever I can had to define fields in files or variables in my RPG programs. By using reference fields makes database changes easier as a field's definition is changed in one place, then by recompiling all of the objects with the referencing, the new objects all have the new definition of the field in them.

In the past fortnight I have been asked several times about using reference fields, and in my experience there are many more who would like to ask the same question but feel intimated to do so. Therefore, I am writing this post to give examples of how I use them and why I do things the way I do.

All well-built databases have a data dictionary that defines the types of all the fields, or columns, within it. On AS400 and IBM i many of us achieved something similar using a reference file. This file contains the definition of every type of field that could be contained in any of the other files in the application.

Different types of numbers in files and tables

In a previous post I wrote about the different types of numbers that could be used in RPG. Alas, we have been spoiled with RPG as there are less types to choose from in when defining numbers fields in DDS files and columns (SQL) DDL tables.

 

DDS files

DDS files come in different forms, and they all have their own rules as to the types of numbers are valid. I am only going cover the major types, which in my opinion are:

1. Physical files
2. Display files
3. Printer files

Recovering source for a Physical file

I am sure this has happened to us all: I have an object and the source it was compiled from is missing. In an earlier posts I showed how I could retrieve the source code from a program, but what about other object types?

Many, many years ago I wrote a command and programs to retrieve the source code for various types of files. When I recently found a physical file with its source missing I thought "I will just use my retrieve command". To my horror I found the command source and object in my personal tools library, but the programs and their source were missing. I may only use this command once in a very long time, but in the past it had proved invaluable in recovering the source for physical, display and printer files. I decided to go ahead and recreate the programs, in several stages. First I want to be able to recover the source for a physical file, then I would move onto the more complicated arrangements found within display and printer files. This post will describe the first stage of this journey, recovering the source for a physical file. The later stages will be covered in future posts.

New RedPaper: Reorganize Physical File Member

I have Ralf Petter to thank for bringing to my attention a RedPaper, published this month, describing the changes to the RGZPFM, Reorganize Physical File Member, command.

It includes the changes made to the command with the release of TR7 (Technical Release 7), including the new From Record parameter, and how you can determine the status of the file's reorganization.

You can download/view the RedPaper, in PDF format, here.

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.