Problem solve Get help with specific problems with your technologies, process and projects.

Beyond DSPPGMREF -- Scan all libraries before changing a database file

Eliminate some of the hassle of changing a database file by scanning ALL libraries beforehand.

No matter how much planning you do when you design a database file, it will eventually need to be changed.

A seemingly small change, such as adding a new field or changing an existing field, may actually become a very involved job, depending on the usage of the file in question. You must find all program references to that file and its associated logical files so you can begin checking each program for necessary changes and recompiling. This search, unfortunately, is not limited solely to a single library. It must encompass your whole system to avoid an unexpected level-check error in the future. You don't know how big a job you're facing until you find all references to the physical file (and attached logical files) being changed.

Your first step is to scan your HLL source code (one library at a time) for references to the physical file and its associated logical files plus any references in CL. However, this step doesn't have to be done manually. The iSeries provides a command -- DSPPGMREF -- that can save you a lot of time in situations like this. It makes this possible by creating an outfile that you can use to print a report on the information you need for a single library.

A tech tip by Eugene Arencibia in Midrange Computing in June of 1992 showed how to access this information, but it worked with only one library at a time. The software in this article solves that problem and will give you a systemwide scan of all the libraries on your iSeries. It uses a physical file (LIBRNAME) to store the names of all your application libraries (with program load members) on the system. All the library names are then processed using this physical file. It creates a physical file (LIBRFLNM) with all file and program references for your entire system. This is generally run via the job scheduler after working hours to have a current file for referencing. However, it can be run on demand if you wish to have an up to the minute cross-reference file.

Extra feature -- wild card function
There is also a 'wild card' feature ('.') for pulling all desired file references in one pass. For example you can scan for MSTRFLPF, MSTRFLLF and MSTRFLL1 in separate searches or simply enter MSTRFL.. and get a single report with all program references to each of these files. The wild card character can be placed anywhere after the first character, and there can be two sections of wild card characters (e.g., MS..FL..).

Using this system file gives you an accurate picture of file usage by program in each library. Thus you get an up-to-date picture of all program references (CL, DFU and RPG) to a select file. Additionally, the report includes the date of last use, so you can readily determine if the program is being used.

The List File Program Usage (FILESCAN) command gives you a solution to this problem with a concise listing of necessary information about all programs using the select file.

Extra feature -- source code scan for character string
To search for use of a select field in these programs you can use the command FIELDSCAN to list all references to a desired field (e.g., UNITX in upper or lower case) in the programs on the previous listing.

You must create the physical file named LIBRNAME (I placed it in QGPL for reasons described below) before running these programs. Use your favorite file editor to enter the names of all your libraries containing compiled program object members. This is IMPORTANT as the program will halt and you will get a CPF2123 error if you try to process a library with no program object members.

Setup instructions:

  1. Create all physical and logical files -- FLDMATCH, LBPGFLUS, PGMFLPF, PGMLFLF, PROGRMNM
  2. Compile all Display Screen programs
  3. Compile all RPG or RPGLE programs
  4. Compile all CL programs
  5. Create all CMDs

Extra feature -- automated compiling
Once you have this listing of programs using a file(s) in question, you generally have to compile those programs to avoid level-check errors the next time you run them. As long as I had the necessary information for compiling all affected programs, I wrote a process to automate the compiles. Enter the command COMPILE and CL or RPG when prompted. You will get a screen informing you that you are compiling programs using ---------- files.

You are allowed to change the source and/or object libraries in case you do not keep your compiled objects in the same library as your source members. CMD 1 allows you to bypass compiling a program. CMD 3 ends the job as usual, but in case you have to stop somewhere in the middle of the list, you can start in where you left off when you resume compiling.

Each program name record is coded with an 'X' in the PGSTAT field after a successful compile. This code is checked, and the program name is bypassed if not blank in the F0002RG program. In case of a fatal compile error you will get a message on your screen informing you of the unsuccessful compile, note the name of the program for later correction, take a CMD 1 to bypass the program and continue with the next compile.

I put all the objects in QGPL library so we can call FILESCAN, FIELDSCAN and COMPILE from any library when the need arises. If your shop standards are different for this process you can put the objects where you need them.

Note: Download the save file that includes all the code referred to in this tip. This file must then be transferred to your iSeries via FTP. For help FTPing OS/400 save files, refer to this tip from Joe Hertvik -- Tricks and techniques for FTPing OS/400 save files. If you have questions or problems, you may contact Roger at


  • A use of DSPPGMREF ...
    Member Herpelinck Luc shows you how to use the DSPPGMREF command to output program references to a file.
  • Determine the external call to programs
    If you want to find out the external call to programs and all files used in your RPG program or module, you can use DSPPGMREF.
  • Stay current with PTFs to prevent problems
    Installing new software and upgrades doesn't have to be a nightmare. Follow site expert Pascal Jacquemain's steps using the DSPFD and DSPPGMREF commands to ensure compatibility between files and programs.
  • Create a cross-reference file Using the DSPPGMREF command, site expert Tim Granatir tells you how to create a file that cross references all programs to each other.

Dig Deeper on iSeries ILE programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.