UDTF returning a Table on DB2 V5R4 with Dynamic SQ

Question

I must to write a UDF returning a Table. I’ve done it with Static SQL.

I’ve created Procedures preparing a Dynamic and Complex SQL sentence and returning a cursor.

But now I must to create a UDF with Dynamic SQL and return a table to be used with an IN clause inside other select.

It is possible on DB2 v5R4? Do you have an example?

Thanks in advance...

Answer 1

I don't have V5R4, but I have i 6.1 and V5R3. I have a 6.1 example, and I poked around in V5R3 to find how to make the same example work there. I can't guarantee V5R4, but this ought to be extremely close. Generating the working V5R3 code into 'Run SQL Scripts' gives this:

DROP SPECIFIC FUNCTION SQLEXAMPLE.DYNTABLE ; 

SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","SQLEXAMPLE" ; 

CREATE FUNCTION SQLEXAMPLE.DYNTABLE ( 
              SELECTBY VARCHAR( 64 ) ) 
   RETURNS TABLE ( 
              CUSTNBR DECIMAL( 6, 0 ) , 
              CUSTFULLNAME VARCHAR( 12 ) , 
              CUSTBALDUE DECIMAL( 6, 0 ) )   
   LANGUAGE SQL
   NO EXTERNAL ACTION 
   MODIFIES SQL DATA 
   NOT FENCED 
   DISALLOW PARALLEL
   CARDINALITY 100 

   BEGIN 

DECLARE DYNSTMT VARCHAR ( 512 ) ; 
DECLARE GLOBAL TEMPORARY TABLE SESSION.TCUSTCDT 
   ( CUSTNBR DECIMAL ( 6 , 0 ) NOT NULL , 
     CUSTNAME VARCHAR ( 12 ) , 
     CUSTBALDUE DECIMAL ( 6 , 2 ) ) 
WITH REPLACE ; 

SET DYNSTMT = 'INSERT INTO Session.TCustCDt SELECT t2.CUSNUM , (t2.INIT CONCAT '' '' CONCAT t2.LSTNAM) as FullName , t2.BALDUE FROM QIWS.QCUSTCDT t2 ' CONCAT CASE WHEN SELECTBY = '' THEN '' ELSE SELECTBY END ; 

EXECUTE IMMEDIATE DYNSTMT ; 

RETURN SELECT * FROM SESSION . TCUSTCDT ; 

END  ; 

COMMENT ON SPECIFIC FUNCTION SQLEXAMPLE.DYNTABLE 
    IS 'UDTF returning dynamic table' ;

And in 'Run SQL Scripts', the function can be called like this:

SELECT t1.* FROM TABLE(sqlexample.dyntable('WHERE STATE = ''TX''')) t1

The example is intended to work over IBM's sample QCUSCDT table in library QIWS. Most systems will have that table available. The table function returns values from two QCUSCDT columns, CUSNUM and BALDUE, directly through two of the table function's columns, CUSTNBR and CUSTBALDUE. The third table function column, CUSTFULLNAME, gets its value by a concatenation of INIT and LSTNAM from QCUSTCDT.

However, the part that apparently relates to the question is the SELECTBY parameter of the function. The usage example shows that a WHERE clause is passed in and used to help built a dynamic 'INSERT INTO... SELECT...statement. The example shows that rows containingSTATE='TX'` will be returned. A more complex clause could be passed in or the needed condition(s) could be retrieved from somewhere else, e.g., from another table.

The dynamic statement inserts rows into a GLOBAL TEMPORARY TABLE named SESSION.TCUSTCDT. The temporary table is defined in the function. The temporary column definitions are guaranteed (by the developer) to match the 'RETURNS TABLE` columns of the table function because no dynamic changes can be made to any of those elements. This allows SQL to handle reliably columns returned from the function, and that lets it compile the function.

The RETURN statement simply returns whatever rows are in the temporary table after the dynamic statement completes.

The various field definitions take into account the somewhat unusual definitions in the QCUSTCDT file. Those don't make great sense, but they're useful enough.