RE: Program explosion report

Hi Dan

I use the following UDTF which produces an indented hierarchical list of objects and programs/modules/service programs that call them. So, for example, if PGM3 was called by PGM2A and PGM2B and PGM2B was called by PGM1 then the result would look like:

Level Program
1 PGM3
2 PGM2A
2 PGM2B
3 PGM1

The source for the UDTF is shown below:

-- -------------------------------------------------------------------
-- * * * P R O C E D U R E C R E A T I O N D E T A I L S * * *
-- -------------------------------------------------------------------
-- RUNSQLSTM SRCFILE(MASON#JO/QSQLSRC)
-- SRCMBR(UTF/USEDBY)
-- COMMIT(*NONE)
--
-- Populate file PGMREFS using command:
--
-- DSPPGMREF PGM(library/*ALL) OUTPUT(*OUTFILE) OBJTYPE(*PGM)
-- OUTFILE(QTEMP/PGMREFS)
--
-- Run in SQL using:
--
-- SELECT * FROM TABLE(USEDBY('program')) T01
-- -------------------------------------------------------------------

drop function USEDBY;


create function USEDBY (iObject varchar(10))
returns table (Level dec(5), Object char(50),
UsedBy char(10), Desc char(50),
Dup char(1)
)
language SQL

begin

return

with recursive RefHierarchy
(Level, RefObj, RefObjTyp, RefLib, Pgm, Desc) as
(select 1 as Level, WHFNAM, WHOTYP, WHLNAM, WHPNAM, WHTEXT
from PGMREFS
where WHFNAM = iObject
and WHLNAM not in('QSYS', 'QGPL')
union all
select Level+1 as Level,
REF.WHFNAM, REF.WHOTYP, REF.WHLNAM, REF.WHPNAM,
REF.WHTEXT
from RefHierarchy HDR,
PGMREFS REF
where HDR.Pgm = REF.WHFNAM
and REF.WHOTYP in ('*PGM', '*MODULE', '*SRVPGM'))

search depth first by RefObj, Pgm set NewOrder
cycle RefObj, Pgm
set Duplicate to '*' default ' '

select
Level as RefLevel,
char(repeat (' ',Level-1) || RefObj, 50) as Object,
Pgm, Desc, Duplicate
from RefHierarchy
where RefLib not in ('QSYS', 'QGPLV')
and RefObj <> ' '
and substr(Pgm, 1, 2) <> 'A0'
and RefObj <> Pgm
and Duplicate <> '*'
order by NewOrder;

end


All the best
Jonathan



-----Original Message-----

date: Thu, 7 May 2015 11:36:45 -0400
from: Dan <dan27649@xxxxxxxxx>
subject: Program explosion report

Currently, I query the outfile of DSPPGMREF *ALL to what calls program A, and drill backwards until I get no results. And, by "drill backwards", I mean that I manually query each one. I.e., if program A is called by programs B and C, then I run the same query for B and for C. Obviously, for deeply nested calls, this can be an onerous exercise.

I know there are commercial products available, but these are not options for me.

I'm wondering if there's a way to write a query that does this "recursion"
(?) for me. Thinking of a "Bill of Materials" type of report, I guess.

- Dan



Proud partner of The Ageas Bowl and the Ageas Salisbury International Arts Festival.

Registered Address: Ageas House, Hampshire Corporate Park, Templars Way, Eastleigh, Hampshire, SO53 3YA Registered Number: 354568 England

Authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and the Prudential Regulation Authority

This e-mail together with any attachments are intended for the addressee only and may be private and confidential. If you are not the intended recipient, or the person responsible for delivering it to the intended recipient, you must not open any attachments, or copy, disclose, distribute, retain or use this e-mail, including any attachments, in any way whatsoever; please return it to us immediately using the reply facility on e-mail.

Consider the environment and think before you print this email.