Re: SQL Recursion and UDFT...

Hi Rob,

There's a good chance you should be able to do what you want, although I've
never tried using two UNION ALL statements in recursive SQL.

One risk is whether or not you get any locking deadlocks (hangs) recreating
table QTEMP/PGMREFPGM over and over within the scope of a single SQL
statement. Since it is a QTEMP object you're probably OK, but objects
modified inside functions can be subject to locking side effects (deadlocks
in certain cases).

The error is because "x.whfnam" is not in scope when accessed in the second
function call reference (specifically the "x" correlation name is not in
scope).

I think this should be closer to what you want:

WITH pgms (level, whfnam) AS (

CTE_PARMS ( level, main_program ) as (
VALUES(0,'SB0010')
)

-- Initialization

SELECT p.level, p.main_program
FROM CTE_PARMS p

UNION ALL

SELECT p.level + 1, i.whfnam
FROM CTE_PARMS p
JOIN TABLE(RROGERSONW.Get_Called_Program_Ref( p.main_program )) i

UNION ALL

-- Recursion
SELECT a.level + 1, r.whfnam
FROM pgms a
CROSS JOIN TABLE(RROGERSONW.Get_Called_Program_Ref( a.whfnam )) r
fetch first 100 rows only

)

-- Final/Main Select
SELECT level, whfnam
FROM pgms
WHERE level <= 3
ORDER BY 1;

Mike


date: Wed, 31 Jan 2018 08:45:58 -0500

from: Robert Rogerson <rogersonra@xxxxxxxxx>
subject: SQL Recursion and UDFT...

Hi all,

I'm working on (at this point) an exercise for personal learning. My
final goal is to pass a program name to a program/procedure and this
will
return as the files accessed by the job stream.

I created a UDTF

CREATE OR REPLACE FUNCTION
rrogersonw.Get_Called_Program_Ref(main_program VarChar(10))
RETURNS TABLE (whfnam Char(10) )
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE CLCmd VarChar(256) Not NULL Default '';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE ERROR_HIT INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET ERROR_HIT = 1;
DROP TABLE QTEMP/pgmrefpgm;
CREATE TABLE QTEMP/pgmrefpgm as (
SELECT * FROM QADSPPGM ) WITH NO DATA;
END;

SET CLCmd = 'DSPPGMREF PGM(' concat main_program concat ') '
Concat ' OUTPUT(*OUTFILE) '
Concat ' OUTFILE(QTEMP/PGMREFPGM) '
Concat ' OUTMBR(*FIRST *REPLACE) ';

CALL QCMDEXC(CLCmd, LENGTH(CLCmd));

RETURN SELECT whfnam
FROM QTEMP/pgmrefpgm
WHERE whotyp = '*PGM'
AND SUBSTRING(whfnam, 1, 1) <> 'Q'
;
END;

So if I call my UDTF. Also note that I am only selecting *PGM types to
keep it easier at the start.

SELECT *
FROM TABLE(RROGERSONW.Get_Called_Program_Ref('SB0010', 1)) x;

It correctly returns a table with the programs called by SB0010. My
next
step is to recursively call the UDTF for the called programs.

This is the sql statement I'm trying to use.

-- Use recursion to find all the programs in a jobstrean
WITH pgms (level, whfnam) AS (

-- Initialization
VALUES(0,'SB0010')

UNION ALL
SELECT 1, x.whfnam
FROM TABLE(RROGERSONW.Get_Called_Program_Ref('SB0010')) x

-- Recursion
UNION ALL
SELECT level+1, a.whfnam
FROM TABLE(RROGERSONW.Get_Called_Program_Ref(x.whfnam)) a
JOIN pgms b
ON a.whfnam = b.whfnam
fetch first 100 rows only)

-- Final/Main Select
SELECT level, whfnam
FROM pgms
WHERE level <= 3
ORDER BY 1;

I get the error

SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable WHFNAM not found.

When I try to run the statement.

Is it even possible to do what I'm trying to do? If so, how do I
qualify
whfnam? Or can someone show me how I should be writing the statement.
I tried to write the query based on a presentation Brigitta have at
Commons in Europe. ([1]https://www.youtube.com/watch?v=VeMAmMYCHI0)

Thanks for any help.

Rob

References

Visible links
1. https://www.youtube.com/watch?v=VeMAmMYCHI0