Parsing Program CALLs with SQL

A short while ago, I posted an article that nicely wraps up the DSPPGMREF CL command as an SQL Table Function. This command, and table function is normally used for one of two purposes:

1. Determine which objects are called or opened in a Program.
2. Build a object where-used database.

One of the shortcoming of DSPPGMREF for the Where-used database is the fact that is only deals with compiled objects. This leaves off two important pieces of where-used information:

1. Menus
2. Job Scheduler Entries

The IBM i Menu object has no "Work with" or "Display" Menu Entries commands so you can only work with the menu source, and the basic Job Scheduler does have WRKJOBSCDE but to formally access the entries, you need to call an API. The *JOBSCD object itself is named QDFTJOBSCD which is actually an INDEX object. But with QSECURITY(40) or higher you can only get to it via APIs.

In #SQLTools we created two User Defined Table Functions. The RTVMNUOPT function returns a list of the menu options for *MENU objects along with their CL Command to run. This only works for non-UIM menus however. There is an RFE out there to provide this capability for UIM menus.

The JOBSCDE_LIST function lists the job scheduler entries. While there is a *JOBSCD IBM i object type, there is only one job scheduler object on the system. Perhaps the Advanced Scheduler provides ways to create additional Job Schedulers, but I don't know.

Here's an excerpt of the JOBSCDE_LIST function output results.

Note the CMD column that contains the Job Scheduler Command To Run. This can be a CL command or a CALL statement. Technically a CALL statement is a CL command also, but you get my meaning.

I've highlighted the Job Scheduler Entry's command to run in the above image. Note the 2nd entry's CALL statement. A normal DSPPGMREF command will miss this entry. So if you were updating that referenced program, you wouldn't know it was in the job scheduler.

Now you can use the JOBSCDE_LIST function to process those entries and complete the picture. Well... sort of. Just having the CALL statement isn't going to do much good unless you also PARSE that statement so you know the specific program being referenced. Don't worry, #SQLTools has you covered on that front as well.

The PARSE_CALL table function returns the program and library for a CALL statement. I believe it also works with SBMJOB commands so if the job scheduler entry (for some reason) was a SBMJOB, PARSE_CALL would be able to look at the CMD parameter of SBMJOB and return the program and library name.

Let's process that 2nd Job Scheduler Entry with the PARSE_CALL table function.

Ha! Tricked you. The JOBSCDE_LIST function itself calls the PARSE_CALL function under the covers for the CMD column so you only need to include two additional columns named CALLEDPGM and CALLEDPGM_LIB. Pretty cool, huh?

Okay but what if you have something like a Menu option (from RTVMNUOPT) or your own in-house-built menu system? Then you would use the PARSE_CALL yourself.

In the above example, I pass the CALL to the ARSTMT program to PARSE_CALL. I get back two columns: OBJNAME and OBJLIB

In your own code, you would simply pass in a host variable containing the CALL statement. Then you'd take the OBJNAME and OBJLIB and store that in your database where you save the DSPPGMREF output. Note, you would need to add a object type of *PGM when adding the entries to your own cross reference database as PARSE_CALL only parses the statement.

Let's try this on the RTVMNUOPT function also found in #SQLTools.

First the basic output for RTVMNUOPT looks like the following:

As you can see, this is an old S/36-style menu that still uses option 24 to sign off. Which means that I have to type in 90, swear, and then type in 24 every time I use it. But our focus here is on the 4 menu options that have CALL statement behind them. So let's parse them and get their actual program and library names:

I've added a LATERAL join to the original RTVMNUOPT function. Now each menu option that is returned calls PARSE_CALL to extract the program and library associated with the CALL. I then omit any entry that is NULL so we only see the options with program calls--the others aren't of interest to us for our home-grown cross reference database.

Call to Action

Remember that phrase from the old days when everyone was doing PowerPoint presentations and they'd recommend the outline/flow of the presentation conclude it with a "Call to Action"? I do...

I've spent months (years?) building a collection of SQL functions that replace every useful IBM API I've used over the years. Whether its an object list, member list, object description, system value, or even something as obscure as a Menu or the Job Scheduler entries. That collection is now available in #SQLTools for your system. You just need to buy a copy--which costs less than a used iPhone.

One of the cool things we try to design into everything we do is to make it work on V7R2 and later. So while sometimes other interfaces become available on much later releases, you can still do virtually everything in #SQLTools on V7R2, V7R3, V7R4 and even V7R++ without worrying about availability.

Check out #SQLTools today and yes, buy a copy so we can continue doing good stuff for #IBMi into the future.

https://www.SQLiQuery.com

That's all there is to it!