UDF means user defined function. In DB2 you can fine three built-in SQL functions. The built-in functions are default to the database, whereas user defined functions are created by users.
Stored Procedures vs Functions
In this post you can find how to Create and Delete DB2 user-defined functions.
3 Kinds of Functions an User Create UDF
- Scalar functions- A scalar function returns a single value each time it is invoked, and is generally valid wherever an SQL expression is valid.
- Table functions- A table function can be used in a FROM clause and returns a table
- Row function- A row function can be used as a transform function and returns a row
The other functions that can be created in DB2 using create function statement are :
- Sourced functions– Functions that are based on existing functions.
- External functions– Functions that are developed by users.
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
- Why SQL functions are faster than UDF (stackoverflow.com)