Tip

Get rid of deleted records and improve performance

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.


This was first published in October 2000

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.