Admin Alert: Determining Which OS/400 Files Need Reorganizing, Part 2

by Joe Hertvik

A few weeks ago, I described a procedure for determining which OS/400 files need reorganizing. Unfortunately, I made a mistake in describing that procedure by omitting one important parameter: how to determine which physical file members to reorganize in a multiple member file. One reader pointed out the need for a correction, and that correction (as well as some other points) is the topic of this week's column.

Reader Jacques Elias pointed out that "the Display File Description (DSPFD) and the Reorganize Physical Member (RGZPFM) commands work at the member level. [However] I don't see any member name in your DDS description. I am not sure your process will give the right information if a file member other than the first member has records that need reorganization."

And Jacques is right on this point. But the procedure can be easily modified to specify which file members have the largest number of deleted records. Here's how to do it.

My procedure consisted of the following steps:

1. Create a physical file that will contain information about which physical file members need reorganization. This file, called REORGMAP, serves as a roadmap to determine which file members have the largest number of deleted records.
   
   
2. Populate a temporary file called REORGMAP1 in the QTEMP library; REORGMAP1 contains file description information from every file on your system. REORGMAP1 is populated by running the DSPFD command, specifying an output file (*OUTFILE in the Output parameter).
   
   
3. Copy your system's reorganization information from the REORGMAP1 file to your permanent REORGMAP file. This copy creates a file that contains the number of records, the number of deleted records, and the physical file member size of every file member on your AS/400.
   
   
4. Sort the REORGMAP file in descending order by the number of deleted records field, and start reorganizing your file members starting with the files that have the largest number of deleted records and the largest physical size. You can use a number of client server programs or OS/400 Query to do this examination.

The details of how to perform these steps can be found in the earlier column. As I mentioned in point 2, the REORGMAP file contains information on every file member on your system. So sorting it by the number of deleted records will show each file member that needs to be reorganized by using RGZPFM. The only problem, as Jacques pointed out, was that I neglected to put in a physical file member field in the REORGMAP DDS description. So although REORGMAP in its current incarnation would give you the right information on which physical file members have the highest number of deleted records, it only works correctly for files that contain one physical member. If you have a physical file with a number of different file members, it won't be immediately clear which file member should be reorganized.

To correct my procedure, you simply have to change the REORGMAP DDS specifications listed in the earlier column to the following:

A          R REORG
A            MLFILE        10A         TEXT('FILE NAME')
A            MLLIB         10A         TEXT('LIB NAME')
A            MLNAME        10A         TEXT('MEMBER NAME')
A            MLNRCD        10S 0       TEXT('NBR RECORDS')
A            MLNDTR        10S 0       TEXT('NBR DELETED RECORDS')
A            MLSIZE        10S 0       TEXT('FILE SIZE')

This adds the DSPFD member name field, MLNAME, to the file, which was the missing link in my original procedure. Once that field is added, MLNAME will be populated when you copy records into REORGMAP from REORGMAP1. Once this information is included in your file, you can accurately see exactly which physical file members should be reorganized to recover DASD space.

Jacques had another concern with my technique. Specifically, since I used DSPFD to build the file over all files on an iSeries or AS/400, it would include all Q* libraries, which contain IBM system files that should not be reorganized. This is a point well taken, and I stress that anyone using this list for reorganization should ignore files residing in a library starting with the letter Q or set up their query program to omit those libraries.

Another point Jacques made was that, in determining which members to reorganize, it's helpful to calculate the percentage of deleted records to total records in the file. If you have that information, you can make a rule to guide your reorganization decisions. For example, you can say that you'll reorganize any member that has more than 15 percent deleted records. So it might be helpful to include this calculation in your query. You could even make it a calculated field in your query program, which would be a nice add-on.

Sponsored By BYTWARE

Rachel has multiple systems and partitions running WebSphere, Domino, and more. Sound like a management nightmare? Normally it could be, but with MessengerConsole it's so easy! Rachel can see events from all systems and partitions on a single screen. Now she's saving thousands by managing multiple systems and partitions with fewer resources. Get the Message. Get MessengerConsole. www.bytware.com/products/mconsole.html