DB2: 3 Top User-defined functions

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

DB2: How to Create, Delete User-defined Function

DB2: User-defined Function Syntax to Create, Drop

In this post you can find how to Create and Delete DB2 user-defined functions.

3 Kinds of Functions an User Create UDF

  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.



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


Author: Srini

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