Reorganize Physical File Mbr (RGZPFM)
| Where allowed to run: All environments (*ALL) Threadsafe: Conditional |
Parameters Examples Error messages |
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.
If a keyed file is identified in the Key file (KEYFILE) parameter, the system reorganizes the member by changing the physical sequence of the records in storage to either match the keyed sequence of the physical file member's access path, or to match the access path of a logical file member that is defined over the physical file. Reorganization can decrease file processing time when a program is reading sequentially through a keyed physical file or through a keyed logical file.
When the member is reorganized and KEYFILE(*NONE) is not specified, the sequence in which the records are actually stored is changed, and any deleted records are removed from the file. If KEYFILE(*NONE) is specified or defaulted, the sequence of the records does not change, but deleted records are removed from the member. Optionally, new sequence numbers and zero date fields are placed in the source fields of the records. These fields are changed after the member has been compressed or reorganized.
Notes:
- If you cancel this command, the system rebuilds any access paths that are not maintained during the reorganize. If ALWCANCEL(*NO) is specified, any updates to a physical file member with a unique access path over it are prevented until the access path is completely rebuilt.
- If you cancel this command and ALWCANCEL(*YES) is specified, the reorganize will be partially complete. Subsequently, another reorganize with the same parameters may be able to continue from where the last reorganize ended.
- The RGZPFM command ignores all file overrides that are currently in effect for the job. The file names specified on the FILE and KEYFILE parameters identify the files actually used in the reorganize operation, regardless of overrides that may exist for these files.
Restrictions:
- During the reorganization of a physical member, the file being reorganized is locked. The Lock state (LOCK) parameter can be specified to indicate how much concurrent access to the physical file member should be allowed.
- The user needs object operational authority, object management or alter authority, all data authority to the physical file containing the member to be reorganized, and execute authority to the library. The user also needs object operational authority to the file specified on the KEYFILE parameter and execute authority to the library.
- In multi-threaded jobs, this command is not threadsafe for distributed files and fails for distributed files that use relational databases of type *SNA. This command is also not threadsafe and fails for Distributed Data Management (DDM) files of type *SNA.
| Top |
Parameters
| Keyword | Description | Choices | Notes |
|---|---|---|---|
| FILE | Data base file | Qualified object name | Required, Positional 1 |
| Qualifier 1: Data base file | Name | ||
| Qualifier 2: Library | Name, *LIBL, *CURLIB | ||
| MBR | Member | Name, *FIRST, *LAST | Optional, Positional 2 |
| KEYFILE | Key file | Single values: *NONE, *FILE, *RPLDLTRCD Other values: Element list |
Optional |
| Element 1: Logical file | Qualified object name | ||
| Qualifier 1: Logical file | Name | ||
| Qualifier 2: Library | Name, *LIBL, *CURLIB | ||
| Element 2: Member | Name | ||
| RBDACCPTH | Rebuild access paths | *YES, *OPTIMIZE, *NO | Optional |
| ALWCANCEL | Allow cancel | *NO, *YES | Optional |
| LOCK | Lock state | *EXCL, *EXCLRD, *SHRUPD | Optional |
| FROMRCD | From record | Unsigned integer, *START, *PRVRGZ | Optional |
| SRCOPT | Source update options | Single values: *SAME Other values (up to 2 repetitions): *SEQNBR, *DATE |
Optional |
| SRCSEQ | Source sequence numbering | Element list | Optional |
| Element 1: Starting sequence number | 0.01-9999.99, 1.00 | ||
| Element 2: Increment number | 0.01-9999.99, 1.00 | ||
| RCDFMT | Record format | Name, *ONLY | Optional |
| Top |
Data base file (FILE)
Specifies the physical file whose member is to be reorganized.
This is a required parameter.
Qualifier 1: Data base file
- name
- Specify the name of the physical file.
Qualifier 2: Library
- *LIBL
- All libraries in the library list for the current thread are searched until the first match is found.
- *CURLIB
- The current library for the thread is used to locate the file. If no library is specified as the current library for the job, the QGPL library is used.
- name
- Specify the name of the library to be searched.
| Top |
Member (MBR)
Specifies the member to be reorganized.
- *FIRST
- The first, or the only, member in the file is reorganized.
- *LAST
- The last, or the only, member in the file is reorganized.
- name
- Specify the name of the file member to be reorganized.
| Top |
Key file (KEYFILE)
Specifies whether the physical file member has its arrival sequence changed to match its keyed sequence, is reorganized in the sequence of a logical file member, or is not reorganized. If this parameter specifies a multiple-format logical file and member, the Record format (RCDFMT) parameter must also be specified.
Note: Join logical files, or an SQL Index with a WHERE clause cannot be specified as key files, and a logical file in this parameter is not allowed to have a select/omit access path.
Single values
- *NONE
- The member is not reorganized; it is only compressed by having deleted records removed.
- *RPLDLTRCD
- The member will be reorganized by replacing deleted records at the start of the file with valid records from the end of the file. If the records must exactly match the current arrival sequence, do not use *RPLDLTRCD.
- *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.
Element 1: Logical file
Specify the name and library of the logical file associated with the physical file member.
Qualifier 1: Logical file
- file-name
- Specify the name of the logical file to use as the key file.
Qualifier 2: Library
- *LIBL
- All libraries in the job's library list are searched until the first match is found.
- *CURLIB
- The current library for the job is used to locate the file. If no library is specified as the current library for the job, QGPL is used.
- library-name
- Specify the name of the library to be searched.
Element 2: Member
- name
- Specify the member of the logical file whose sequence is used to reorganize the physical file member.
| Top |
Rebuild access paths (RBDACCPTH)
Specifies whether to rebuild or maintain any valid access paths (other than an access path specified as the KEYFILE or a MAINT(*REBLD) access path) over the member.
- *YES
- Access paths will be rebuilt synchronously at the end of the reorganize operation.
- *OPTIMIZE
- Access paths will either be rebuilt asynchronously at the end of the reorganize operation, or maintained during the reorganize, based on which method will result in the access paths being rebuilt the fastest.
- *NO
- Access paths will be maintained during the reorganize.
If *NO is specified, ALWCANCEL(*YES) must also be specified.
| Top |
Allow cancel (ALWCANCEL)
Specifies whether to allow the reorganize to be canceled.
- *NO
- The reorganize cannot be canceled. If the job ends and the reorganize of the data has not finished, any changes up to the point of the failure are discarded.
If *NO is specified, KEYFILE(*RPLDLTRCD) and RDBACCPTH(*NO) must not be specified, and LOCK(*EXCL) must also be specified.
- *YES
- The reorganize can be canceled. If a reorganize is canceled before it is finished, a subsequent reorganize with the same parameters will typically continue the reorganize from where it ended. If the number of changes that have occurred since the reorganize was canceled is too large, the reorganize may be restarted rather than continued.
| Top |
Lock state (LOCK)
Specifies the lock to acquire on the physical file member. The lock state will determine how much concurrent access is allowed by other jobs during the reorganize.
- *EXCL
- An exclusive lock is acquired. No concurrent access to the physical file member is allowed from another job.
- *EXCLRD
- An exclusive allow read lock is acquired. Concurrent read access to the physical file member is allowed from another job.
If *EXCLRD is specified, ALWCANCEL(*YES) must also be specified.
- *SHRUPD
- A shared update lock is acquired. Concurrent update, delete, and insert access to the physical file member is allowed from another job.
If *SHRUPD is chosen, the resulting order of the records may not exactly match what was requested on the KEYFILE keyword. The records will be reorganized to closely match the specified order, but concurrent update, delete, and insert operations will cause some records to not be reorganized. If the records must exactly match the arrival sequence (*NONE) or a keyed file sequence, do not use *SHRUPD.
At the end of the reorganize, an attempt is made to remove deleted records at the end of the file and return storage to the system. If *SHRUPD is specified, it is possible that a concurrent insert that occurs just prior to removing the deleted records may prevent the removal of some or all of the deleted records at the end of the file. In this case, the FROMRCD keyword can be used on a subsequent reorganize to attempt the removal of the deleted records without reorganizing the the entire file again.
If *SHRUPD is specified, ALWCANCEL(*YES) must also be specified.
Note: Additional details about lock states can be found in the CL topic collection in the Programming category in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/.
| Top |
From record (FROMRCD)
Specifies which records in the file will be reorganized. Only records from the specified record to the end of the file will be reorganized.
- *START
- All records in the file will be reorganized.
- *PRVRGZ
- If the previous reorganize of this file was unable to remove some or all of the deleted records, the reorganize will begin at the record that would have been the last record in the file if a concurrent insert had not prevented the deleted records from being removed.
If the previous reorganize completed normally and was able to remove the deleted records, the reorganize will begin with the first record in the file.
*PRVRGZ is ignored if the reorganize is continued from a previously canceled reorganize.
If *PRVRGZ is specified, ALWCANCEL(*YES) must be specified and either KEYFILE(*RPLDLTRCD) or KEYFILE(*NONE) must be specified.
- 1-4294967288
- Specifies the record number of the first record to be reorganized.
If a record number greater than 1 is specified, ALWCANCEL(*YES) must be specified and either KEYFILE(*RPLDLTRCD) or KEYFILE(*NONE) must be specified.
| Top |
Source update options (SRCOPT)
Specifies, for physical source files only, whether the member places new numbers in the sequence number field, places zeros in the date field, or changes both fields. Changes occur after the records are compressed or reorganized.
Single values
- *SAME
- The sequence number field and date field of records do not change.
Other values
- *SEQNBR
- The records have a new sequence number placed into the sequence number field. The SRCSEQ parameter specifies a start value and a value to be added.
- *DATE
- The records have a null date (000000) placed in the date field.
| Top |
Source sequence numbering (SRCSEQ)
Specifies, only when *SEQNBR is also specified on the SRCOPT parameter, the sequence number that is given to the first record in the source file member and the increment value that is used to renumber all other records in the member. If the member is renumbered but this parameter is not specified, 1.00 is assumed for sequence number and increment value.
Element 1: Starting sequence number
- 1.00
- The first source record in the member has a sequence number of 0001.00.
- 0.01-9999.99
- Specify the sequence number of the first source record in the member. A whole number of up to 4 digits or a fraction of up to 2 digits can be specified. If the starting value contains a fraction, a decimal point must be used.
Element 2: Increment number
- 1.00
- The source records are renumbered in the member with whole number increments of 1 (for example, 1.00, 2.00, 3.00...).
- 0.01-9999.99
- Specify the increment value for renumbering all source records following the first record. A whole number of no more than four digits or a fraction of no more than two digits can be specified. If the increment value contains a fraction, a decimal point must be used.
For example, if SRCSEQ(5000 10) is specified, the first record in the reorganized member is numbered 5000.00, the second is 5010.00, the third is 5020.00, and so on. If SRCSEQ(*N .25) is specified, the records are numbered 1.00, 1.25, 1.50, 1.75, 2.00, and so on. If a starting value of .01 and an increment value of .01 are specified, there are 999,999 unique sequence numbers possible. If the maximum sequence number of 9999.99 is reached, the remaining records are also assigned the sequence number 9999.99.
| Top |
Record format (RCDFMT)
Specifies the record format name if the physical file member is reorganized in the sequence of a multiple-format logical file.
- *ONLY
- The logical file specified by the Key file (KEYFILE) parameter has only one record format. That format is used to reorganize the physical file member.
- name
- Specify the name of a record format in the multiple-format logical file that is used to reorganize the physical file member.
| Top |
Examples
Example 1: Reorganizing by Deleting Records
RGZPFM FILE(PAYROLL) MBR(MBR1)
This command compresses member MBR1 of the PAYROLL file by removing the deleted records from the file member.
Example 2: Reorganizing by Replacing Deleted Records
RGZPFM FILE(PAYROLL) MBR(MBR1) KEYFILE(*RPLDLTRCD)
ALWCANCEL(*YES) LOCK(*EXCLRD)
This command compresses member MBR1 of the PAYROLL file by replacing deleted records at the start of the file with valid records from the end of the file. The command may be canceled and other jobs are allowed to read data from the PAYROLL file while the reorganize is in progress.
Example 3: Reorganizing in Keyed Sequence
RGZPFM FILE(QCLSRC) MBR(CLMBR2) SRCOPT(*SEQNBR *DATE)
KEYFILE(*FILE) SRCSEQ(1.00 .25)
This command reorganizes the member CLMBR2 of the CL source file QCLSRC in keyed sequence, with the sequence number field used as the key. The reorganized member has new sequence numbers (starting at 1.00 and incrementing by .25) and a null date (000000) placed in all records when the original member is reorganized.
| Top |
Error messages
*ESCAPE Messages
- CPF2981
- Member &3 file &1 in &2 not reorganized.
- CPF3135
- Access path for member &2 already in use.
- CPF32B8
- Distributed file error, reason code &3.
- CPF32CF
- Distributed file error, reason code &3.
- CPF32C3
- Distributed file error, level ID mismatch
- CPF9801
- Object &2 in library &3 not found.
- CPF9809
- Library &1 cannot be accessed.
- CPF9810
- Library &1 not found.
- CPF9820
- Not authorized to use library &1.
*NOTIFY Messages
- CPF2985
- Source sequence numbers exceeded maximum value allowed. (G C)
| Top |