Wed, May
2 New Articles

Practical SQL: Change Management with SQL

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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:


LEFT OUTER JOIN BOUND_MODULE_INFO ON                        


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:


       SRCLIB, SRCFILE, SRCMBR, SRC_CHGTS                


LEFT OUTER JOIN BOUND_MODULE_INFO ON                        


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:

Practical SQL: Change Management with SQL - Figure 1 

Figure 1: Object query showing the OPM source information

Practical SQL: Change Management with SQL - Figure 2 

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:


       SRCLIB, SRCFILE, SRCMBR, SRC_CHGTS                




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!



Joe Pluta

Joe Pluta is the founder and chief architect of Pluta Brothers Design, Inc. He has been extending the IBM midrange since the days of the IBM System/3. Joe uses WebSphere extensively, especially as the base for PSC/400, the only product that can move your legacy systems to the Web using simple green-screen commands. He has written several books, including Developing Web 2.0 Applications with EGL for IBM i, E-Deployment: The Fastest Path to the Web, Eclipse: Step by Step, and WDSC: Step by Step. Joe performs onsite mentoring and speaks at user groups around the country. You can reach him at This email address is being protected from spambots. You need JavaScript enabled to view it..

MC Press books written by Joe Pluta available now on the MC Press Bookstore.

Developing Web 2.0 Applications with EGL for IBM i Developing Web 2.0 Applications with EGL for IBM i
Joe Pluta introduces you to EGL Rich UI and IBM’s Rational Developer for the IBM i platform.
List Price $39.95

Now On Sale

WDSC: Step by Step WDSC: Step by Step
Discover incredibly powerful WDSC with this easy-to-understand yet thorough introduction.
List Price $74.95

Now On Sale

Eclipse: Step by Step Eclipse: Step by Step
Quickly get up to speed and productivity using Eclipse.
List Price $59.00

Now On Sale



Support MC Press Online

$0.00 Raised:

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: