UDF with scalar SQL

Michel Helg

UDF with scalar SQL
Hi all

I have tried to implement a UDF with SQL (DB2 z/OS, V9.1 NFM). The
samples that I could find in the manuals (and Listserv) are pretty simple and I
wonder whether it is also possible to use an SQL which is a bit more
complex and uses *SQL control statements* and *cursors*.
Here my example which works with a simple SQL but doesn't work with the
SQL below.

SET CURRENT SQLID='C125083';
DROP FUNCTION TEST2;
SET CURRENT PATH
= "SYSIBM","SYSFUN","SYSPROC","C125083" ;
CREATE FUNCTION C125083.TEST2
(ENTITY_ID INTEGER, ZW_SUFFIX VARCHAR(100))
RETURNS VARCHAR(100)
SPECIFIC TEST2
LANGUAGE SQL
NOT DETERMINISTIC PARAMETER CCSID EBCDIC CALLED ON
NULL INPUT
READS SQL DATA NO EXTERNAL ACTION
RETURN ZW_SUFFIX;

FOR SUF AS CUR CURSOR FOR
SELECT SF.MFRO_SUFFIX FROM ITAR.STAMM_ITSOBJS SF
WHERE SF.ENTITY_ID = ZW_ENTITY_ID
DO
IF ZW_SUFFIX NOT 'BLANK'
THEN ZW_SUFFIX = ZW_SUFFIX CONCAT ','
ZW_SUFFIX = ZW_SUFFIX CONCAT SF.MFRO_SUFFIX;
END FOR;

COMMIT;

The intention is to read a table
The Data is like this: ENTITY_ID = V123, MFRO_SUFFIX = A
The Data is like this: ENTITY_ID = V123, MFRO_SUFFIX = B
The Data is like this: ENTITY_ID = V123, MFRO_SUFFIX = C
The result ZW_SUFFIX should be: 'A,B,C'

With an external function it works.

Thanks to all contributing.
Regards Michel

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Robert Catterall

Re: UDF with scalar SQL
(in response to Michel Helg)
Michel,

You could do what you are wanting to do with a user-defined SQL function in
a DB2 for LUW environment, but not with a DB2 for z/OS system. The body of a
DB2 for LUW SQL UDF can contain a dynamic compound SQL statement (multiple
statements, such as a FOR...DO and a RETURN, delimited by BEGIN and END and
treated as one executable block). The body of a DB2 for z/OS SQL scalar UDF,
on the other hand, is contained within the RETURN statement itself. The
expression in that return statement can't contain a SELECT (neither a
fullselect nor a subselect), and can't include a column name.

I expect that with some future release, DB2 for z/OS SQL UDFs will have the
functionality that you have today with DB2 9 for LUW. For now, if you want
to provide the functionality you've described in a routine written in SQL, I
believe you'll have to do it by way of a SQL procedure versus a SQL
function.

Robert


On Thu, Jan 14, 2010 at 4:10 AM, Michel Helg <[login to unmask email]>wrote:

> Hi all
>
> I have tried to implement a UDF with SQL (DB2 z/OS, V9.1 NFM). The
> samples that I could find in the manuals (and Listserv) are pretty simple
> and I
> wonder whether it is also possible to use an SQL which is a bit more
> complex and uses *SQL control statements* and *cursors*.
> Here my example which works with a simple SQL but doesn't work with the
> SQL below.
>
> SET CURRENT SQLID='C125083';
> DROP FUNCTION TEST2;
> SET CURRENT PATH
> = "SYSIBM","SYSFUN","SYSPROC","C125083" ;
> CREATE FUNCTION C125083.TEST2
> (ENTITY_ID INTEGER, ZW_SUFFIX VARCHAR(100))
> RETURNS VARCHAR(100)
> SPECIFIC TEST2
> LANGUAGE SQL
> NOT DETERMINISTIC PARAMETER CCSID EBCDIC CALLED ON
> NULL INPUT
> READS SQL DATA NO EXTERNAL ACTION
> RETURN ZW_SUFFIX;
>
> FOR SUF AS CUR CURSOR FOR
> SELECT SF.MFRO_SUFFIX FROM ITAR.STAMM_ITSOBJS SF
> WHERE SF.ENTITY_ID = ZW_ENTITY_ID
> DO
> IF ZW_SUFFIX NOT 'BLANK'
> THEN ZW_SUFFIX = ZW_SUFFIX CONCAT ','
> ZW_SUFFIX = ZW_SUFFIX CONCAT SF.MFRO_SUFFIX;
> END FOR;
>
> COMMIT;
>
> The intention is to read a table
> The Data is like this: ENTITY_ID = V123, MFRO_SUFFIX = A
> The Data is like this: ENTITY_ID = V123, MFRO_SUFFIX = B
> The Data is like this: ENTITY_ID = V123, MFRO_SUFFIX = C
> The result ZW_SUFFIX should be: 'A,B,C'
>
> With an external function it works.
>
> Thanks to all contributing.
> Regards Michel
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
> presentations!
> DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
> Warehouses, - among
> many more categories of help waiting for you!
> Whether you are an old hand or a DB2 newbie, we have presentations for
> every level.
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
> the home of IDUG's DB2-L
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Dave Nance

Re: UDF with scalar SQL
(in response to Robert Catterall)
Hi Michael,
   As Robert has already pointed out what you are attempting here is not possible as you have written it. What you are really after is a recursive SQL statement. There are many examples on the web, quite a few on DBFORUM as well.
 
David Nance
 




________________________________
From: Michel Helg <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, January 14, 2010 4:10:05 AM
Subject: [DB2-L] UDF with scalar SQL

Hi all

I have tried to implement a UDF with SQL (DB2 z/OS, V9.1 NFM). The
samples that I could find in the manuals (and Listserv) are pretty simple and I
wonder whether it is also possible to use an SQL which is a bit more
complex and uses *SQL control statements* and *cursors*.
Here my example which works with a simple SQL but doesn't work with the
SQL below.

SET CURRENT SQLID='C125083';                                   
DROP FUNCTION TEST2;                                           
SET CURRENT PATH
= "SYSIBM","SYSFUN","SYSPROC","C125083" ;     
CREATE FUNCTION C125083.TEST2                                   
  (ENTITY_ID INTEGER, ZW_SUFFIX VARCHAR(100))                 
  RETURNS VARCHAR(100)                                         
  SPECIFIC TEST2                                               
  LANGUAGE SQL                                                 
  NOT DETERMINISTIC  PARAMETER CCSID EBCDIC  CALLED ON
NULL INPUT
  READS SQL DATA NO EXTERNAL ACTION                             
  RETURN ZW_SUFFIX;                                             

    FOR SUF AS CUR CURSOR FOR                                 
      SELECT SF.MFRO_SUFFIX FROM ITAR.STAMM_ITSOBJS SF         
        WHERE SF.ENTITY_ID = ZW_ENTITY_ID                     
      DO                                                       
        IF ZW_SUFFIX NOT 'BLANK'                               
          THEN ZW_SUFFIX = ZW_SUFFIX CONCAT ','                 
        ZW_SUFFIX = ZW_SUFFIX CONCAT SF.MFRO_SUFFIX;           
    END FOR;                                                   

COMMIT; 

The intention is to read a table
The Data is like this: ENTITY_ID = V123, MFRO_SUFFIX = A
The Data is like this: ENTITY_ID = V123, MFRO_SUFFIX = B
The Data is like this: ENTITY_ID = V123, MFRO_SUFFIX = C
The result ZW_SUFFIX should be: 'A,B,C'

With an external function it works.

Thanks to all contributing.
Regards Michel

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *  http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L





_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L