TAA Tools
CPRDLTRCD       COMPRESS DELETED RECORDS               TAADBJI

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
functions.

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:

            CPRDLTRCD   FILE(xxx)

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
       the file.

            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
       your job.

            TRNDLTRCD   FILE(xxx)

  **   Check the file with DSPMBRD:

            DSPMBRD     FILE(xxx)

  **   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).

            CLRPFM      FILE(TAAWORK/CPRDLTRCD)

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
REUSEDLT(*YES).

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.

Restart
-------

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
specify:

         - FROMRCD(*RESTART)

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
       by CPRDLTRCD.

  **   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.

Work file
---------

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.

Commitment control
------------------

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
                 found.

                 If  a  value is  entered,  it must  be  between 1  and
                 99,999,999,999.

                 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
                 performed.

   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
                 99.9.

   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
                 specified.

   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
                 parameter description.

                 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
                 deleted.

Restrictions
------------

Only physical files are supported.

Files  containing Large Objects  (LOBs) -  either BLOBs or  CLOBs - are
not supported.

The user of  CPRDLTRCD must have all  rights to the  file named in  the
FILE parameter.

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.

Prerequisites
-------------

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

Implementation
--------------

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.
					

Added to TAA Productivity tools September 15, 2000


Home Page Up to Top