How to Create UDF in DB2

The built-in functions are default to the database, whereas user-defined functions are created by users.

Built-In Functions

1. Scalar functions- A scalar function returns a single value each time it is invoked and is generally valid wherever an SQL expression is valid.
2. Table functions- A table function can be used in a FROM clause and returns a table
3. Row function- A row function can be used as a transform function and returns a row

Two Types of UDFs

• Sourced functions– Functions that are based on existing functions.
• External functions–  Functions that are developed by users.

Example:

Create Function DAYSINMONTH(DATE)
RETURNS Integer
EXTERNAL NAME  'DAYMYTH'
LANGUAGE COBOL;

Once UDF is created, DBA has to give Grant access to use this function in COBOL program. In the COBOL program the code should be as follows:

Select Empno, Lastname, Birthdate, Daysinmonth(Birthdate)
From DSN8810.Temp
Where Daysinmonth <31

Related: 

• Why SQL functions are faster than UDF (stackoverflow.com)