---
title: "How to Create UDF in DB2"
description: "There are 5 different functions can be created in DB2 as part of user defined functions (UDF). Out of which three functions that can be created using only SQL statements."
url: https://srinimf.com/2010/04/12/db2-udf/
date: 2010-04-12
modified: 2026-10-07
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2018/06/smilies-bank-sit-rest-160731.jpeg?fit=1880%2C1164&ssl=1
categories: ["DB2"]
tags: ["DB2 User defined function with an example", "UDF"]
type: post
lang: en
---

# How to Create UDF in DB2

The built-in functions are default to the database, whereas [user-defined functions](https://srinimf.com/2023/12/19/sql-user-defined-functions/) are created by users.

## Built-In Functions

1. Scalar functions- A *[scalar function](https://srinimf.com/2015/10/14/aggregate-functions-vs-scalar-functions-in-db2/)* 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](https://srinimf.com/2016/10/12/the-ultimate-notes-on-sql-functions-to-make-you-stronger/)* 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

### Two Types of UDFs

- **Sourced functions**– Functions that are based on existing functions.
- **External functions**–  Functions that are developed by users.

**Example:**

[Create Function](https://srinimf.com/2014/06/04/creating-udf-in-db2-zos/) 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](https://srinimf.com/2015/02/19/db2-columnar-functions-cast-decimal/), Lastname, Birthdate, Daysinmonth(Birthdate)

From DSN8810.Temp

Where Daysinmonth <31

**Related: **

- [Why SQL functions are faster than UDF](http://stackoverflow.com/questions/19884138/why-sql-functions-are-faster-than-udf) (stackoverflow.com)

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2010%2F04%2F12%2Fdb2-udf%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2018%2F06%2Fsmilies-bank-sit-rest-160731.jpeg%3Ffit%3D1200%252C743%26ssl%3D1&description=How%20to%20Create%20UDF%20in%20DB2)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2010/04/12/db2-udf/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2010/04/12/db2-udf/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2010/04/12/db2-udf/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2010/04/12/db2-udf/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Create%20UDF%20in%20DB2&body=https%3A%2F%2Fsrinimf.com%2F2010%2F04%2F12%2Fdb2-udf%2F&share=email)
-

### Like this:

Like Loading…