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 This email address is being protected from spambots. You need JavaScript enabled to view it..

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