Troubleshooting
Problem
This document explains when space from deleted records is given back during the execution of
RGZPFM.RGZPFM has several options that affect the run time and ending result.The parameters you want depend on the result you need.
Resolving The Problem
RGZPFM ALWCANCEL(*NO) RDBACCPTH(*YES) must be used to return auxiliary space and fixed length space of a file.Auxiliary space is used for LOB and Variable Length fields for any data beyond what is allocated.
For all of the auxiliary space to be returned, there needs to be at least one deleted record in the file.
RGZPFM ALWCANCEL (*NO) will create a copy of the file, so one needs at least the same amount of space of the original file.To get an estimate of the auxiliary space in a file, run the following query:
select TABLE_NAME, DATA_SIZE, VARIABLE_LENGTH_SIZE from QSYS2.SYSPARTITIONSTATwhere TABLE_SCHEMA = '-your library name-' and TABLE_NAME = '-your table name-'
Under the
VARIABLE_LENGTH_SIZE column, one can see the physical space taken up by the variable length data. Divide the number you see there by x1000000 (16,777,216) for a rough estimate of how many segments are being used. The maximum allowed is xFFFD (65533).RGZPFM ALWCANCEL(*YES) can only return the fixed length space portion of the file.As the
RGZPFM ALWCANCEL(*YES) is running, it is moving the deleted rows to the end of the file and adding JRN entries to the journal.These entries should be replayed on any target systems.
At the end of the
RGZPFM ALWCANCEL(*YES), we give fixed length space back.There are times if one has users using the file that we can not give the space back.
When this happens, one can wait for when the file is not used and run
RGZPFM ALWCANCEL(*YES) again.This should run very quickly and give all the fixed length space portion back.
ALWCANCEL(*YES) truncates space back to a 16 MB boundary, so you could have up to 16 MB of extra space in the file.In contrast,
ALWCANCEL(*NO) will only allocate the space that is needed.Tables with LOB columns will not always be able to get to zero deleted rows.
A deleted row may be kept for each segment to avoid going across a segment boundary.
Note:
The
A deleted row may be kept for each segment to avoid going across a segment boundary.
Note:
Reuse Deleted Records REUSEDLT(*YES) via DSPFD only refers to fixed length space portion.The
IBM i always tries to reuse and manage the auxiliary space of the file, regardless of DSPFD settingsRelated Information
[{"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
N1012502
Was this topic helpful?
Document Information
Modified date:
24 November 2023
UID
nas8N1012502