Simple method to Reorganize a database library

Often significant disk space can be recovered by simply reorganizing your database physical files. This is due to the fact that delete records are usually still occupying space. The operating system will mark an index that the record is to be ignored, i.e. "deleted", but the physical space is not recovered until the file is reorganized with RGZPFM. One exception to this rule is when the "reuse deleted records" parameter is set but this is not common. Reuse deleted records means that "arrival sequence" will no longer have any valid meaning. Recovering this wasted space is easy and can allow programs to operate more efficiently.

One simple and effective means of recovering this disk space is to write a CL procedure that simply does a DSPFD to an out-file of all physical files in a database library. The procedure then does a DCLF of that out-file and READS the out-file continuously until end-of-file is reached. The out-file can be created in QTEMP so that no further maintenance would be required. The only logic required is that the procedure check the number of deleted records. If the number is greater than zero the procedure does a RGZPFM on that file.

Example:

      DCLF FILE(*LIBL/QAFDMBRL)
      OVRDBF FILE(QAFDMBRL) TOFILE(QTEMP/QAFDMBRL) MBR(*ALL)
      DSPFD FILE(MyLib/*all) TYPE(*MBRLIST) OUTPUT(*OUTFILE) 
                 OUTFILE(QTEMP/QAFDMBRL) 
READ: RCVF
      MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(EOF))
      ALCOBJ OBJ(*LIBL/&MLFILE *FILE *EXCL)) WAIT(5)
      MONMSG MSGID(CPF0000) EXEC(DO)
      GOTO CMDLBL(READ)
      ENDDO
      IF COND(&MLNDTR *GT 0) THEN(DO)
      RGZPFM FILE(*LIBL/&MLFILE)
      ENDDO

The procedure needs to do a MONMSG immediately following the RGZPFM to check for locks. An override (OVRDBF) can be used to change the wait time to, say, five seconds for example, so a lock is detected sooner than the default sixty seconds. If the file is locked by another user it can be either skipped or a delay can be added and the procedure can loop and try again. Of course it would be preferable to run such a procedure during off hours to limit the risk of an object being locked. Also, once a RGZPFM starts canceling it is not advised.

Take "before and after" snap shots of the DASD utilization, using DSPSYSSTS, to determine how much space is actually recovered. Run this procedure weekly or monthly.

Usual defaults are:
RGZPFM KEYFILE(*NONE) RBDACCPTH(*YES) ALWCANCEL(*NO)

The RGZPFM RBDACCPTH(*YES) command will automatically resynchronize any logical files attached to the physical files.

Allow cancel with RGZPFM ALWCANCEL(*YES);  Unfortunately this "allow cancel" feature is not as useful as it sounds. Using the ALWCANCEL requires the PF to be journaled.

RGZPFM can also be used to reorganize source file members although it is also easy to change sequences and dates with PDM/SEU. To resequence starting with line ten in increments of five:

RGZPFM FILE(QRPGLESRC) MBR(MYSOURCE) SRCOPT(*SEQNBR) KEYFILE(*NONE) SRCSEQ(10.0 5.0)