How Much Storage is Used by Deleted Records in Physical Files?

Figure 3. Flow Chart: Deleted Records hen your program requests a record deletion, OS/400 does not necessarily remove the record from the physical file. Unless you created the physical file with REUSEDLT(*YES), the system just marks the record as deleted and retains it in the physical file. As a result, you can use up a lot disk space by keeping records that are serving no purpose.

You can get back the disk space used by deleted records, by routinely searching for physical files that have deleted records.

Display File Descriptions

To create a report on deleted records for a library, start by using the command DSPFD and directing the output to an outfile. For example, for all physical files in the general ledger file library:


DSPFD	FILE(GLFILES/*ALL) +
	TYPE(*MBR)  FILEATR(*PF) +
	OUTPUT(*OUTFILE) +
	OUTFILE(mylib/myfile)

Calculate the Storage Used

Use the create results feature in Query/400 to calculate the amount of storage used by deleted records. You can, of course, ignore this calculation, and just report the number of deleted records. However, it is simple to calculate the amount of storage used, and generally it is more useful information, because the record length impacts the amount of storage being used. To calculate the storage used by deleted records; multiply the number of deleted records by the record length of the file member that has deleted records. The record format for DSPFD will be QWHFDMBR. For example, use Query/400 to create a result field:


STG  MBNDTR *MBMXRL

Select Only Files That Have Deleted Records

Use the select records feature in Query/400 to select only files that have deleted records. There is no point in printing a report that includes files that have no deleted records. For example:


MBNDTR  GT  0

Sort Records and Run the Report

The most useful way to sort the report is in descending order by storage used. In that way, the deleted records using the most storage appear on the report first, where they are most noticeable. Run the query to get a report of physical files that have deleted records.

Authority for DSPFD

The AS/400 will provide information for only the files to which you have sufficient authority. You will need to ensure that you have enough authority to obtain information on all of the files in the library being analyzed. It is unlikely that you will be able to have access to the QSECOFR password every time you want to check the storage used by deleted records.

It is preferable to create a command and command-processing program to adopt QSECOFR authority. Create a new command and command-processing program called, for instance, DSPFDALL. Have the security officer compile this new program with USRPRF(*OWNER) and make the owner QSECOFR. When you use the command DSPFDALL, you will get information reported on all files in the requested library, but will not have permanent access to them or their contents.

Use the Reports

The files in which deleted records use the most disk storage appear first on your report, and only files with deleted records are included on the report.

Use this method of checking for storage used by deleted records frequently. Follow up by reorganizing the file members that have deleted records (command RGZPFM).

For many files, you can reorganize the file anytime that the file is not in use. However, very large files and/or files with many logical files attached will take a long time to reorganize you will need to schedule them carefully. Now, instead of using up disk space with deleted records, you can use it for more useful information. T < G