Here is a program that uses SQL to produce a report showing level check conflicts. The system stamps every file created with a format level identifier. This identifier changes when major attributes of the file change, such as field attributes, number of fields, or order of fields. The format level is not affected by the creation date or edit codes. When a program is compiled, the format level identifier becomes a part of the program. When the program executes, the system checks the format level identifier in the program against the one in the file, and if they don't match, a MCH3601 error will be generated. (The format check is ignored if LVLCHK(*NO) is specified for the file when it is created.)

CL program LEVELCK (1), along with RPG program LEVELCKR (2) will produce a list of all programs in which the format level identifier does not agree with the one in the file.

CL program LEVELCK (Figure 1), along with RPG program LEVELCKR (Figure 2) will produce a list of all programs in which the format level identifier does not agree with the one in the file.

The CL program LEVELCK is called with two parameters. The first is the name of the library for the files and the second is the name of the library for the programs. The program uses the DSPFD command to create a file with all the format descriptions(TYPE(*RCDFMT)). The display program reference (DSPPGMREF) command is used to create a file with all the programs and the objects that they reference including the format level of each referenced object. The program LVL uses SQL to produce a list of the unmatched format levels.

F1 is a data structure with the necessary field names from both files. The first SQL statement will handle all errors by ending the program. The second statement identifies the selection and joining of the two files. The files will be joined and data will be returned to the program if the following conditions are met: the file name and format name match, the referenced object is a file, the record format identification is not 'spaces', and neither the ID in the record format nor the ID in the program match the ID in the file. The main processing loop is simple. The program reads the joined file (FETCH R1 INTO :F1) until the SQL return code (SQLCOD) is not zero. You can see how easy programming can be if you let SQL do the work for you.

Alon Fluxman Highlands Ranch, Colorado

TechTalk: Finding Level Check Conflicts

Figure 1 CL program LEVELCK

 LEVELCK: + PGM PARM(&LIBRARY1 &LIBRARY2) DCL VAR(&LIBRARY1) TYPE(*CHAR) LEN(10) DCL VAR(&LIBRARY2) TYPE(*CHAR) LEN(10) DSPFD FILE(&LIBRARY1/*ALL) TYPE(*RCDFMT) OUTPUT(*OUTFILE) + OUTFILE(QTEMP/FILEFILE) DSPPGMREF PGM(&LIBRARY2/*ALL) OUTPUT(*OUTFILE) + OUTFILE(QTEMP/PROGFILE) CALL PGM(LEVELCKR) ENDPGM 
 
 TechTalk: Finding Level Check Conflicts
 
 Figure 2 RPG program LEVELCKR
 
 FQSYSPRT O F 132 OF PRINTER IF1 DS I 1 10 WHPNAM I 11 20 WHLIB I 21 30 WHOTYP I 31 41 WHFNAM I 42 42 WHOBJT I 43 53 WHLNAM I 54 63 WHRFNM I 64 76 WHRFSN I 77 86 RFFILE I 87 96 RFLIBM I 97 97 RFFTYP I 98 107 RFNAME I 108 120 RFID C EXCPT#HEAD C/EXEC SQL WHENEVER SQLERROR GOTO ERROR C/END-EXEC C/EXEC SQL DECLARE R1 CURSOR FOR SELECT C+ WHPNAM, WHLIB, WHOTYP, WHFNAM, C+ WHOBJT, WHLNAM, WHRFNM, WHRFSN, RFFILE, RFLIB, RFFTYP, C+ RFNAME, RFID FROM PROGFILE, FILEFILE C+ WHERE WHFNAM = RFFILE AND WHRFNM = RFNAME C+ AND WHOBJT = 'F' AND WHRFSN > ' ' C+ AND WHRFSN <> RFID C/END-EXEC C/EXEC SQL C+ OPEN R1 C/END-EXEC C SQLCOD DOUNE0 C/EXEC SQL C+ FETCH R1 INTO :F1 C/END-EXEC C EXCPT#PRT C OF EXCPT#HEAD C OF SETOF OF C END C ERROR TAG C SETON LR OQSYSPRT E 203 #HEAD O 06 'OBJECT' O 20 'LIBRARY' O 30 'TYPE' O 45 'FILE NAME' O 49 'TP' O 56 'FORMAT' O 70 'LEVEL' O 90 'FORMAT LEVEL' OQSYSPRT E #PRT O WHPNAM 10 O WHLIB 22 O WHOTYP 34 O WHFNAM 47 O WHOBJT 49 O WHRFNM 60 O WHRFSN 76 O RFID 90