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.

Example:

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

Related: 

Author: Srini

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