IBM continues to expand the scope of DB2 services, and two of them combine to provide excellent change management.

In a recent article, I wrote about using an IBM DB2 for i service called DISPLAY_JOURNAL to retrieve object deletion information from the QAUDJRN journal. This service provides a way to identify when a program has been deleted. This article will expand on that by providing a way to compare the programs in two libraries to identify which programs are missing or otherwise different.

Simple Change Management

Back in the old days, the first part of any change management analysis started with the DSPOBJD command, using OUTPUT(*OUTFILE) to create a list of all the objects in a library. The file would contain the object’s last used and change dates, and, for OPM programs, we could even get the source information. That was usually enough for simple change management. But with the advent of ILE, the source information became a little more difficult to come by. Since an ILE program could consist of any number of modules, we had to dig into the program in more detail, and for a long time the only way to do that was to use the QBNLPGMI API. When I had to build analysis tools that went that far, since I had to use one API anyway, I found it more consistent to use APIs for everything, so I replaced DSPOBJD with the QUSLOBJ API. I ended up with a program that called QUSLOBJ and looped through all of its entries. For each ILE program, I called QBNLPGMI and finally wrote the results out to a file for later comparison.

Fast forward to today; the basic concepts remain the same, but now I can use IBM DB2 for i services instead of APIs. The OBJECT_STATISTICS table function replaces QUSLOBJ to get a list of objects, while the BOUND_MODULE _INFO view replaces QBNLPGMI. I combine those into a single SQL statement to retrieve all the information I can in one query. The query to see all the entries for a single library looks like this:

SELECT * FROM TABLE(OBJECT_STATISTICS('MYLIB','ALL'))

LEFT OUTER JOIN BOUND_MODULE_INFO ON                        

   (PGM_LIB, PGM_NAME, BDMOD) = (OBJLIB, OBJNAME, OBJNAME)

This query is relatively straightforward, but it does have one nuance that I’d like to explain. The simple part is that I’m selecting all the objects in the library MYLIB, and then joining that list to the BOUND_MODULE_VIEW. The nuance is that I only select those modules where the module name matches the program name. That’s the default configuration of a program created using CRTBNDRPG or CRTBNDCL. You don’t have to create your program that way; ILE lets you combine different modules and then name the program something completely different from any of them. But this is a simple homegrown query, and so the simple route is enough.

The Results

To demonstrate, I created library MYLIB, and in that library I created a source file named MYSRC. In that source file, I created two members, MYPGM and MYILEPGM. The former is an RPG program; the latter is an ILE RPG program. I compiled both and then ran a slimmed down version of the query. Rather than including every field (SELECT *), I instead opted to select only the program identifying fields and the source information. This is the focused query I ended up with:

SELECT OBJNAME, OBJTYPE, OBJLIB,

       SOURCE_FILE, SOURCE_LIBRARY, SOURCE_MEMBER, SOURCE_TIMESTAMP
       SRCLIB, SRCFILE, SRCMBR, SRC_CHGTS                

FROM TABLE(OBJECT_STATISTICS('MYLIB','ALL')) O              

LEFT OUTER JOIN BOUND_MODULE_INFO ON                        

   (PGM_LIB, PGM_NAME, BDMOD) = (OBJLIB, OBJNAME, OBJNAME)

The first three fields identify the object, the next four are the object source information from OBJECT_STATISTICS, and the last four are the module source information from BOUND_MODULE_INFO. The module source fields are only relevant for ILE programs, while the others are relevant for everything else. Here are my results:

Figure 1: Object query showing the OPM source information

Figure 2: Object query showing the ILE source information

The first entry is the ILE program, called MYILEPGM. That program has nothing (null values) in the object source fields, but it does have source information in the ILE source fields. The next entry is for the OPM program, MYPGM. It is the polar opposite of MYILEPGM: MYPGM has values in the object source fields, but nulls in the ILE source fields. Both of these are exactly what we expect. And finally, we have the MYSRC physical source file itself. It was created using the CRTSRCPF command, not from any source, so both sets of source fields are null. This is also to be expected for any non-compiled object.

How Do We Use This?

One obvious use of this information is to compare the source date for a program with the current date on the member in the source file. If they don’t match, you have a problem. If the date in the program is earlier than the date in the source file, then your program is out of date. But if the date in the program is later than the date in the source file, then you have a bigger problem because you’re missing the latest version of the source.

But that’s really not the focus of this article. Instead, I want to focus on change management and, more specifically, on comparing the objects in multiple libraries. The first use is to compare the objects in two libraries. You can quickly identify objects that exist in one library and not another. Once you’ve done that, you can use the QAUDJRN queries from my previous article to see if the missing program was deleted or just never created.

Another case occurs when you have multiple environments. For example, you might have a production library and a test library. While there are certain programs that will be different (the ones that are currently being tested), sometimes old programs get left in the test library. Or sometimes a quick fix is put in place but doesn’t get formally installed in other environments. With the information from this query, you can easily compare the results from two libraries to make sure they’re in sync.

What I implemented recently, though, was really cool. You can actually run this query on another partition entirely using three-part naming. The query is almost the same; it looks like this:

SELECT OBJNAME, OBJTYPE, OBJLIB,

       SOURCE_FILE, SOURCE_LIBRARY, SOURCE_MEMBER, SOURCE_TIMESTAMP
       SRCLIB, SRCFILE, SRCMBR, SRC_CHGTS                

FROM TABLE(OBJECT_STATISTICS('MYLIB','ALL')) O              

LEFT OUTER JOIN OTHERPART.QSYS.BOUND_MODULE_INFO ON                        

   (PGM_LIB, PGM_NAME, BDMOD) = (OBJLIB, OBJNAME, OBJNAME)

The only change is that I qualified the reference to BOUND_MODULE_INFO, specifying the partition (OTHERPART) that I wanted to query. Because I specified the partition, I also had to specify the library, but that’s all that’s needed. I found this to be incredibly useful for keeping environments consistent across machines.

Only the Beginning

This is only the beginning of what would be needed for formal change management, but it’s the underlying data mining that enables all the rest of the analysis. I hope it helps you manage your environment!