The Compress Deleted Records command moves all active records to the
front of a data base file member by re-using the deleted record
space. At the end of the command, all the deleted records will be at
the back end of the file. The companion tool is TRNDLTRCD to move
the 'end of data' marker after the last active record and reclaim
space for the system.
CPRDLTRCD can be effective when a large file exists and the use of
RGZPFM is inconvenient to use (such as a 24 x 7 application).
CPRDLTRCD can be used while the file is in use for production
CPRDLTRCD must be used against a physical file and requires the user
to have all rights to the file.
Files containing LOBs - either Binary Large Ojbects (BLOBS), or
Character Large Objects (CLOBs), are not supported.
A typical command would be:
The file would be read in arrival sequence order. A list of the
deleted records is built in the default file CPRDLTRCD in the TAAWORK
library (this allows for a restart without having to re-read all of
the deleted records). When an active record is read (after the first
deleted record is found), the active record is moved to the first
available deleted record slot. Status messages are sent periodically
to describe the number of records moved. A small spooled file with
summarized results is output.
Several options exist such as:
- Restarting the command - See Restart
- Providing a minimum deletion percentage - Default is 5%
- Ending after a specific number of moves
- Ending with ENDJOB OPTION(*CNTRLD)
- Using commitment control - See later discussion
Use with TRNDLTRCD
The Truncate Deleted Records (TRNDLTRCD) tool is the companion for
CPRDLTRCD. It will truncate the deleted records from the back of the
file which causes the 'end of data' marker to be moved after the last
active record. The member statistics (such as how many deleted
records exist would be updated. The truncated space will be returned
to the system if it is large enough (probably increments of 16MB).
Note that if CPRDLTRCD completes and then another user adds a record
to the end of the file before TRNDLTRCD can be run, there would be no
gain made by running TRNDLTRCD. You must coordinate the use of
CPRDLTRCD and TRNDLTRCD to achieve a payoff.
CPRDLTRCD can be run while other users are reading, updating, adding,
or deleting records to the same file. TRNDLTRCD requires an
exclusive lock on the file and member.
The following recommended sequence describes how to use the
combination of CPRDLTRCD and TRNDLTRCD. You may wish to add other
parameters to CPRDLTRCD.
** Submit the CPRDLTRCD command to batch as it will probably be a
long running function.
SBMJOB JOB(CPRDLTRCD) CMD(CPRDLTRCD FILE(xxx))
** Review the spooled output. The 'Final Results' section should
indicate that all deleted records are at the 'end of file'.
** Assuming few if any additional records have been added to the
file after the completion of CPRDLTRCD, run CPRDLTRCD again
interactively using the *RESTART option (described later) to
ensure that the file still has all deleted records at 'end of
file'. Using *RESTART allows the CPRDLTRCD to rapidly process
CPRDLTRCD FILE(xxx) FROMRCD(*RESTART)
** You should see a positive statement in the completion message
that all deleted records are at the 'end of file'.
** Allocate the file/member to ensure that no one is using it.
ALCOBJ FILE((xxx *FILE *EXCL mmm))
** Run CPRDLTRCD again to ensure that the deleted records are
still at the 'end of file'.
CPRDLTRCD FILE(xxx) FROMRCD(*RESTART)
** Run TRNDLTRCD to truncate the file back to the last active
record and return unused space to the system (only large
blocks of storage are returned such as a minimum of 16MB).
TRNDLTRCD will also request an *EXCL lock which is held by
** Check the file with DSPMBRD:
** It should show a deleted record count of 0.
** Deallocate the file:
DLCOBJ FILE((xxx *FILE *EXCL mmm))
** Clear the file named in the WORKFILE parameter (default is
CPRDLTRCD in TAAWORK).
Approach and Performance Considerations
The major factors that will influence performance are:
- The number of active records in the file that must be moved
- The number of access paths that must be updated
The program begins by attempting to read the record specified on the
'From record number' parameter. If the record exists, the program
loops until the first deleted record is sensed and the slot for this
record is noted. All deleted record slots are written to the file
named in the WORKFILE parameter (default is CPRDLTRCD in TAAWORK).
This is designed to allow for a restart (see Restart section). The
program continues to read until an active record is found. The
active record is moved to the slot of the first deleted record.
A move occurs by deleting the active record from its existing record
slot which will cause any access paths over the physical to remove a
reference to the record. The values from the record just deleted are
then written to the slot of the first deleted record. This also
causes any access paths to be updated.
Note that because the active record is deleted first, any duplicate
key considerations are avoided. However, there is an exposure that
another user could add the same key before the 'move' is completed.
There is also an exposure that another job may attempt to access the
record between the 'delete' and the 'add' in which case a 'record not
found' condition would occur.
Note also that the FIFO order in which the records were originally
written to the file is maintained for REUSEDLT(*NO) files. The
relative record numbers change, but the sequence is preserved.
If CPRDLTRCD is in operation and another job writes a record into a
deleted record slot, it is possible that CPRDLTRCD will attempt to
add a record to this same slot and fail because an active record
already exists. This could also occur if the file is specified as
The program attempts to account for this by checking to see if the
record slot is still deleted before writing a record to the slot. If
an active record exists, the next deleted record slot is used.
Ending the program
The CPRDLTRCD program may take a long time to run on a very large
file (unless the Restart function is used). Since it will not be
clear how long the job will take, you will probably want to submit
the command to run in batch during a slow period.
The program will end in one of three ways:
** When 'end of file' is reached.
** If you specify the MOVNBR parameter, the program will end
after the number of record 'moves' you specified has occurred.
The default is *NOMAX meaning the number of moves is not a
consideration to end the program.
** You can end the program by ending the job with ENDJOB
OPTION(*CNTRLD). The program is designed to listen for this
request and will end normally.
To restart the program, see the Restart discussion.
During the running of the program, the deleted record slot values
(the Relative Record numbers) are added to the file named in the
WORKFILE parameter (default is CPRDLTRCD in library TAAWORK). If the
program ends and you need to restart, the proper restart point is the
first record after the last deleted record that was found. Rather
than re-reading all of the deleted records again and rebuilding this
file, the FROMRCD parameter supports the special value *RESTART.
The normal use of CPRDLTRCD would be to begin with FROMRCD(1). Any
numeric value specified for FROMRCD causes the WORKFILE file to be
cleared. The program then builds the file of deleted record slots.
On any subsequent use of CPRDLTRCD for the same file, you should
The special *RESTART option tells the command to reuse the data in
the file named in the WORKFILE parameter. The last record in the
file is read to determine where the program should begin processing.
To prevent misuse of the *RESTART function, (such as as using a set
of deleted record slots from FILEA to cause a restart to FILEB),
several checks are made:
** The named WORKFILE must exist and contain records.
** At the completion of CPRDLTRCD, the member text description is
updated with the library/file/member last specified for the
FILE/MBR parameter. This is checked if *RESTART is specified
to ensure the same file is being used.
** If the file exists, it must have the format that is expected
** The file is allocated to prevent multiple use.
** The completion date and time of CPRDLTRCD is also stored in
the member description. The FILE/MBR specified cannot have
been restored after this date and time.
** The CPRDLTRCD file cannot have been restored since the last
change date/time of the FILE/MBR being compressed.
The Work file (WORKFILE parameter) defaults to CPRDLTRCD in TAAWORK.
If FROMRCD(n) is specified (not *RESTART) and the file does not
exist, it is automatically created.
Any file and library (except QTEMP) may be named. This allows you to
have multiple uses of CPRDLTRCD in operation.
The work file is not used by TRNDLTRCD.
When you no longer need the work file, you may clear the member or
delete the file. Because the *RESTART function allows a significant
performance gain, it is generally desirable to retain the work file
until you have successfully used TRNDLTRCD.
The default is to run using commitment control. This is the safest
solution because the function is moving each record by both deleting
and adding a new record.
To use commitment control, you must be journaling the file. If you
specified STRJRNPF IMAGES(*AFTER), the system will automatically
cause both the before and after image to occur in the journal while
commitment control is active.
Each 'move' operation requires:
2 - Reads for update
2 - Deletes
2 - Writes
The COMMITFREQ parameter determines the frequency in which commit
will be used. The default is after 20 moves have occurred. This
provides better performance than committing after each move, but is
not as safe. The safest solution is to use COMMITFREQ(1).
COMMIT(*NO) may be specified if you are not journaling the file. If
the job or system abnormally terminates while the function is
running, you may lose some records from the file.
CPRDLTRCD escape messages you can monitor for
TAA9892 No deleted records exist
TAA9893 No active records exist
TAA9894 COMMIT(*YES) specified, but file is not journaled
TAA9895 Percentage of deleted records less than MINDLTPCT
Escape messages from based on functions will be re-sent.
Command parameters *CMD
FILE The qualified name of the file to be compressed.
Only physical files are supported. The library
value defaults to *LIBL. *CURLIB may also be used.
MBR The member to be compressed. *FIRST is the default.
FROMRCD The Relative Record number in the file to start at.
1 is the default. The record does not have to exist
in the file (it may be a deleted record).
Any numeric value specified for this parameter will
cause the file named in the WORKFILE to be cleared.
This will cause the rebuilding of this file with the
Relative Record number of any deleted record slots
If a value is entered, it must be between 1 and
The special value *RESTART may be used if a
'restart' is required. During the running of
CPRDLTRCD, the file named in the WORKFILE parameter
is dynamically built with values that represent the
deleted record slots in your file. *RESTART will
re-use this file so that the deleted record slots
that were already determined do not have to be
re-determined. The program will begin processing at
the first record after the last deleted record slot
value found in the WORKFILE file.
If *RESTART is specified, several checks are made to
ensure the data in the WORKFILE file is for the same
file to be compressed. For example, the file must
have the correct format, it must contain records, it
must have been created from a prior use of the same
file/member named in the FILE/MBR parameters, etc.
See the Restart section for the checks that are
MOVNBR The 'Move number' value allows the program to end
after a number of active records have been moved.
The default is *NOMAX, meaning the program will end
when 'end of file' is reached.
Specifying a number allows the program to end after
a specific number of active records have been moved
closer to the beginning of the file. If a number is
entered, it must be between 1 and 99,999,999,999.
This parameter is intended for the case where you
have a very large file and cannot (or prefer not to)
complete the compression in one use of the command.
MINDLTPCT The minimum percentage of deleted records in the
file that must exist before the long running
operation begins. The default is 5% meaning the
file must have at least 5% deleted records. If not,
TAA9895 is sent as an escape message.
The intent of this parameter is to avoid careless
use of the command. For example, if only one
deleted record exists and it is at relative record
1, every active record in the file would be moved up
1 position. This would result in a great deal of
overhead and an insignificant gain.
If a value is entered, it must be between 0 and
COMMIT Whether to use Commitment Control. *YES is the
default which provides better recovery if the job or
system fails while the command is running.
Specifying *YES requires the file to be journaled
(TAA9894 is sent if not). *YES is the correct
choice if it is practical to journal the file or it
is already being journaled.
Use the COMMITFREQ parameter to determine the
frequency in which a 'commit' is performed.
*NO may be specified if the file is not journaled
and it is not practical to journal the file.
If *NO is specified and the job or system fails
while the command is running, you may lose data
records or have duplicate records in the file.
COMMITFREQ The number of 'record moves' that occur before a a
'commit' is issued. The default is 20 to provide a
better performance boost.
The safest solution is to specify 1 to cause a
'commit' for each 'record move'.
This parameter is ignored if COMMIT(*NO) is
WORKFILE The qualified name of the work file that is used to
store the deleted record slots found in the
file/member named in the FILE/MBR parameters. The
default is CPRDLTRCD in TAAWORK.
If the file exists, the user must have *ALL rights
to the file.
If FROMRCD(n) is specified (not *RESTART), the file
will be automatically created if it does not exist.
If created, the *PUBLIC profile is given all rights.
If the file already exists, it is cleared.
If FROMRCD(*RESTART) is used, the file must exist
and must match the criteria described in the FROMRCD
Any file name may be used. Any library may be used
except QTEMP (this prevents a batch job from
building the file and then having it deleted when it
is needed later).
When you no longer need the data in the file
described in WORKFILE, the file may be cleared or
Only physical files are supported.
Files containing Large Objects (LOBs) - either BLOBs or CLOBs - are
The user of CPRDLTRCD must have all rights to the file named in the
The user of CPRDLTRCD must have all rights to the file named in the
FILE parameter if it exists.
See the previous comments on COMMIT(*NO).
A maximum record size of 9999 bytes is supported.
There are restrictions discussed in the 'Approach' section.
There are restrictions discussed in the 'Restart' section.
The following TAA Tools must be on your system:
CHKOBJ3 Check object 3
CRTDUPPF Create duplicate PF
EDTVAR Edit variable
HLRMVMSG High level language remove message
RTVDBFA Retrieve data base file attributes
RTVFMT Retrieve data base format
RTVSYSVAL Retrieve system value
SNDCOMPMSG Send completion message
SNDESCMSG Send escape message
SNDSTSMSG Send status message
None, the tool is ready to use.
Objects used by the tool
Object Type Attribute Src member Src file
------ ---- --------- ---------- ----------
CPRDLTRCD *CMD TAADBJI QATTCMD
TAADBJIC *PGM CLP TAADBJIC QATTCL
TAADBJIR *PGM RPG TAADBJIR QATTRPG
TAADBJIP *FILE PF TAADBJIP QATTDDS
TAADBJIQ *FILE PF TAADBJIQ QATTDDS
TAADBJIS *FILE PF TAADBJIS QATTDDS
The TAADBJIP file is used as a model to create the file named on the
WORKFILE parameter to contain the deleted record numbers. TAADBJIP
is used to compile against and is overridden at run time.
The physical files TAADBJIQ and TAADBJIS are used to compile against.
TAADBJIQ is used if COMMIT(*NO) is specified. TAADBJIS is used if
COMMIT(*YES) is specified. At run time, these files are overridden
to the file named in the FILE parameter.