write UDF in cobol

jinsezh · #1 (**permalink**) 03-16-07, 12:19

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

stolze · #2 (**permalink**) 03-16-07, 16:36

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.

kevinhurt · #3 (**permalink**) 03-26-07, 14:53

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

Internet Services

Web Development

Digital Marketing

Consumer Tech