Hello,

I'm trying to transform some User Defined Functions from a DB2 V8 on Linux to DB2 z/OS V8. It works a bit different and I didn't find useful descriptions in the IBM books. Does anyone know some good documentation about creating UDF's in DB2 z/OS ?

Thanks for Herlp
Dirkk

what kind of UDF you focus on?
SCALAR SOURCED UDF
SCALAR SQL UDF
or
TABLE EXTERNAL UDF(with host language application)?
SCALAR EXTERNAL UDF(with host language application)?

Hi,

UDF's from DB2 Linux are scalar sql.
Here is one example:

create function testbo(aBONAME VARCHAR(254) )
RETURNS VARCHAR(254)
LANGUAGE SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN ATOMIC
declare tTab varchar(254);


set tTab = (select tablename from amt_genericbo
where boname=aBONAME);
if tTab is NULL
then set tTab = 'NOT FOUND';
end if;

return(tTab) ;
end

DB2 z/OS does not yet have SQL PL for UDFs. However, you can solve pretty much any problem with SQL as it was invented. For example, your UDF can simply be written like this:

unfortunately, I don't think DB2/zos allow scalar full-select in such scalar in-line udf.

You are right, it does not work.
If UDF do not support PL I think I have to look for a different kuind of solution for this.
Thanks for your answers

if you have to use a UDF, you can re-write the logic into a
SCALAR EXTERNAL UDF, by using a simple host language application.

Or if you are on DB2/z v9, you can use SQL PL in native SQL procedure.

My bad - you are right and SQL UDFs don't allow scalar fullselects. I mixed this up with DB2 LUW.