Admin Alert: Determining Which OS/400 Files Need Reorganizing

by Joe Hertvik

In the last "Admin Alert," I described how to reclaim OS/400 disk space from physical files by running the Reorganize Physical File Member (RGZPFM) command, which removes deleted records and reorganizes and compresses file members. What I didn't tell you was how to determine which files have enough deleted records in them so that it's worth it to run a RGZPFM over them.

The gist of this procedure is building a list of files where reorganization will restore the most disk space to your system. Once you have the list sorted by file size and the number of deleted records, it's a simple matter to pick out those files where reorganization will result in the greatest pickup of OS/400 DASD. Here's how to make it happen.

First, create a physical file to hold the results of your file examination. Let's call this file REORGMAP, and it has the following DDS layout:

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

REORGMAP is a simple file that contains the name and library of the files you're examining (the MLFILE and MLLIB fields), as well as the number of records in the file member (MLNRCD), the number of deleted records (MLNDTR), and the file member size (MLSIZE).

There are a few important things to note about this file. First, REORGMAP's fields must be named exactly as shown or the procedure won't work. That's because the file information will be copied from an output file, created through the Display File Description (DSPFD) command, and the fields must be named this way in order to retrieve the proper data.

Second, be sure to create REORGMAP with a large enough value for its initial number of records, which is determined by the Member Size (SIZE) parameter on the Create Physical File command (CRTPF). By default, OS/400 creates the file with enough capacity for 10,000 records. But if you're going to retain file information for every file member on your system, this will probably take more than 10,000 records, so you will definitely need to increase this value. My recommendation is to set SIZE to *NOMAX, meaning that it can store as many records as necessary. To set it to *NOMAX on the CRTPF command, run the command this way:

CRTPF FILE(libname/REORGMAP) SRCFILE(SOURCELIB/QDDSSRC) SIZE(*NOMAX)

If REORGMAP was created without changing the member size, it's a simple matter to change it by running the Change Physical File command (CHGPF), as follows:

CHGPF FILE(libname/REORGMAP) SIZE(*NOMAX)

Also, don't be tempted to create REORGMAP's numeric fields as packed fields. Set them up as standard decimal numbers, as shown here, because that will help you sort the file correctly later.

Once REORGMAP is created, you need to populate it with information from all the files in your OS/400 library. Do this by running the following CL program, which I'll call REORGCREAT.

PGM

DSPFD      FILE(*ALL/*ALL) TYPE(*MBRLIST) +
             OUTPUT(*OUTFILE) FILEATR(*PF) +
             OUTFILE(QTEMP/REORGMAP1)

CPYF       FROMFILE(QTEMP/REORGMAP1) +
             TOFILE(LIBNAME/REORGMAP) +
             MBROPT(*REPLACE) FMTOPT(*MAP *DROP)

ENDPGM

The Display File Description (DSPFD) command retrieves file member information for all members on your system. It copies this information into an intermediary file that will, in turn, be copied into the REORGMAP file I created above. By specifying *ALL/*ALL in the FILE parameter, I tell DSPFD to retrieve information from all the files in the system. The type of information parameter (TYPE) tells OS/400 exactly what information to retrieve. By setting TYPE to *MBRLIST, DSPFD will retrieve an alphabetical list and a brief description for all file members in all files on your system. Setting the File Attributes parameter (FILATR) to *PF tells DSPFD to only retrieve this information for physical files. Finally, the File to receive output (OUTFILE) parameter tells OS/400 to store this information in a file called REORGMAP1, in library QTEMP, and OS/400 will automatically create this file. I chose to store this information in the QTEMP library because REORGMAP1 is a temporary database that doesn't need to be stored permanently on the system.

I'm also retrieving the DSPFD information into a temporary file because the numeric fields we're looking for (MLNRCD, MLNDTR, and MLSIZE) are returned as packed fields to the DSPFD output file. This isn't bad if you only want to use the file in an RPG program or even in an OS/400-based query. But if you want to import the file into a Microsoft Access database, an Excel spreadsheet, or another similar program, you may not be able to sort the numeric data properly if it's in packed format. This procedure is set up as a two-step process, so it moves the data to a final file where the information can be processed in the most useful manner.

After the data has been copied to our intermediary file, the Copy File (CPYF) command copies it to our final target file, REORGMAP. I specify *REPLACE in the replace or add records parameter (MBROPT) so that the file is cleared every time it's recreated. I also specify *MAP and *DROP in the Record format field mapping parameter (FMTOPT), so that CPYF only copies the fields that exist in both REORGMAP1 and REORGMAP.

After running this CL, I have a file containing all the file members on my system, the number of active records in each member, the number of deleted records, and the size of each file. From there, I can manipulate the data to determine which files will give me the biggest RGZPFM bang for my buck. Although you can process REORGMAP in an RPG program, the quickest way to work with this file is either through the iSeries Query (5722-QU1) licensed program or through a client/server program, such as Access.

Whatever program you use, in order to get a prioritized listing of the best files to reorganize to regain disk space, sort REORGMAP by file size (MLSIZE) in descending order and only select those records where the number of deleted records is greater than zero. Then begin at the top of the list and start by reorganizing the files you find there, working your way down. Since the largest files are at the top of the list, you're more likely to pick up disk space by reorganizing those first.

Alternatively, you could also produce your list by sorting REORGMAP by the number of deleted records in each file (MLNDTR) in descending order. This list prioritizes your RGZPFM list by the largest record gains you'll get by reorganizing each file, but that won't necessarily translate into the largest amount of disk space you could retrieve from an RGZPFM. However, it may be comparable.

Perhaps the best way to determine which files to reorganize first is to peruse either list, then reorganize those files where the total file size and number of deleted records are both the highest. This would be an "eyeball" technique rather, than the straight list techniques described here, but it would do the trick.

Sponsored By BYTWARE

You've disabled Active X, turned off Java, and set up a firewall. But do you have virus scanning on your mail? If you use OS/400 mail, a malicious message could undermine all of your other efforts, passing infected message straight to your PCs. Don't overlook your iSeries. Make sure you're covered with the first and only electronic mail scanner for OS/400 mail, powered by McAfee. It's border security for your iSeries. Get Protected. Get StandGuard Anti-Virus. www.bytware.com/love_letter.html