Hi SQL Gurus,
I am trying to modernize our old documentation tools.. .producing
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.
CAST(sys_connect_by_path ( WHFnam , ' : ' ) as varchar(200)) as
WHERE WHOBJT = 'P'
START WITH WHFNAM =
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 )
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'
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'
whpnam , whfnam , liste , cyclic_data
any suggestion to fix/improve the code and make it work in SQL is
Kind Regards, thank you,