 |
| RGZPFM - Reorganize physical file members in AS400 |
Introduction
RGZPFM is a CL command used to remove deleted records from a table (DDS physical file).
Example
Just take a simple example to understand the scenario about when to use this RGZPFM command to reorganize physical file members.
We have a DDS PF named PF20 in library EASYCLASS1 having the below fields and records. Just select the records using the below SQL query and we will see below table record for RRN 1 till 4 present in the file.
SELECT rrn(a),a.* FROM PF20 a PF20 file records
| RRN | IDN | NAME | ADDR1 | ADDR2 |
|---|---|---|---|---|
| 1 | 4 | NAME4 | ADDRESS4 | PART2 |
| 2 | 4 | NAME4 | ADDRESS4 | PART2 |
| 3 | 4 | NAME4 | ADDRESS4 | PART2 |
| 4 | 4 | NAME4 | ADDRESS4 | PART2 |
let's delete the record for RRN 3 from file PF20 in library EASYCLASS1
DELETE FROM EASYCLASS1/PF20 A WHERE rrn(a) = 3
Record with RRN 3 has been deleted from the file and we are left with RRN 1,2 and 4
| RRN | IDN | NAME | ADDR1 | ADDR2 |
|---|---|---|---|---|
| 1 | 4 | NAME4 | ADDRESS4 | PART2 |
| 2 | 4 | NAME4 | ADDRESS4 | PART2 |
| 4 | 4 | NAME4 | ADDRESS4 | PART2 |
Let's insert the new record in file PF20 in library EASYCLASS1
INSERT INTO EASYCLASS1/PF20 VALUES(1, 'TEST', 'TEST', 'TEST')
A new record gets inserted with RRN 5
Let's select the records from file PF20 in library EASYCLASS1
SELECT rrn(a),a.* FROM PF20 a
Record with RRN 5 has been inserted into the file
| RRN | IDN | NAME | ADDR1 | ADDR2 |
|---|---|---|---|---|
| 1 | 4 | NAME4 | ADDRESS4 | PART2 |
| 2 | 4 | NAME4 | ADDRESS4 | PART2 |
| 4 | 4 | NAME4 | ADDRESS4 | PART2 |
| 5 | 1 | TEST | TEST | TEST |
We can observe that RRN 3 was freed once that record gets deleted. However, that storage space is still not in use since the next inserted RRN was 5. So, once we perform RGZPFM then only we will be able to reuse that RRN 3 or reuse that freed space.
RGZPFM syntax
once pressing the ENTER the command gets executed and Data in member PF20 is reorganized.
The RRNs of the table PF20 are reorganized successfully
| RRN | IDN | NAME | ADDR1 | ADDR2 |
|---|---|---|---|---|
| 1 | 4 | NAME4 | ADDRESS4 | PART2 |
| 2 | 4 | NAME4 | ADDRESS4 | PART2 |
| 3 | 4 | NAME4 | ADDRESS4 | PART2 |
| 4 | 1 | TEST | TEST | TEST |
RGZPFM important parameters
RGZPFM command has many parameters out of which let's point out some important ones.
- ALWCANCEL - Allow cancel (*NO and *YES) - *YES is preferred and the default is *NO.
- RBDACCPTH - Rebuild Access paths (Default is *YES)
- KEYFILE - Key file (Default is *NONE)