News
Abstract
This document summarizes the main options on RGZPFM.
The Reorganize Physical File Member (RGZPFM) command removes deleted records (compresses) from one member of a physical file in the database.
Content
RGZPFM has several options that affect the run time and ending result.
The parameters you want depend on the result you need.
This document provides a high-level review of the main options:
RBDACCPTH - Rebuild access paths
ALWCANCEL - Allow cancel
KEYFILE - Key file
Allow cancel
*NO / Rebuild Access path *YES (this is the default)Locking: requires an exclusive lock on the file during the
RGZPFM.What does it do? Makes a copy of the file and will rebuild access paths at the end.
Extra space needed: Disk space needed to hold a copy of the file (minus deleted record space).
Benefits: (1) Only option to return variable length and LOB (Large objects) column storage.
(2) Can be faster in some cases.
Time needed to run: Can be estimated by running a
Time needed to run: Can be estimated by running a
CPYF and rebuilding access paths over the the CPYF version.Cancel before completion results: (1) If ended during the copy phase, all work is lost and access paths still need to be rebuilt.
(2) If ended during rebuilding access path phase - the access path that is currently being rebuilt will need to start over but the deleted records have been removed.
(2) If ended during rebuilding access path phase - the access path that is currently being rebuilt will need to start over but the deleted records have been removed.
Access paths that were not rebuilt will need to be rebuilt.
Access paths: Dropped at the beginning and rebuild at the end - before
RGZPFM completes. They are not usable until they finish being rebuilt.Do not use:
(1) You need access to the physical file and logical files before the
RGZPFM can complete.(2) You cannot allocate the required time for the
RGZPFM to complete.(3) You do not have disk space to hold a copy of the file.
Allow cancel
*YES / Rebuild Access paths *NOLocking: (1) If
(2) If
(3) If
(4) If
What does it do? Moves the data row by row (system deletes a row and immediately insert it in a different position).
Extra space needed: Journal receiver(s) will be much larger than normal.
LOCK parameter is set to *EXCL deleted fixed length will be returned if RGZPFM is allowed to complete. (2) If
LOCK parameter is set to *EXCLRD, user jobs can prevent some rows from being moved and not all deleted fixed length can be returned.(3) If
LOCK parameter is set to *SHRUPD and users insert rows - space might not be returned.(4) If
LOCK parameter is set to *SHRUPD jobs need to handle rows as they are deleted /unavailable for a short time while data is being moved.What does it do? Moves the data row by row (system deletes a row and immediately insert it in a different position).
Extra space needed: Journal receiver(s) will be much larger than normal.
Benefits: (1) Physical file and logical files are usable during the
RGZPFM.(2) Can be cancelled and restarted later.
Time needed to run: Cannot be estimated.
Cancel before completion results: No effects - physical file and logical files are immediately usable.
Bonus: running the exact same command later results in starting where the previous
RGZPFM was cancelled.Access paths: Rebuild access paths to
*NO means they will be maintained during RGZPFM.Other notes:
(1) You might need to run this command several times.
(2) Seen more with files that have
(3) Message is displayed if less storage was given back than estimated.
(2) Seen more with files that have
DSPFD REUSEDLT (Replace deleted rows) to *NO.(3) Message is displayed if less storage was given back than estimated.
(4) Journal Receivers will grow large as we insert and delete rows. Each move is a delete and insert.
Consider replication software and replication with the extra journal receiver data.
Consider replication software and replication with the extra journal receiver data.
Key file (
KEYFILE)*NONE Arrival sequence is kept with Key File parameterThis process is done by starting at the top and moving every row up after the first deleted row is found.
This can mean many rows are moved.
*RPLDLTRCD Arrival sequence is not kept.This process is done by starting at the bottom and move valid rows in the deleted rows above.
- only need to move the number of deleted rows (or often less) than the file has.
*FILE For a physical file member having a keyed sequence access path, the arrival sequence of the records in the member is changed to match their keyed sequence.Other considerations and references:
RGZPFM can use Symmetric Multiprocessing (SMP) which is CPU intensive.Symmetric Multiprocessing (SMP) can also be used in rebuilding of the access paths that can also take up temp storage.
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001hrpAAA","label":"IBM i Db2-\u003ERGZPFM - Reorganize Physical File"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Historical Number
N1021958
Was this topic helpful?
Document Information
Modified date:
06 April 2023
UID
nas8N1021958