Brief: Deleted records take up disk space and can be difficult to manage.
Physical files need to be reorganized regularly in a controlled manner. This
utility allows you to reorganize the files in a library and specify when the
reorganization takes place.
Disk space utilization is growing, jobs are taking longerùwhat should you do?
One thing that can help is to reorganize files that have deleted records to
free up disk space.
The only problem is, to reorganize a file, you need to have exclusive use of
the file. You should run the reorganization overnight or on the weekend, but
what if you get busy and forget to submit it? Another day goes by and disk
usage increases a little more. You need a way to schedule a job that will
reorganize files every night or weekend.
I wrote the Reorganize Library (RGZLIB) command to fill this need. I have used
this utility in a CL training class as an example of the many common things
done in CL programming. The command processing program (CPP) executes a command
to output to a file, reads that file using the data from each record to execute
a CL command, and has some basic error handling.
The program uses the Open Query File (OPNQRYF) command. Many features of this
program can be used in other system utility programs.
The RGZLIB command will reorganize all library files that contain deleted
records, and it will do it within a given time frame. RGZLIB will reorganize
the files in sequence of disk space consumed by deleted recordsùthe file with
the most space to be freed is reorganized first. The command will end at a
specified date and time, so if your users get in at 6:00 a.m., for instance,
you can tell the program not to begin any reorganizations after 5:00 a.m.
RGZLIB would normally be submitted before leaving for the night or weekend,
using the date and time parameters to make sure it completes before the
beginning of the next work day.
Overview
The parameters on the RGZLIB command are a library name and a stop date and
time for the job. The prompt for the command is illustrated in 1, and
time for the job. The prompt for the command is illustrated in Figure 1, and
the source code for the command is shown in 2. RGZLIB does not use a
the source code for the command is shown in Figure 2. RGZLIB does not use a
validity checking program to verify the parameters that were entered, but the
parameter types do perform some rudimentary checking. The library must be a
*NAME type, which ensures it follows AS/400 object naming conventions. The date
and time parameters are *DATE and *TIME data types, so some validation is done.
The library name parameter is edited within the CPP.
The CPP (RGZ001CL), shown in 3, starts by declaring the file to be read
The CPP (RGZ001CL), shown in Figure 3, starts by declaring the file to be read
and the variables used in the program. The following line is used to tell the
compiler what the file being read will look like:
DCLF FILE(QAFDMBR)
This tells the program what record format will be used. Almost every IBM
command that has an output file parameter has a template file in QSYS that is
used when you run a command that creates a new output file. If you read the
detailed Help text on the output file parameter, this file name is listed.
By declaring the template file, you do not have to create any permanent work
files, and, since the compiler is using the IBM file, the program will compile
without creating the work file. The Override Database File (OVRDBF) command
then overrides from the declared file to the file that was created by the
Display File Description (DSPFD) command.
The main program begins by using the Check Object (CHKOBJ) command to see if
the library exists. If the check object fails, the program sends a message and
ends. The program deletes the work file that it is about to create and the
DSPFD command sends a list of all physical files in the specified library to
the output file. The DSPFD command uses *MBR for the TYPE parameter so the
output file will contain the record count information required later in the
program.
The program builds the ending date value by checking to see if the user has
requested a specific date to end. If not, it defaults to ending on the current
date. The program formats this date into a YYMMDD layout. If no end time was
entered, the ending time is changed to 999999, and the program will run until
the date parameter is exceeded. A message is also sent to the joblog so you can
easily tell when the job will end.
The OVRDBF command is then used to override from the file named on the Declare
File (DCLF) command to the output file from DSPFD.
Sorting the Fields
OPNQRYF sorts the file in descending sequence according to the disk space used
by the deleted records. Only files that contain at least one deleted record are
selected. The technique used to do this can be very handy.
OPNQRYF can only sort on a field that is listed in the record format. Normally,
you would not be able to sort on the amount of space taken up by the deleted
records since this is a calculated field. Using the MAPFLD parameter, you can
calculate a value of the deleted record space by multiplying the record size by
the number of deleted records. Then store that value in an existing field in
the record format that you will not be using (in this case, the member size
field).
You can use that field name on the KEYFLD parameter and sort the file in
descending sequence according to the disk space used by the deleted records.
This sorting, done by the OPNQRYF command, reorganizes files in the most
productive sequence, so you get the most disk space back while running as few
reorganizations as possible.
The program then begins looping through each record in the output file. At the
beginning of each pass through the loop, the program retrieves the current
system date and time and compares it to the date and time the program was told
to end. If the current date is greater than or equal to the end date, and the
current time is greater than the end time, the program branches to the end of
file processing.
If the date edit passes, the Receive File (RCVF) command reads the next input
record. The Monitor Message (MONMSG) command checks to see if end-of-file is
reached, branching to the end of the program if it is.
Each record read from the output file contains the name of the file, library,
and member that needs to be reorganized. You can view the names of these fields
by using the Display File Field Description (DSPFFD) command against the
template file, QAFDMBR. The Reorganize Physical File Member (RGZPFM) command is
then run using the file, library, and member name to reorganize the file. The
monitor message is included, so even if there is a problem with a
reorganization, the program will continue processing the rest of the files.
If the program encounters an error, a message sent to the joblog lists the file
that had the problem. If there is no problem, the reorganization is complete.
The program branches around and begins the process again by checking the
current date and time against the job's end date and time. When end of file is
reached, the program branches to the tag EOF and performs some cleanup tasks.
The program closes the output file being read, deletes the override, does a
reclaim resource to free up any other resources, and ends.
Implementation Issues
RGZLIB can be implemented very easily. The CL program (RGZ001CL) should be
compiled first. The command itself should then be compiled with the program as
its CPP. No special parameters are needed on either compile. The command can
run either interactively or in batch and can be a stand-alone function or
embedded in a jobstream. The program can be tested by running it against a test
library.
RGZLIB can be a fairly simple way to keep the number of deleted records in your
production libraries to a minimum. By running it every weekend over your main
production libraries, you can keep disk space cleaned up and your applications
running smoothly without ever having to remember to reorganize a file again.
Fred Gamache is a systems consultant with Palarco Inc. in Wayne, Pennsylvania.
He can be reached at 610-687-3410 or by E-mail at
The Reorganize Library (RGZLIB) Utility
Figure 1The Prompt for the RGZLIB Command
UNABLE TO REPRODUCE GRAPHICS
The Reorganize Library (RGZLIB) Utility
Figure 2The RGZLIB Command
/*===============================================================*/ /* To compile: */ /* */ /* CRTCMD CMD(XXX/RGZLIB) PGM(XXX/RGZ001CL) + */ /* SRCFILE(XXX/QCMDSRC) */ /* */ /*===============================================================*/ CMD PROMPT('Reorganize a Library') PARM KWD(LIB) TYPE(*NAME) LEN(10) MIN(1) + PROMPT('Library to reorganize') PARM KWD(DATE) TYPE(*DATE) PROMPT('End date') PARM KWD(TIME) TYPE(*TIME) PROMPT('End time')
The Reorganize Library (RGZLIB) Utility
Figure 3Command Processing Program RGZ001CL
/*===============================================================*/ /* To compile: */ /* */ /* CRTCLPGM PGM(XXX/RGZ001CL) SRCFILE(XXX/QCLSRC) */ /* */ /*===============================================================*/ PGM PARM(&LIB &INDAT &INTIM) DCLF FILE(QAFDMBR) DCL VAR(&LIB) TYPE(*CHAR) LEN(10) DCL VAR(&INTIM) TYPE(*CHAR) LEN(6) DCL VAR(&TIME) TYPE(*CHAR) LEN(6) DCL VAR(&DATE) TYPE(*CHAR) LEN(7) VALUE('0000000') DCL VAR(&INDAT) TYPE(*CHAR) LEN(7) DCL VAR(&ENDTIM) TYPE(*CHAR) LEN(6) VALUE('000000') DCL VAR(&ENDDAT) TYPE(*CHAR) LEN(7) VALUE('0000000') DCL VAR(&MSG) TYPE(*CHAR) LEN(80) DCL VAR(&QYEAR) TYPE(*CHAR) LEN(2) DCL VAR(&QMONTH) TYPE(*CHAR) LEN(2) DCL VAR(&QDAY) TYPE(*CHAR) LEN(2) /* MAKE SURE LIBRARY EXISTS */ CHKOBJ OBJ(QSYS/&LIB) OBJTYPE(*LIB) MONMSG MSGID(CPF0000) EXEC(DO) CHGVAR VAR(&MSG) VALUE('Library' *BCAT &LIB *BCAT + 'not found') SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA(&MSG) + MSGTYPE(*ESCAPE) GOTO CMDLBL(EOF) ENDDO /* CREATE A FILE CONTAINING ALL FILES FROM THE REQUESTED LIBRARY */ DLTF FILE(QTEMP/DELREC) MONMSG MSGID(CPF0000) DSPFD FILE(&LIB/*ALL) TYPE(*MBR) OUTPUT(*OUTFILE) + FILEATR(*PF) OUTFILE(QTEMP/DELREC) + OUTMBR(*FIRST) CHGVAR VAR(&ENDTIM) VALUE(&INTIM) IF COND(&INDAT = '0000000') THEN(DO) RTVSYSVAL SYSVAL(QYEAR) RTNVAR(&QYEAR) RTVSYSVAL SYSVAL(QMONTH) RTNVAR(&QMONTH) RTVSYSVAL SYSVAL(QDAY) RTNVAR(&QDAY) CHGVAR VAR(%SST(&ENDDAT 2 2)) VALUE(&QYEAR) CHGVAR VAR(%SST(&ENDDAT 4 2)) VALUE(&QMONTH) CHGVAR VAR(%SST(&ENDDAT 6 2)) VALUE(&QDAY) ENDDO ELSE CMD(CHGVAR VAR(&ENDDAT) VALUE(&INDAT)) /* IF NO END TIME WAS ENTERED - RUN TILL ALL FILES ARE REORGANIZED */ IF COND(&INTIM = '000000') THEN(CHGVAR + VAR(&ENDTIM) VALUE('999999')) ELSE CMD(DO) CHGVAR VAR(&MSG) VALUE('Job will end at' *BCAT + &INTIM *BCAT 'on' *BCAT &INDAT) SNDPGMMSG MSG(&MSG) ENDDO /* USE OPNQRYF TO ONLY SELECT FILES WITH AT LEAST ONE DELETED RECORD */ /* AND REORGANIZE BASED ON THE MOST DISK SPACE BEING CONSUMED */ OVRDBF FILE(QAFDMBR) TOFILE(QTEMP/DELREC) SHARE(*YES) OPNQRYF FILE((DELREC)) QRYSLT('MBNDTR *NE 0') + KEYFLD((*MAPFLD/MBDSSZ *DESCEND)) + MAPFLD((MBDSSZ 'MBMXRL * MBNDTR')) READ: /* GET CURRENT SYSTEM TIME */ RTVSYSVAL SYSVAL(QTIME) RTNVAR(&TIME) RTVSYSVAL SYSVAL(QYEAR) RTNVAR(&QYEAR) RTVSYSVAL SYSVAL(QMONTH) RTNVAR(&QMONTH) RTVSYSVAL SYSVAL(QDAY) RTNVAR(&QDAY) /* COMPARE CURRENT DATE/TIME TO TIME YOU WANT THE JOB TO END */ CHGVAR VAR(%SST(&DATE 2 2)) VALUE(&QYEAR) CHGVAR VAR(%SST(&DATE 4 2)) VALUE(&QMONTH) CHGVAR VAR(%SST(&DATE 6 2)) VALUE(&QDAY) IF COND(&TIME *GT &ENDTIM *AND &DATE *GE + &ENDDAT) THEN(GOTO CMDLBL(EOF)) /* If + past End Time, end job */ /* READ NEXT RECORD FROM OUTPUT FILE OF "DSPFD" */ RCVF RCDFMT(QWHFDMBR) MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(EOF)) /* If + End of File error, end program */ RGZPFM FILE(&MBLIB/&MBFILE) MBR(&MBNAME) MONMSG MSGID(CPF0000) EXEC(DO) /* If the reorganize + failed, put an error message in the joblog */ CHGVAR VAR(&MSG) VALUE('File' *bcat &MBfile *bcat + 'could not be reorganized.') SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA(&MSG) + MSGTYPE(*STATUS) ENDDO GOTO CMDLBL(READ) /* read next record */ EOF: CLOF OPNID(DELREC) MONMSG MSGID(CPF0000) DLTOVR FILE(QAFDMBR) MONMSG MSGID(CPF0000) RCLRSC ENDPGM
LATEST COMMENTS
MC Press Online