Re: Program explosion report -- MIDRANGE-L

Jhonathan,
Very Nice!
What would I need to do to add the program library to the output?

Jeff Young
Sr. Programmer Analyst

On Thu, May 7, 2015 at 12:22 PM, Jonathan Mason <Jonathan.Mason@xxxxxxxxxxx>
wrote:

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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.