UDF means user defined function. There are 3 types of user defined SQL functions present in DB2. SQL functions are in-built to database, whereas user defined functions are like user defined ones.
- 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)