I asked IBM if the new OBJECT_STATISTICS SQL Service could provide the ability to do a long needed Service Program and/or Module Where-Used utility. They said "Thanks for the suggestion." So I took that as a "no". :)

Honestly, there so much coming out in SQL for IBM i today, I have no idea how they manage all of it. In fact, SQL features seem to be the most remarkable accomplishment (on the surface) in new Releases of th OS. And now they're trying to wrap some of the APIs so that we can query these things? Genious! The best part is, if you have a web-based Query tool, like the one in the new ACS or my own SQL Query File, you can run these SQL service from the Web and get a resultset that look remakably like the traditional information you get on the green screen, but a lot better looking in versatile.

So today, I got busy on building one of my own. The goal was to provide a *SRVPGM and *MODULE WHERE-USED SQL Service. But to do that I needed an Object Structure SQL Server--one that accepted a program name and listed the *MODULE and *SRVPGM objects contained in or referenced by the object.

Basically this means I needed to create an SQL UDTF (user-defined SQL table function) that returns the Object Structure as an SQL result dataset. I call OBJSTRUCT.

UDTF OBJSTRUCT

To download the SQL Query File product (with a free 60-day trial built-in) click here.

I wrote the UDTF definition first, and made it an external routine so that I could write the guts of it in C++. The input is:

1. Object - The program (generic, full or *ALL) name that is to have its referenced objects listed.
2. Library - The library name (full or *ALL, *ALLUSR, *LIBL) that contains the objects.
3. Object Type - The (optional) Object Type. Valid entries are *PGM or *SRVPGM. *ALL may also be specified and is the default.

From that input, the UDTF OBJSTRUCT( object, library ) evokes the underlying QBNLxxx APIs to list the objects referenced by each of the program and service programs specified on the Object parameter.

This API returns just the following values, all of which are returned as VARCHAR columns (fields) with the exceptions the the obvious date/time fields, from the UDTF.

1. OBJNAME - Object Name - VARCHAR(10)
2. OBJLIB - Object Library - VARCHAR(10)
3. OBJTYPE - Object Type (*PGM or *SRVPGM) - VARCHAR(10)
4. REFOBJNAME - Referenced Object Name - VARCHAR(10)
5. REFOBJLIB - Referenced Object Library - VARCHAR(10)
6. REFOBJTYPE - Referenced Object Type (*MODULE or *SRVPGM) - VARCHAR(10)
7. SRCFILE - Source File Name that created the object (for *MODULE objects only) - VARCHAR(10)
8. SRCLIB - Source File Library Name - VARCHAR(10)
9. SRCMBR - Source File Member Name - VARCHAR(10)
10. SRCTYPE - Source Type - VARCHAR(10)
11. SRCMBRLASTCHGDATE - Source Member Last Changed Date when module was bound - DATE
12. SRCMBRLASTCHGTIME - Source Member Last Changed Time when module was bound - TIME
13. DEBUG - Debug Information (*YES or *NO) (Doesn't seem to be correct) - VARCHAR(10)
14. REFOBJCRTDATE - Referenced Object (*MODULEs only) Creation Date - DATE
15. REFOBJCRTTIME - Referenced Object (*MODULEs only) Creation Time - TIME
16. SRVPGMACT - Service program activation - VARCHAR(10)
17. SIGNATURE - Service Program signature - VARCHAR(32)

With the exception of the Signature and the date or time columns, all columns are VARCHAR(10) and Signature is VARCHAR(32). If the signature is plain text, it is returned as is (16-character signature) otherwise it is converted to hex and returned as a 32-byte hexadecimal signature. This is similar to what you see on the DSPPGM command.

The OBJSTRUCT UDTF is a useful tool that can be used to explode *PGM tand *SRVPGM objects to see what they are made of, or we can created a *SRVPGM/*MODULE Where-Used utility as easy as an SQL SELECT statement. Here's an example:

To list all the user-created programs and service programs in all user libraries that use the COZTOOLS service program, you could use the following:

SELECT * FROM table( objstruct('*ALL','*ALLUSR') ) WU   
 WHERE refobjLib = 'COZTOOLS' and refObjType = '*SRVPGM'     
       and objlib <> 'COZTOOLS'
 ORDER BY objlib,objname  

This will list any *PGM or *SRVPPGM objects that that reference the COZTOOLS *SRVPGM in the COZTOOLS library.

We also ship a macro that does essentially the same thing as the above SQL SELECT statement:

RUNSQLF *COZXREF

The RUNSQLF (Run SQL using Query File) command allows you to run any SQL statement or specify a source file member that contains the SQL script you'd like to run. The OBJSTRUCT UDTF is one tool in a rather large inventory of tools that can simplify just about everything you do on IBM i. Check it out today at www.sqlQueryFIle.com