As with most things in life, as your databases grow older, the larger and slower they become. The good news is that you can do something about it.
As programs delete records from the database, DB2/400 (unless told otherwise) just "marks" the record as deleted. Because these records are just "flagged" and not really deleted, they take up space and can affect your system's performance.
You can reclaim the storage space these records take up and get the DASD and performance back. First you need to identify the physical files that contain deleted records. IBM provides a command to do just that: "Display File Description" (DSPFD). If you run this command for all physical files in a given library to an outfile, you can query that file and see which files you want to reorganize.
Run the command with the following parameters:
DSPFD FILE(FILELIB/*ALL) TYPE(*MBR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE (YOURLIB/YOURFILE)
You can now query the data by running "RUNQRY *N YOURLIB/YOURFILE". Press F20 a few times, and you will see a column heading of "Deleted Records". This field will display the number of deleted records in each file. To calculate the approximate amount of space you will recover, take the number of deleted records and multiply that by the Maximum Record Length also found in the outfile.
Now that you have identified the files that are wasting space, the next step is to reclaim that DASD. IBM again provides a command to do that. The command is "Reorganize Physical File Member" (RGZPFM). This command will remove the deleted records, which will reduce the size of your physical file.
Before running this command, you will need an exclusive lock on this file. No one will have access to the file that is reorganizing until it is complete. Also, if during the reorganization the job is interrupted, you may possibly lose your data. Therefore, you should run this process right after a full backup of the files -- and during off-hours. This process can take several hours, depending on the size of the file and the speed of your processor.
The RGZPFM command will reorganize one file. To start this process, run RGZPFM FILE(FILELIB/FILE). I recommend you enter RGZPFM, press F4, then place your cursor on the heading at the top of the screen and press F1. This displays the IBM help text, which explains what this command will do and how it will do it.
You'd be surprised at the amount of DASD and performance improvement some systems experience from this. Depending how your software is written, the impact could be huge.
About the author
Kohan is a senior programmer analyst for a software development company. He has worked with the AS/400 since 1990 and has learned how to use the platform to solve business problems.
