Re: Retrieve Program References

Dennis wrote:
Thanks for your reply, but I don't think I follow. Let me paraphrase the
way I read this: If you collect all the data into one place, then SQL can
collect all the data into one place recursively.

What did I miss?


Dennis -

If you put all of the the program reference information into a single
outfile, then you can use a recursive SQL statement to print a formatted
list, and even indent the called programs in the listing showing a level
number.

Example:

Program TESTA:

C CALL 'TEST1'
C CALL 'TESTB'
C Seton LR

Program TESTB:

C CALL 'TEST1'
C CALL 'TESTC'
C Seton LR

Program TESTC:

C CALL 'TEST1'
C CALL 'TESTD'
C Seton LR

Program TESTE:

C CALL 'TEST5'
C CALL 'TESTF'
C Seton LR

Program TEST1:

C CALL 'TESTE'
C CALL 'TEST1'
C Seton LR

(note the recursive call to itself...)

Compiled all to library SJL, then:

DSPPGMREF PGM(SJL/*ALL)
OUTPUT(*OUTFILE)
OUTFILE(SJL/PGMREF)


Now use Query Management run this recursive SQL over the PGMREF file:
( cyclic_data will be '1' in the resulting query if a recursive item is
detected.)

WITH REF
(
Level
, WHPNAM
, WHFNAM
, WHOBJT
)

AS
(SELECT
1
, ROOT.WHPNAM
, ROOT.WHFNAM
, ROOT.WHOBJT

FROM &FILELIB/PGMREF ROOT

WHERE ROOT.WHPNAM = &PARENT
and ROOT.WHOBJT = 'P'

UNION ALL
SELECT
PARENT.LEVEL + 1
, CHILD.WHPNAM
, CHILD.WHFNAM
, CHILD.WHOBJT

FROM REF PARENT, &FILELIB/PGMREF CHILD

WHERE PARENT.WHFNAM = CHILD.WHPNAM
and CHILD.WHOBJT = 'P'
)

Search depth first by WHPNAM,WHFNAM Set Sort1
Cycle WHFNAM Set cyclic_data to '1' DEFAULT '0'

SELECT
Left('............',INT(Level)) CONCAT CHAR(Level) as Level
, WHPNAM
, WHFNAM
, WHOBJT
, cyclic_data

FROM REF

ORDER BY
Sort1