The Reorganize Physical File Member (RGZPFM) command removes deleted records from (compresses) one member of a physical file in the database, and it optionally reorganizes that member.

What we used to say about RGZPFM

It is generally not recommended to perform an RGZPFM (Reorganize Physical File Member) operation on IBM i files while they are in use by other applications or users. This is because the reorganization process requires exclusive access to the file in order to ensure that the data is consistent and accurate.

If a file is in use by other applications or users, it may not be possible to obtain exclusive access to it, and attempting to do so could cause the applications or users to encounter errors or other issues.

Therefore, it is best to schedule the RGZPFM operation for a time when the file is not in use, because you will be requiring exclusive access.

What we NOW say about RGZPFM

* Since 2022 we have been able to reorganize files while they are in use *

With IBM’s new and enhanced Reorganize Physical File Member (RGZPFM) command we can reorganize any file while it is in use.

RGZPFM FILE(MYLIBRARY/BIGFATFILE) ALWCANCEL(*YES)

RGZPFM usings Reorganize While Active

• Reorganize While Active can only be performed on files that are journaled
• The “Reorganize While Active” option only releases unused space for fixed-length fields. It does not release space used for BLOBs (Binary Large Objects), CLOBs (Character Large Objects), or variable-length fields, which is managed separately by the system as “AUX” space.
• To reclaim unused AUX space, you can use the RGZPFM command with the ALWCANCEL parameter set to *NO. This will cancel any allocations for unused AUX space and release it back to the system. It is important to note that this should be done with caution, as it may cause performance issues or other problems if the file is in use by other applications or users.

When using the “Reorganize While Active” option, it is important to understand how it works. The following parameters are relevant:

• Rebuild access paths: Set to *NO, meaning that access paths are maintained while the RGZPFM is running.
• Allow cancel: Set to *YES, allowing the reorganization process to be cancelled if necessary.

It is also important to note that the LOCK state parameter determines whether users can read or update the data while the RGZPFM ALWCANCEL(*YES) is running.

When RGZPFM is used with the option to RBDACCPTH(YES), it will render a faster reorganization of your file; however, the rebuild of the access paths of the physical file has been reorganized will be very CPU-intensive. See Question 2 below. RBDACCPTH(NO) will maintain all logical files as the physical file is reorganized.

To speed up the rebuild step, you should allocate as much memory as possible to memory pool *BASE prior to starting the RGZPFM and to leave as much CPU as possible for this task. For a faster reorganization of the physical file, you should consider using the KEYFILE(*RPLDLTRCD) option with RGZPFM. If the file is “Reuse deleted records” REUSEDLT(*YES) this option is safe to use. Check via DSPFD. With this option, the deleted records space in the beginning of the file will be replaced with valid records from the end of the file. If the file is REUSEDLT(*NO), note that using this option will not preserve the arrival sequence and will not preserve the FIFO (first in, first out) or LIFO (last in, first out) order among duplicate keys.

Use With Caution

It can take a long time ( aka IT WILL TAKE A LONG TIME )

During the reorganization process, rows are moved (deleted and inserted) in order to relocate all deleted records to the bottom of the file. This can be observed in the journal as a delete and an insert for each row that needs to be moved. This can result in a large amount of journal activity and an increase in the size or number of journal receivers. These journal entries must be replayed on any target system.

Consider the amount of Journal Receiver space that will be used

Commitment control is used when the job is deleting and reinserting rows during the reorganization process. The system moves a set of rows before a commit is issued, with the number of rows moved per transaction varying between 1 and 4000. The system determines the number of rows to move before a commit is issued.

File Reorg while active may impact system performance

Production jobs that are running with an isolation level of Cursor Stability or higher may experience delays while waiting for rows that have been moved to be committed. The default record wait timeout is 60 seconds, but if this value has been modified to be smaller, lock timeout errors may occur.

To reduce the likelihood of row lock waits by production jobs, it is recommended to use the OVRDBF FILE(FILE) WAITRCD(1) command before the RGZPFM command is issued. This will reduce the record wait time for the reorganization job and limit the time that rows are locked. If the job is unable to obtain a record lock within the 1-second wait time, it will skip that row and move on. This will also reduce the number of rows moved per transaction.

Monitor with WRKSYSACT while in progress

Monitor the system during the RGZPFM operation and to adjust the record wait time as needed to avoid lock timeout errors.

It is important to note that reducing the record wait time may increase the risk of data inconsistency or other issues if the reorganization job is interrupted or cancelled before it is completed. It is recommended to carefully consider the trade-offs and to take appropriate precautions to ensure the integrity of the data.

The Taste Test

Obviously its a good idea to test the RGZPFM operation with a small sample of data before running it on a large scale, to ensure that it is working as expected and to identify any potential issues.

Backup, backup and backup

Have a backup or recovery plan in place, in case of any issues or errors during the RGZPFM operation. It is also important to monitor the system after the RGZPFM operation is completed, to ensure that it has completed successfully and that the file is functioning as expected.

And Finally…

Periodically reorganize the file to maintain its performance and integrity. The frequency of the RGZPFM operation will depend on the size and usage of the file, as well as the performance requirements of the system.