Reorganize Physical File Mbr (RGZPFM)

Where allowed to run: All environments (*ALL)
Threadsafe: Conditional

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