The Stray Logical Landmines

Recently, I was faced with a situation most of us have probably experienced at one point or another on the AS/400 platform. I was in front of a program that appeared to work perfectly in a test environment but consistently yielded strange results when in production.

Eventually, we found the solution to this problem. You guessed it; it was a stray logical, (a logical file pointing to a physical file in a library different from its own). Once this was fixed, to point to where it should, the program worked properly in production and everyone was happy. Everyone? Well, there was a lingering doubt in my mind that there might be more of those hidden traps in other areas of the system.

In this article, I will describe the technique I used to "sniff" out every single case of a logical pointing to a physical in a library different than its own. My aim was clearly to cover the entire AS/400, not just the situation I had just resolved. I also wanted to package this utility in a simple program and not spend too much time doing it. My "stray-logical sniffer" utility ended up being just two objects: a CL program and a QUERY.

When running this utility for the first time I found there were quite a few of these cases, especially where users had created logical files in private libraries, pointing to production files. The results of my sniffer utility surprised none more than our customer, on whose box we performed this operation. The reaction was, at the time, to review each individual situation (there were over 200), and either delete the stray logical or place it in the proper library. Note that this exercise, depending on the load on the system should probably be run either at night or during the weekend. It may take a number of hours to complete.

While most of these situations did not directly affect the production system, they could have. Here is the kicker: The situation I described earlier happened 6 months prior to writing this article. Between Christmas 98 and New Year 99, having a lighter load on the system, I decided to re-run my stray-logical sniffer utility again. The results it produced showed that, as time went by, new logical files were built, again on libraries different from their own. Again, we had to go through a purge exercise to review each situation individually. My best guess is that this type of situation will happen anywhere there is intensive development going on with mixed teams of consultants and in-house programmers. Controlling this is probably mostly a matter of control, security and education with programmers who are new on the system and a perhaps some enforcement of the rules. Having the comfort of being able to audit this aspect of your system can help too.

Only when I ran this utility 6 months after our first clean-up, did I realize how dynamic a working company's computer system really can be. What I am saying here is that cleaning stray logicals once does not mean they will not ever re-appear. New programmers replace the ones that leave, standards are not always clearly explained or enforced, people run one-time tests but don't always clean up after themselves, and security holes do happen. Basically, monitoring your system for this type of problem should be an on-going exercise.

Here is the code for the CL…

Note: The file is first built from one generic library. It is then cleared and modified. This is to give us the opportunity to modify the size of the file before starting the larger process of producing this file. If this were not done, one would get endless requests to increase the size of the file.

************************************************************

0001.00 PGM

0002.00

0003.00 /*****************************************************/

0004.00 /* DISPLAY DATABASE RELATIONS */

0005.00 /* FOR ALL USER LIBRARIES. */

0006.00 /* THE PURPOSE OF THIS PROGRAM */

0007.00 /* IS TO SHOW ALL PHYSICAL FILES */

0008.00 /* WITH A LOGICAL FILE THAT IS IN */

0009.00 /* A DIFFERENT LIBRARY *******************************/

0010.00

0011.00 DLTF FILE(QTEMP/DBRALL)

0012.00 MONMSG MSGID(CPF0000)

0013.00

0014.00 DSPDBR FILE(QGPL/*ALL) OUTPUT(*OUTFILE) +

0015.00 OUTFILE(QTEMP/DBRALL)

0016.00

0017.00 CLRPFM FILE(QTEMP/DBRALL)

0018.00

0019.00 CHGPF FILE(QTEMP/DBRALL) SIZE(1000000 10000 90)

0020.00

0021.00 DSPDBR FILE(*ALLUSR/*ALL) OUTPUT(*OUTFILE) +

0022.00 OUTFILE(QTEMP/DBRALL)

0023.00

0024.00 /*****************************************************/

0025.00 /* QUERY THE RESULTS OF THE EXERCISE */

0026.00 /*****************************************************/

0027.00

0028.00 RUNQRY QRY(QRYDBRALL) QRYFILE((QTEMP/DBRALL)) +

0029.00 RCDSLT(*NO)

0030.00

0031.00 ENDPGM

***************************************************************

Here are the screens for building the Query Definition on the result file just built:

Note: the easiest way to create this Query is to first do a DSPDBR with a resulting file in QTEMP. This way the file can be quickly built and you have a base to create the query definition. The Query Definition itself must be named "QRYDBRALL" to work within the context of the CL program above.

Specify File Selections

Type choices, press Enter. Press F9 to specify an additional file selection.

File . . . . . DBRALL Name, F4 for list

Library . . QTEMP Name, *LIBL, F4 for list

Member . . . . *FIRST Name, *FIRST, F4 for list

Format . . . . QWHDRDBR Name, *FIRST, F4 for list

Select and Sequence Fields

Type sequence number (0-9999) for the names of up to 500 fields to appear in the report, press Enter.

Seq Field Text Len Dec

10 WHRLI Library 10

20 WHRFI File 10

30 WHNO Number of dependencies 5 0

40 WHRELI Dependent library 10

50 WHREFI Dependent file 10

Select Records

Type comparisons, press Enter. Specify OR to start each new group. Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT.

AND/OR Field Test Value (Field, Number, 'Characters', or ...)

WHRLI NE WHRELI

AND WHRELI NE ' '

Select Sort Fields

Type sort priority (0-999) and A (Ascending) or D (Descending) for the names of up to 32 fields, press Enter.

Sort

Prty A/D Field Text Len Dec

10 A WHRLI Library 10

20 A WHRFI File 10

30 A WHREFI Dependent file 10

40 A WHRELI Dependent library 10

Select Report Summary Functions

Type options, press Enter. 1=Total 2=Average 3=Minimum 4=Maximum 5=Count.

---Options--- Field Text Len Dec

5 WHRLI Library 10

5 WHRFI File 10

Define Report Breaks

Type break level (1-6) for up to 9 field names, press Enter. (Use as many fields as needed for each break level.)

Break Sort

Level Prty Field Text Len Dec

1 10 WHRLI Library 10