SQL recursive on DSPPGMREF

Hi SQL Gurus,

I am trying to modernize our old documentation tools.. .producing
listings...

I would like to use recursive/hierarchic SQL to produce
- a program tree
- a where used list
based on the output of : DSPPGMREF
PGM(TSTLIB/*ALL) OUTPUT(*OUTFILE) OBJTYPE(*ALL) OUTFILE(TSTLIB/PROGS)

I have been struggling all day but with not many success..

The above statement is working but only when the result set is limited
(about 500 lines..)
A small result set with a loop is detected and working.

SELECT DISTINCT
CAST(sys_connect_by_path ( WHFnam , ' : ' ) as varchar(200)) as
Pgm_Tree ,
CONNECT_BY_ISSCYCLE loop
FROM PROGS
WHERE WHOBJT = 'P'
START WITH WHFNAM =
'PGM001'
CONNECT BY NOCYCLE PRIOR WHFNAM = WHPNAM
ORDER BY Pgm_Tree

if the result set is larger, I have to abort the query or wait +/- 6 hours
until it is cancelled by MCH2803 (disk full... I have 600GB available when
I start the query and a disk upgrade is not a very welcome option)...


I have aslo tried CTE with the same result (OK for small set, infinite(?)
loop for bigger)

WITH pgmstruct (WHPNAM , WHFNAM , level , liste )
AS (
SELECT
f.whPnam , f.whfnam , 1,
CAST(f.whPnam CONCAT f.whfnam AS VARCHAR(2000))
FROM PROGS f
WHERE f.whpnam = 'PGM001' and f.whobjt = 'P'
UNION ALL
SELECT r.whpnam , b.whfnam , r.level + 1 ,
CAST(r.liste CONCAT b.whfnam AS VARCHAR(2000))
FROM pgmstruct r, PROGS b
WHERE r.whfnam = b.whpnam )
CYCLE whfnam SET cyclic_data TO '1' DEFAULT '0'
SELECT
whpnam , whfnam , liste , cyclic_data
FROM pgmstruct

any suggestion to fix/improve the code and make it work in SQL is
welcome...


Kind Regards, thank you,
Paul