The built-in functions are default to the database, whereas user-defined functions are created by users.
- 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
Two Types of UDFs
- 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)