Who uses this file? Dependencies with DSPPGMREF

DSPPGMREF lists the objects that programs refer to: files, data areas, other programs, service programs. With OUTPUT(*OUTFILE) it writes the list to a database file, and from there a query tells you which programs use a file, which is the question to ask before changing its layout.

The references file

DSPPGMREF PGM(MYLIB/*ALL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/REFS)

PGM also accepts *ALLUSR as the library, for every user library; OBJTYPE says which objects to look at (*PGM, *SRVPGM, *MODULE, *SQLPKG, *QRYDFN, or *ALL). The file produced has format QWHDRPPR of file QADSPPGM in QSYS: one row per program-object pair.

The query

SELECT WHLIB, WHPNAM, WHFUSG
  FROM QTEMP.REFS
 WHERE WHFNAM = 'ITEMS'
   AND WHOTYP = '*FILE'
 ORDER BY WHLIB, WHPNAM;

WHFUSG is how the program uses the file, as a sum of codes:

A 7 is input, output and update; a 5 input and update. Before adding a field to a file, the programs with 2 or 4 in the code are the first to look at.

What it misses

DSPPGMREF shows what the compiler recorded when the program was created. Its limits follow from that, and IBM spells them out:

• Names in a variable. In CL, if the file name is in a variable, the variable name is recorded (&FILE); if it is an expression, *EXPR. In COBOL a call using an identifier instead of a literal (a dynamic call) is not recorded.
• Languages with little information. For ILE C only service programs, neither files nor programs; for non-ILE C nothing.
• Names as of compile time. If the object was moved afterwards, or an override was in effect during compilation, the recorded name may differ from the real one.
• Rows that never go away. UPDPGM and UPDSRVPGM add references, but never remove any.
• SQL. Tables, views and indexes used by embedded SQL are recorded, with the library as written in the source: with system naming, an unqualified name without DFTRDBCOL stays *LIBL. Dynamic SQL, built at run time, is not there.
• The local data area is not recorded.

So the result tells you for certain who uses a file; it does not tell you for certain that nobody else does.

Example

Library, file and program names are made up.

The description field of file ITEMS has to be lengthened. DSPPGMREF on ERPLIB and the query give 23 programs: 17 with code 1, 4 with 5, 2 with 3. The 6 that write or update are reviewed one by one; the 17 that read are recompiled, because the format changes.

Then you look for what DSPPGMREF misses: CL programs with the name in a variable show up by querying WHFNAM LIKE '&%' and WHFNAM = '*EXPR', and for each one you read the source; for dynamic SQL you search the sources for the file name with FNDSTRPDM.

Which releases

DSPPGMREF is supported from the 7.1.

Sources

• IBM: Display Program References (DSPPGMREF), 7.6
• IBM: Displaying the files used by programs (7.6)
• IBM: Program references in applications that use SQL (7.6)