Results 1 to 8 of 8

Thread: UDF in DB2 z/OS

  1. #1
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    8

    Question Unanswered: UDF in DB2 z/OS

    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

  2. #2
    Join Date
    May 2003
    Posts
    113
    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)?




    Quote Originally Posted by drosemeyer
    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

  3. #3
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    8

    scalar SQL

    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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:
    Code:
    CREATE FUNCTION testbo( aBONAME VARCHAR(254) )
       RETURNS VARCHAR(254)
       LANGUAGE SQL
       NOT DETERMINISTIC
       NO EXTERNAL ACTION
       READS SQL DATA
       RETURN COALESCE (
                 ( SELECT tablename
                   FROM   amt_genericbo
                   WHERE  boname = aBONAME ),
                 'NOT FOUND' )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Posts
    113
    unfortunately, I don't think DB2/zos allow scalar full-select in such scalar in-line udf.

    Quote Originally Posted by stolze
    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:
    Code:
    CREATE FUNCTION testbo( aBONAME VARCHAR(254) )
       RETURNS VARCHAR(254)
       LANGUAGE SQL
       NOT DETERMINISTIC
       NO EXTERNAL ACTION
       READS SQL DATA
       RETURN COALESCE (
                 ( SELECT tablename
                   FROM   amt_genericbo
                   WHERE  boname = aBONAME ),
                 'NOT FOUND' )

  6. #6
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    8

    Does not work

    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

  7. #7
    Join Date
    May 2003
    Posts
    113
    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.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    My bad - you are right and SQL UDFs don't allow scalar fullselects. I mixed this up with DB2 LUW.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •