IBM Support

RGZPFM - Basics on Reorganize While Active

Troubleshooting


Problem

This document provides basic information on RGZPFM's Reorganize While Active.
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.

Resolving The Problem

This document provides basic information on Reorg While Active option, RGZPFM ALWCANCEL(*YES) + LOCK parameters.
Review Technote RGZPFM options and comparison.

NOTE1: Reorganize While Active requires journaling.

NOTE2: Reorganize While Active removes unused fixed length only. Space used for BLOBs, CLOBs and variable length fields not included in the allocated space, will not be returned. This is referred to as the AUX space and managed by the system outside of the RGZPFM command. The only way to regain unused AUX space is RGZPFM ALWCANCEL *NO.

When running Reorg While Active, it is important to understand how this is being implemented.

First, the following parameters come into play:

Rebuild access paths. > *NO (meaning they are maintained while the RGZPFM is running)
Allow cancel. . . . . > *YES

Note the LOCK state parameter determines whether users can read the data or update the data while the RGZPFM ALWCANCEL(*YES) is running.

The reorg job is moving rows (deleting and inserting rows). This is being done to get all the deleted records at the bottom of the file. You see this in the journal by seeing a delete and an insert for every row that needs to be moved. This can cause a large amount of journal activity and an increase to journal receiver size or number of journal receivers generally expected. These journal entries need to be replayed on any target system.

Commitment control is used when the job is deleting and reinserting the rows. It also moves a set of rows before a commit is issued. The number of rows moved per transaction varies 1 - 4000. It is up to the system to adjust how many rows it moves before a commit is issued. Production jobs that are running with an isolation level of Cursor Stability or higher might wait for rows that have been moved until the commit is issued. The default record wait timeout is 60 seconds. If the record wait time has been modified to be much smaller, lock timeout errors are possible.

A suggestion to reduce the possibility of row lock waits by production jobs is to have the reorg job issue OVRDBF FILE(FILE) WAITRCD(1) before RGZPFM is issued. This will reduce the record wait time the reorg job is waiting to lock a row and limit the time the set of records are locked. If the job is unable to get a record lock during the 1-second wait time, it skips that row and moves on.  It also reduces the number of rows per transaction.

When using *SHRUPD lock, it is important to understand how production jobs inserting into the file affects the RGZPFM job and its ability to return space. After the reorganize job runs through the file one time, it will look to see whether any additional rows were inserted at the end of the file since the reorg started. The job notes what rows need to be moved and try to move them to get as much space returned as possible. If a row gets inserted at the end of the file after the last rows that needed to be moved were noted, the system will not be able to give back as much space. More activity on the file (inserts) can prevent space being returned. Files set-up with Reuse Deleted Records *NO requires all inserts to be placed at the end of the file. This increases the chance of limited space being returned. Files that are set up to Reuse Deleted Records *YES (check via DSPFD) have a greater chance of production jobs inserting rows in the middle of the file and not impacting the RGZPFM job. There is still a chance that rows can get inserted at the end and prevent space being returned even with Reuse Deleted Records turned on.

For this reason, you might need to run the RGZPFM several times in order to see space returned. In some cases, a maintenance window is needed to run RGZPFM with no inserts happening. If this is the case, let the reorg run during the week and move as much as it can and do much of the work. You should perform this work as close to the maintenance window as you can.  If the job ends early, start another one. Then, during the downtime, the reorg has less work to do, and downtime might be decreased. There is no way to get a perfect estimate how long the RGZPFM might run.

The system attempts to truncate any space at the end of the file at the start of the reorg and when it completes normally or cancelled before it finishes.

Use the journal to determine activity that is occurring on the file while the RGZPFM is running (DSPJRN).
Reorg while active (ALWCANCEL(*YES) may not remove deleted records in the first data segment.  Therefore, it is not uncommon to see deleted records in a file if the data portion of the file (i.e. not including indexes, etc) is 16MB in size or less after RGZPFM completes.

In addition,  you can use IBM i Navigator via Technote N1011091 "Viewing Status of RGZPFM through IBM i Navigator"

NOTE: Message CPF503E - User-defined function error on member &4 is normal if a user cancelled the RGZPFM.

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Db2 for i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

N1011025

Document Information

Modified date:
18 January 2023

UID

nas8N1011025