DB2- 3 Types of User Defined Functions

DB2 User defined functions
DB2 User defined functions

UDF means user defined function. There are 3 types of user defined SQL functions present in DB2.

  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

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.

Example:

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

Once UDF is created, DBA has to give Grant 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)

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.