Troubleshooting
Problem
This document describes what RGZPFM does and goes through some of the primary options.
Resolving The Problem
What does a RGZPFM command do, and when should I run it?
The RGZPFM Command
The RGZPFM command can be run to remove deleted records from a table (physical file).
Removing deleted records can improve performance of programs that read a file sequentially. It can also help reduce the paging of data in from DASD to memory.
Generally, there is no benefit unless the number of deleted records exceed approximately 15 to 20 percent of the record count for the file.
RGZPFM has several parameters, but customers are usually most interested with 3 parameters.
ALWCANCEL - Allow cancel
RBDACCPTH - Rebuild access paths
KEYFILE - Key file
Let's do a very brief overview of these three.
ALWCANCEL
Ask yourself - if the RGZPFM runs longer than you anticipated, how does that affect your company?
You may have a very strong need to remove deleted rows from a table, but you very likely also have a limited maintenance window to get this work done.
If you choose ALWCANCEL(*NO) - it is harder to stop and you loose the work that was done if it did not complete far enough. The Default is *NO.
The RGZPFM Command
The RGZPFM command can be run to remove deleted records from a table (physical file).
Removing deleted records can improve performance of programs that read a file sequentially. It can also help reduce the paging of data in from DASD to memory.
Generally, there is no benefit unless the number of deleted records exceed approximately 15 to 20 percent of the record count for the file.
RGZPFM has several parameters, but customers are usually most interested with 3 parameters.
ALWCANCEL - Allow cancel
RBDACCPTH - Rebuild access paths
KEYFILE - Key file
Let's do a very brief overview of these three.
ALWCANCEL
Ask yourself - if the RGZPFM runs longer than you anticipated, how does that affect your company?
You may have a very strong need to remove deleted rows from a table, but you very likely also have a limited maintenance window to get this work done.
If you choose ALWCANCEL(*NO) - it is harder to stop and you loose the work that was done if it did not complete far enough. The Default is *NO.
For that reason, ALWCANCEL(*YES) may be a better choice. While the IBM i is working on your table, we will stop if the RGZPFM is ended.
ALWCANCEL(*YES) also allows you to restart where you left off if you did have to cancel. See TechNote N1014683 "RGZPFM Frequently Asked Questions" for more information.
Note that if you have variable length fields, this will not reclaim the variable length space.
RBDACCPTH
Most customers feel that this parameter ties in to the previously discussed ALWCANCEL(*YES).
Again if the RGZPFM is running longer than you'd like -and- you have to end it, do you need the indexes (logical files) built over that table to be immediately available? Or do you have time to rebuild them before they need to be used?
Default is *YES
ALWCANCEL(*YES) also allows you to restart where you left off if you did have to cancel. See TechNote N1014683 "RGZPFM Frequently Asked Questions" for more information.
Note that if you have variable length fields, this will not reclaim the variable length space.
RBDACCPTH
Most customers feel that this parameter ties in to the previously discussed ALWCANCEL(*YES).
Again if the RGZPFM is running longer than you'd like -and- you have to end it, do you need the indexes (logical files) built over that table to be immediately available? Or do you have time to rebuild them before they need to be used?
Default is *YES
ALWCANCEL(*No)
The access paths are invalidated before the reorganization and have to rebuild once the reorganization is done or canceled. This is the only way the access path will have less storage after the reorganization.
The more Access paths that are over the table being reorganized, the more time will be taken to maintain them during the execution of the RGZPFM. This is often a price people are willing to pay for avoiding the rebuild time if the reorganization is canceled.
KEYFILE
Is the order the rows of data in the table important to your applications? If so, you can use this parameter to influence the 'order' the rows in the table a physically located. You can also use value *RPLDLTRCD with ALWCANCEL to reduce the number of rows moved. Using RPLDLTRCD should only be used if you do not need to maintain arrival sequence.
Default is *NONE
The time it takes for a RGZPFM command to complete depends on many factors, but essentially it does the following steps. (Note: assuming default RGZPFM parameters)
| o | It invalidates all logical files built over the physical and compresses out the deleted records from the table. The speed of this step depends on your I/O configuration. If your system does not have twice the size of the original file available, the operating system will perform a rolling compression. |
| o | If the RGZPFM command uses the KEYFILE parameter, the run time will be dependent on how the records currently reside on DASD. The system will use an existing access path to read the data in the desired order. |
| o | The last part is access path maintenance. Each unique access path is rebuilt. The rule of thumb is 10,000 records per minute (worst case). |
If you want more control of the various steps in RGZPFM, then you could perform the steps manually.
| o | Ensure you have a valid copy of the physical file from a regular save. |
| o | Save the related logical files to tape. |
| o | CPYF with compress *yes to a new file in the same library. Copy from record 1 if you do not want the order to change. |
| o | Delete the related logical files. |
| o | Delete the original physical file. |
| o | Rename the copy of the physical file to the original file name. |
| o | Restore the logical files. The logical files will be rebuilt automatically via the QDBSRVxx jobs. Use the EDTRBDAP command to view the status of those rebuilds. |
A few other notes on RGZPFM.
a) If you do select ALWCANCEL(*YES), an additional LOCK parameter is presented.
This parameter becomes important with option ALWCANCEL(*YES) because now you can open up access to the physical file while the RGZPFM is running.
b) You can use the IBM i Navigator (iNav) to monitor the progress:
a) If you do select ALWCANCEL(*YES), an additional LOCK parameter is presented.
This parameter becomes important with option ALWCANCEL(*YES) because now you can open up access to the physical file while the RGZPFM is running.
b) You can use the IBM i Navigator (iNav) to monitor the progress:
www.ibm.com/support/pages/viewing-status-rgzpfm-through-ibm-i-access-client-solutions
c) If you are planning to reorganize more than one physical file, it is worth reviewing the logical access paths that exist over the physicals. The physical files might refer to logical files that are common to more than one physical file. If this is the case, it would be sensible to remove the logical file members at the start and add them once at the end. This ensures that each logical access path gets rebuilt only once and not each time an underlying physical is reorganized.
Here is the V7R5 URL for RGZPFM https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/rgzpfm.html
c) If you are planning to reorganize more than one physical file, it is worth reviewing the logical access paths that exist over the physicals. The physical files might refer to logical files that are common to more than one physical file. If this is the case, it would be sensible to remove the logical file members at the start and add them once at the end. This ensures that each logical access path gets rebuilt only once and not each time an underlying physical is reorganized.
Here is the V7R5 URL for RGZPFM https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/rgzpfm.html
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"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
6495428
Was this topic helpful?
Document Information
Modified date:
04 December 2024
UID
nas8N1010713