Admin Alert: Tips for Dealing with Deleted Records in AS/400 Files
December 1, 2004 Joe Hertvik
One of the problems with OS/400 databases is dealing with deleted records. By design, OS/400 doesn’t remove deleted records from a file; it only marks the records as deleted. Deleted records continue to occupy file space, and special processing is needed to truly clear deleted records from a file, reclaim their storage, and compress the file for greater efficiency. To that end, here are four tips for dealing with deleted records.
Tip 1: Reorganize large physical files to get rid of deleted records.
To check if a file has a lot of deleted record space, you can run the Display File Description (DSPFD) command, as follows:
DSPFD FILE(LIBRARY/FILE)
The bottom of this display shows the deleted record counts for each file member. If you see a large number of deleted records, you can eliminate the deleted records and compress the file by running a Reorganize Physical File Member (RGZPFM) command on the file, as follows:
RGZPFM FILE(LIBRARYNAME/FILENAME) KEYFILE(*NONE)
Running RGZPFM with the Key File (KEYFILE) parameter equal to *NONE removes the deleted records and compresses the active records in the file, so that all records are still stored according to their arrival sequence.
If you want to use RGZPFM to resequence your file according to the keyed sequence of the physical file’s access path, you can run the RGZPFM command with *FILE in the KEYFILE parameter:
RGZPFM FILE(LIBRARYNAME/FILENAME) KEYFILE(*FILE)
When run this way, the arrival sequence of the reorganized records will be changed to match the keyed sequence access path of the file. Reorganizing by key can also speed up file access, because it makes it easier to retrieve records by their keys. You can also reorganize the file according to a logical file member key sequence by entering the logical file name, library name, and file member name of an associated logical file in the KEYFILE parameter.
Be aware that when you reorganize according to a file key, it eliminates your ability to read the file in its original arrival sequence. Another downside is that, by default, RGZPFM will only reorganize the first member in a physical file. If you want to reorganize other members of the file, run RGZPFM with a member name in the Member name (MEMBER) parameter. You should also note that RGZPFM locks the file being reorganized during the entire time that RGZPFM is running, so no one else can be using the file during that time.
Tip 2: Set up your file to reuse deleted records.
Rather than retaining what is essentially empty file space for deleted records, you can change a physical file so that OS/400 reuses file space currently allocated for deleted records every time you insert a new record in the file. You can change an existing file so that it starts reusing deleted records by using the Change Physical File (CHGPF) command, like this:
CHGPF FILE(LIBRARYNAME/FILENAME) REUSEDLT(*YES)
Once executed, OS/400 will start reusing deleted records whenever it inserts a new record. But there are a few downsides to this technique. First, it won’t make all the deleted records in a file magically disappear. If you have a large number of deleted records in your target file, you may want to start using this parameter only after you reorganize, clear the file, or copy the records out of and back into the file again (see tip 4). Second, you will probably never bring down the deleted record count in a file to zero. This is because the ratio of added records to deleted or changed records is never a direct one-to-one count. My experience with REUSEDLT is that the file will still contain a number of deleted records, even after you make this change. Reusing deleted records only slows down the growth in deleted records; it doesn’t stop it. You will still need to clear or reorganize the file occasionally to bring the number of deleted records down to zero again.
Using REUSEDLT also destroys relative record processing for a file, because records are no longer added to the end of the file; they are inserted wherever there is empty space left over by deleted records. OS/400 will also stop you from turning on REUSEDLT if the physical file uses FIFO or LIFO duplicate key ordering or if the file has any logicals built over it that use FIFO or LIFO duplicate key ordering.
It’s also worth noting that you can only change this parameter if no process or job has a lock on the file you’re trying to modify. So for busy files that are constantly being used, you may need to find a time when no one is using the file to set this parameter.
Tip 3: Have OS/400 notify you when a file contains more than a certain number of deleted records.
If you don’t want to change your file to reuse deleted records, you can set the file up so that OS/400 tells you when the deleted records in a file member exceeds a certain percentage of the total records in that member. So if you wanted OS/400 to tell you when the deleted records in a member exceeded 25 percent of the total records in the file member, you would change the Max % deleted records parameter (DLTPCT) with a CHGPF command in this way:
CHGPF FILE(LIBRARYNAME/FILENAME) DLTPCT(25)
This change takes effect the next time the file is opened and closed. You can select any percentage between 1 and 100 percent, or you can set this parameter to *NONE (no tracking). When the number of deleted records pass the file target percentage, OS/400 will send a message to the system history log (QHST) that the file has reached its limit (OS/400 checks the percentage whenever the file is closed). This is an informational message only, but if you’re using a messaging product like Bytware’s MessengerPlus that is able to monitor the history log, you can have OS/400 page you when the file needs to be cleared or reorganized.
Tip 4: Consider using Copy File (CPYF) commands instead of reorganizing your physical file members.
This is a two-step process in which you first copy your file to a place in temporary storage by using a CPYF statement, similar to the following:
CPYF FROMFILE(Library/File Name) TOFILE(QTEMP/file name) MBROPT(*REPLACE) CRTFILE(*YES)
Notice that this particular statement creates an identical version of the file in the QTEMP library, where the temporary overhead created in duplicating your file will be automatically removed at sign-off. The CPYF statement also skips deleted records in the original file so that the QTEMP version contains only live records in arrival sequence, which is exactly what you want.
|
After creating your temporary hold file, you can copy it back over the original with another CPYF statement that looks something like this:
CPYF FROMFILE(QTEMP/File Name) TOFILE(Library/file name) MBROPT(*REPLACE) CRTFILE(*NO)
Besides copying the records from your hold file back to the original, the only difference between this CPYF statement and the first statement is that you specify *NO in the Create File (CRTFILE) parameter, because the target file already exists.
Like RGZPFM, you need to prevent updates to your live file while this technique is running. The other downside to this technique is that you have to run two CL commands instead of the single RGZPFM statement that you use to reorganize a file member, which in some instances may take a little longer.
