Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    33

    Question Unanswered: write UDF in cobol

    Hello,

    I want to have a UDF that checks if the input user_id exists in users table, if exists then return the user_id, otherwise return a default id. The logic is simple, but we will use it in a lot of places that is why I think of a UDF.

    DBA told me a simple UDF cannot do this, an advanced UDF written in cobol can do this, but he cannot help because cobol is out of his jurisdiction.

    So I am just curious about what's the difference between a simple UDF and an advanced UDF, what language the simple UDF can use?

    More curiousity is that if we provide a cobol program, how difficult (what steps) to make it as a UDF?

    We are using DB2 UDB 8.2 z/os

    Thanks,
    Jinse

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    How about a simple SQL-bodied function?
    Code:
    CREATE FUNCTION getUserId(id VARCHAR(20))
       RETURNS VARCHAR(20)
       RETURN COALESCE( ( SELECT user_id FROM some_table WHERE user_id = id ), 'default userid')
    The advantage would be that the UDF gets compiled into the overall SQL statement, giving the optimizer a better chance to optimize all pieces of the statement. An external UDF would break that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jul 2006
    Posts
    4
    Is there a PTF or something for DB2 V8.1 for z/OS that would allow that Create Function statement above to work. I am currently having this problem trying to imbed a Select statement in a UDF. When I try to execute the above create function statement I get a sqlcode -214 reason code 7.
    I have another post on this topic also: DB2 Create Function with a recursive CTE fails with an SQLCODE=-104

    Thanks
    Kevin

Posting Permissions

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