DSPPGMREF SQL Table Function for #IBMi

I've used DSPPGMREF for decades. There were some 3rd party software offerings along the way that did a better job of providing a complete object cross reference database. DSPPGMREF is what most people use unless they own one of those 3rd party packages.

To make DSPPGMREF a bit more contemporary, last year I created an SQL Table function named PGMREFS (Program References) that runs the DSPPGMREF CL command and returns the output as an SQL result-set. I continue to use this in SQL iQuery and ACS RUNSQL Scripts to get an up-to-date where-used list.

To use a CL command's OUTPUT FILE or "OUTFILE" support in SQL you can approach it in two ways:

1. Generate the output to an outfile, and then query that outfile using SQL.
2. Wrap the CL command in an SQL table function and return the outfile content as a result-set.

I prefer the 2nd approach because it is easier to use with today's pervasive SQL experience.

The PGMREFS table function accepts up to 4 parameter options, but only 1 of those parameters is required, the Object Name.

1. LIBRARY_NAME - VARCHAR(10) - Defaults to *LIBL
2. OBJECT_NAME - VARCHAR(10) - Required. Accepts generic names
3. OBJTYPE - VARCHAR(10) - Defaults to *ALL
4. DATA_OPTION - VARCHAR(10) - Defaults to *REPLACE

The simplest form of calling this table function follows:

SELECT * FROM TABLE( sqlTools.PGMREFS(OBJECT_NAME=>'INV*'));        

The Object_Name parameter is the only required parameter. So with the Library_Name parameter defaulting to *LIBL, the above statement creates a list of object references for all programs, service programs, *modules, and query definitions with the generic INV* name. This limits the scope to just objects who names begin with 'INV'.

Let's look at the code for the PGMREFS table function. Note that LinkedIn may word-wrap some lines even though it is embedded <CODE> in this article. So be sure to review it when you cut/paste into RDi, Visual Studio Code, or ACS.

CREATE or REPLACE FUNCTION sqltools.pgmRefs(
???   ?????????????? LIBRARY_NAME varchar(10) default '*LIBL',
??????   ??????????? OBJECT_NAME? varchar(10),?? -- Required parameter
?????????   ???????? objtype????? varchar(60) default '*ALL',
????????????   ????? data_option? varchar(10) default '*REPLACE'
                                       ??? )

??? RETURNS TABLE (
??????????????? objName varchar(10),
??????????????? objlib? varchar(10),
??????????????? objType varchar(10),
??????????????? objText varchar(50),
??????????????? objRefCount? int,???????? -- Objects Referenced Count
??????????????? refObjName? varchar(11),? -- Referenced Object name
??????????????? refOBJLib?? varchar(11),? -- Referenced Object Library
??????????????? refObjType? varchar(10),? -- Referenced Object Type
??????????????? refSrcName? varchar(11),? -- Ref Name as it appears in Src Pgm
??????????????? REFRCDFMT?? varchar(10),? -- Ref'd File's Record Format
??????????????? rcdfmt_count int,???????? -- Record Fmts used by this Ref'd File
??????????????? file_usage? varchar(50),? -- File Usage Input/Output/etc...
??????????????? LVLCHKID??? char(13),???? -- Ref'd Files' RcdFmt LVLCHK ID
??????????????? fields?????? int,???????? -- Field Count in Ref'd File
??????????????? sysname???? varchar(8),?? -- System name of Ref'd object
??????????????? retrieved_time timestamp(0)? -- Time DSPPGMREF was run
??? )
??? LANGUAGE SQL
??? MODIFIES SQL DATA
??? NOT FENCED
??? NOT DETERMINISTIC
??? SPECIFIC ST_PGMREFS

??? -- Date Format ISO is required for dates prior to 1940.
??? set option datfmt = *ISO, commit=*NONE

BEGIN
?? DECLARE ERROR_CODE BIGINT DEFAULT 0;
?? DECLARE pgmRefCmd varchar(256);
?? DECLARE MBROPT varchar(10) not null default '*REPLACE';
?? DECLARE repl?? varchar(10);
?? DECLARE OBJ_NAME VARCHAR(11) not null default '';
?? DECLARE gen????? int not null default 0;
?? DECLARE DTS_FMT varchar(26) NOT NULL DEFAULT 'YYYYMMDDHH24MISSFF12';
?? BEGIN
???? DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ERROR_CODE = 2;

???? if (DATA_OPTION is NOT NULL and length(data_option) > 0) THEN
???????? set repl = trim(L '*' FROM upper(data_option));
???????? if (length(repl) > 0) then
?????????? set repl = substr(repl, 1, 1);
?????????? if (repl in ('0','A','N')) then
???????????? set mbrOpt = '*ADD';
?????????? end if;
???????? end if;
???? end if;

???? -- Check OBJECT_NAME contains '%'
???? --? If it does, use *ALL for object name,
???? --? and add WHERE clause to the returned SELECT
???? --??? WHPNAM LIKE :objName
???? if (object_name is not null and length(object_name) > 1) then
?????? set gen = position('%', OBJECT_NAME);
?????? if (gen is NOT NULL and gen > 0 and gen <> length(object_name)) then
???????? set obj_name = '*ALL';
?????? elseif (gen is NOT NULL and gen = length(object_name)) then
???????? set gen = 0;
???????? set obj_name = rTrim(object_name,'% ') concat '*';
?????? else
???????? set gen = 0;
???????? set obj_name = object_name;
?????? end if;
???? end if;

???? if (obj_name = '') then
?????? set OBJ_NAME = '*ALL';
???? end if;

???? set pgmRefCmd = 'QSYS/DSPPGMREF PGM('
?????????????????????? CONCAT trim(LIBRARY_NAME)
?????????????????????? CONCAT '/' CONCAT OBJ_NAME CONCAT ') '
?????????????????????? CONCAT 'OBJTYPE(' CONCAT OBJTYPE CONCAT ') '
?????????????????????? CONCAT 'OUTPUT(*OUTFILE) '
?????????????????????? CONCAT 'OUTFILE(QTEMP/ST_PGMREF2) '
?????????????????????? CONCAT 'OUTMBR(*FIRST '
?????????????????????? CONCAT MBROPT CONCAT ')';

   -- Using QCMDEXC requires this UDTF to be "MODIFIES SQL DATA"
???? call qsys2.qcmdexc( pgmRefCmd );

?? END;


?? IF ERROR_CODE > 1 THEN
???? SIGNAL SQLSTATE '42704'
???? SET MESSAGE_TEXT = 'FAILURE on DSPPGMREF cmd inside PGMREF UDTF';
?? END IF;

?? RETURN SELECT
??????????? WHPNAM,
??????????? WHLIB,
??????????? cast(
??????????? CASE WHEN WHSPKG = 'P' THEN '*PGM'
???????????????? WHEN WHSPKG = 'S' THEN '*SQLPKG'
???????????????? WHEN WHSPKG = 'V' THEN '*SRVPGM'
???????????????? WHEN WHSPKG = 'M' THEN '*MODULE'
???????????????? WHEN WHSPKG = 'Q' THEN '*QRYDFN'
???????????????? ELSE WHSPKG
??????????? END as varchar(10)),
??????????? WHTEXT,
??????????? cast(WHFNUM as int),????? -- RefObj Count

??????????? CASE WHEN WHFNAM='1' THEN '*EXPR' ELSE WHFNAM END,
??????????? CASE WHEN WHLNAM='1' THEN '*EXPR' ELSE WHLNAM END,
????????????? WHOTYP,
??????????? CASE WHEN WHSNAM='1' THEN '*EXPR' ELSE WHSNAM END,
????????????? WHRFNM,
??????????? cast( WHRFNB as int),???? -- RcdFmt Count
????????????? cast(
????? --? 1=I,2=O,3=I/O,4=U,5=I/U,6=O/U,7=I/O/U,8=N/S,0=N/A
????? --? (Apparently DELETE isn't supported; returned as UPDATE)
????????????? CASE WHEN WHFUSG = 0 THEN ' '
?????????????????? WHEN WHFUSG = 1 THEN 'INPUT'
?????????????????? WHEN WHFUSG = 2 THEN 'OUTPUT'
?????????????????? WHEN WHFUSG = 3 THEN 'INPUT???? OUTPUT'
?????????????????? WHEN WHFUSG = 4 THEN 'UPDATE'
?????????????????? WHEN WHFUSG = 5 THEN 'INPUT???? UPDATE'
?????????????????? WHEN WHFUSG = 6 THEN 'OUTPUT??? UPDATE'
?????????????????? WHEN WHFUSG = 7 THEN 'INPUT???? OUTPUT??? UPDATE'
?????????????????? WHEN WHFUSG = 8 THEN 'N/S'
?????????????????? ELSE 'UNKNOWN'
????????????? END as varchar(30)),
????????????? WHRFSN,
????????????? WHRFFN,
????????????? WHSYSN,
????????????? timestamp_FORMAT(substr(WHDTTM,2,12),
?????????????????????????????? 'YYMMDDHH24MISS',0)
????? FROM QTEMP.ST_PGMREF2

??? -- Note: trim is used here so that the wildcard pattern
??? --?????? of '%XYZ' (which is 4 characters) matches a
??? --?????? WHPNAM value of 'EDTXYZ??? '? using LIKE which is
??? --?????? not good at length mismatchs.
????? WHERE trim(WHPNAM) LIKE
??????????? CASE WHEN GEN = 0 THEN trim(WHPNAM)
???????????????? ELSE upper(object_name)
??????????? END
????? ORDER BY whlib,whpnam,WHLNAM,WHFNAM;
END;        

The returned list of columns includes most fields from the outfile, but I've omitted a couple that aren't needed and translated a few others to make them more end-user friendly.

You can literally cut/paste the above SQL code into ACS RUNSQL Scripts, run it once, and you'll have it permanently on your system. Just make sure to specify the correct library name instead of SQLTOOLS when creating the UDTF. Also if you own SQL Tools (www.SQLiQuery.com/SQLTools) you already have this on your system and it includes a few extra result columns such as the referenced object's text, and the base object's last used date.

In SQL Tools we also have a Job Scheduler Entry Table Function that parses the job scheduler entry's "Command to run" and separates out any called program name. You can use it to add to your own where-used database. That table function is JOBSCDE_LIST.