Lund Performance Solutions Sponsor Message

Understanding the power of REPEAT...FORFILES

By Steve Hammond

Inside VESOFT covers tips and techniques you can use with VESOFT’s products, especially MPEX.

When I was first approached about writing this column for the NewsWire, I was both flattered and intimidated. I was flattered that the editor thought enough of me as a ‘3000 person’ and as a writer to think I was capable of producing this column on a (hopefully) monthly basis. I was intimidated because of the skill and knowledge of my predecessor — Shawn Gordon. I wish Shawn luck in his new venture, and I hope I can fill his columnist shoes.

That said, let’s move toward what I hope to accomplish over the coming months. Obviously, a lot has been covered by Shawn over the past few years, but I am going to attempt to write this as if I were starting anew. Not that I am going to go back to the basics of VEsoft products, just that I will not shy from a subject because “Shawn already did it.

To that end, I renew Shawn’s entreaty for input from the user community. If you have any ideas, hints, shortcuts, etc. that you wish to share, please let me know and I’m sure we can reward you with some gewgaw we got at some obscure trade show.

About six years ago, when my supervisor was searching for programmers, she created a check-sheet of skills which we would ask job applicants to fill out. The sheet included a full range of e3000 skills — Qedit, Supertool, PowerHouse, Adager, and of course MPEX. The applicants were asked to rate themselves from 0 to 6, with 0 being no knowledge and 6 being skilled enough to train other staff members in the intricacies of the tool

On more than one occasion, I interviewed an applicant who had rated his skill level as a ‘6’ for MPEX. Since I at one time taught MPEX classes, I was always ready for the opportunity to burst that bubble with one question never failed me — “Explain the REPEAT...FORFILES command in MPEX.” There was usually a blanched look on the applicant’s face and a mumble of something like, “Maybe I rated myself too high on that.”

I have been constantly amazed that MPEX users overlook this powerful set of commands. To put things in simple terms, I view MPEX as just a combination of the creation of an indirect file, and the execution of a REPEAT...FORFILES command on the entries in that indirect file. (I realize there’s a lot more to it than that, but that takes it to an understandable level.) There are actually six REPEAT...FOR... commands, all of which perform basically the same type of function - FORFILES, FOREACH, FORJOBS, FORNUM, FORPROFILES and FORRECS. The syntax varies slightly on each, but the idea is the same — give MPEX a command or set of commands and a list. MPEX will then perform that command or those commands on the list.

The REPEAT...FOR construct basically allows you to declare specific commands that are not necessarily part of the MPEX command set and do them repetitively on the selected set of files. All you do is place that command or commands between the REPEAT and the FOR statements and MPEX will perform them. MPEX variables and attributes can be used to address certain aspects of a file in the commands performed. (Note: My examples are ‘use’ files. When run interactively, MPEX issues a ‘>’ prompt after the REPEAT statement and it continues until you complete the command with the FOR statement.)

REPEAT...FORFILES has been very helpful in performing some repetitive tasks I have had to perform on an annual or semi-annual basis. These tasks were generally tedious — creation of 12 empty databases from schema text files, for example. This involved first copying the 12 schema files from one account to the new, empty account. Then it was a matter of running DBSCHEMA 12 times followed by doing the create function in DBUTIL 12 times. I chose instead to set up a command file to do everything! The first part was simple — the MPEX copy of the 12 schema files, stored in a different (source) account — obviously, the files had to be released or the copy done with SM capability.

Now came the step where REPEAT...FORFILES came in handy (although as you’ll see later, FOREACH and FORRECS also could have worked quite nicely). I needed to create the root file by running the schema through DBSCHEMA. I knew in advance that all 12 of the schema text files ended in DDL (for data definition language — created with the SCHEMA function in Adager). So I used this command:

REPEAT
FILE DBSTEXT=!MPEXCURRENTFILE
RUN DBSCHEMA.PUB.SYS;PARM=1
FORFILES @DDL

This basically says, for any file ending in DDL, make that file the input file (DBSTEXT) when running DBSCHEMA with a parm of 1. The variable !MPEXCURRENTFILE refers to the current file in the sequence of the 12 DDL files. So 12 root files were created with this step.
It was now necessary to create the 12 databases from the 12 root files, and again REPEAT...FORFILES did the trick.

REPEAT
RUN DBUTIL.PUB.SYS,CREATE;INPUT=”![RFILE.FILE]”
FORFILES @(CODE=”PRIV”)

This time, I looked for the 12 ‘PRIV’ code files in the account. Remember, this was an empty account before I copied in the schema text files, so I knew the only PRIV files would be the 12 roots. FORFILES looks for PRIV files and the command runs DBUTIL, with the entry point of CREATE, and uses as input the file name of the database root file. In this case, I had to use ![RFILE.FILE] because !MPEXCURRENTFILE gives the fully qualified file name, while RFILE.FILE gives the file name attribute, which is just the name without qualification. RFILE.GROUP would give the group name and RFILE.CODE would give the file code, etc.

So this step would create the 12 databases, thus doing everything in a command file instead of interactively running DBSCHEMA and DBUTIL 12 times each.

A second means of doing this would have been to create a file (here named THISFILE) containing the specific file names in it, whether (as here) the schema text files or the root files and then done:

REPEAT
FILE DBSTEXT=!THISREC
RUN DBSCHEMA.PUB.SYS;PARM=1
FORRECS THISREC=THISFILE,OLD

FORRECS says to process each record of the file, substituting the data in each record for the THISREC variable. The command runs through all the records in the file. In this case, THISREC and THISFILE are user-declared variables, as opposed to the MPEXCURRENTFILE variable earlier, which is declared by MPEX.

And the third way to perform the same function is with the REPEAT...FOREACH command. In this case you declare the file names in the FOREACH statement.

REPEAT
FILE DBSTEXT=!FNAME
RUN DBSCHEMA.PUB.SYS;PARM=1
FOREACH FNAME=file1, file2, file3...file12

All accomplish the same task, just using slightly different approaches. Next time, we’ll play with the other REPEAT...FOR commands.

Steve Hammond is a system manager who plays with MPEX, along with other software packages, for a trade association in Washington, DC.