User-Defined Function's DB2 SQL

Hi, welcome to a new article. In this article we will learn about the user-defined functions. So let's get started.

What are user-defined functions (UDFs)?

UDFs are custom functions that users can create in SQL and can be used as we use other SQL functions.

Types of UDFs

There are two ways of creating a UDF. We can create a program in any traditional programming language or use an existing template.

There are two types of UDF that can be written:

1. Scaler Function: A scaler function is applied to a column of row and operates on a single value. For example, upper(). DB2 provides us with a vast list of built-in scalar functions, but we can create our own.

2. Table Function: These are the functions that, when invoked, return an entire table. A table function is specified in the FROM clause of a SELECT statement.

Scalar UDF

The scaler function is used per row and returns value as defined in output.

--------------------- SYNTAX -----------------------------------
CREATE OR REPLACE FUNCTION function_name (param1 datatype, ...)
RETURNS return_datatype
LANGUAGE SQL
BEGIN
  -- your logic here
  RETURN expression;
END

------------------ Example - FULL NAME FORMAT --------------------
CREATE OR REPLACE FUNCTION FORMAT_NAME(
    P_FIRST  VARCHAR(50),
    P_LAST   VARCHAR(50)
)
RETURNS VARCHAR(101)
LANGUAGE SQL
DETERMINISTIC
BEGIN
    RETURN TRIM(P_LAST) || ' ' || TRIM(P_FIRST);
END

--------------------- USAGE ----------------------------------------
SELECT FORMAT_NAME(FIRST_NAME, LAST_NAME) AS DISPLAY_NAME
FROM   EMPLOYEES;

Table UDF

A table function returns a set of rows and columns. This means it returns an entire table. You call it in the FROM clause, wrapped in the TABLE() keyword.

------------------------ SYNTAX TEMPLATE -----------------------------
CREATE OR REPLACE FUNCTION function_name(param1 datatype)
RETURNS TABLE (
    col1  datatype,
    col2  datatype
)
LANGUAGE SQL
RETURN
    SELECT col1, col2
    FROM   some_table
    WHERE  condition = param1;

--- NOTE : For a single RETURN statement (no BEGIN...END), no semicolon is needed after the SELECT. ----------------------------

--------------------- EXAMPLE ----------------------------------
CREATE OR REPLACE FUNCTION GET_DEPT_EMPS(P_DEPT VARCHAR(10)) 
RETURNS TABLE ( 
    EMP_ID INTEGER, 
    EMP_NAME VARCHAR(100), 
    SALARY DECIMAL(10,2), 
    JOB_TITLE VARCHAR(50) 
) 
LANGUAGE SQL 
RETURN 
    SELECT E.EMP_ID, E.EMP_NAME, E.SALARY, E.JOB_TITLE 
    FROM EMPLOYEES E 
    WHERE E.DEPT_CODE = P_DEPT 
    AND E.STATUS = 'ACTIVE' 
    ORDER BY E.SALARY DESC;

----------------------- SIMPLE USAGE --------------------------------------
SELECT * FROM TABLE(GET_DEPT_EMPS('HR')) AS T;

Logic & Variables

DB2 UDFs support full procedural SQL, which means you can have variables, loops, and conditions in your function.

CREATE OR REPLACE FUNCTION CALC_TAX(P_INCOME DECIMAL(12,2)) 
RETURNS DECIMAL(12,2) 
LANGUAGE SQL 
BEGIN 
    DECLARE V_TAX DECIMAL(12,2) DEFAULT 0; 
    DECLARE V_RATE DECIMAL(5,4) DEFAULT 0;

    SET V_RATE = CASE
        WHEN P_INCOME <  250000  THEN 0.05
        WHEN P_INCOME <  500000  THEN 0.10
        WHEN P_INCOME < 1000000  THEN 0.15
        ELSE                          0.30
    END;

    SET V_TAX = P_INCOME * V_RATE;
    RETURN V_TAX;
END

Options & Clauses

CREATE OR REPLACE FUNCTION CALC_BONUS(
    P_SALARY DECIMAL(10,2),
    P_RATING INTEGER
)
RETURNS DECIMAL(10,2)
LANGUAGE SQL
DETERMINISTIC            -- same salary + rating = same bonus always
NO EXTERNAL ACTION       -- safe for optimiser
RETURNS NULL ON NULL INPUT -- skip if salary or rating is NULL
BEGIN
    RETURN P_SALARY * CASE P_RATING
        WHEN 5 THEN 0.20
        WHEN 4 THEN 0.15
        WHEN 3 THEN 0.10
        ELSE       0.05
    END;
END

Writing UDF as external function

You can write the executable code of a user-defined function (UDF) in a language other than SQL. It provides the flexibility for you to use whatever language is most effective for you. The executable code can be contained in either a program or service program.

There are 4 stages of writing external UDFs.

1. Write the module having export procedures in RPGLE. You can use another language as well, but for this I am using RPGLE.

2. If you have written a module having export procedures, attach your module with a service program.

3. Register the program/module you have written with CREATE FUNCTION. This tells DB2 the function name, parameter types, return type, the library name, service program name, the procedure name, and the language. This stores the metadata in DB2's catalog.

4. Call it like you call any other function.

Scenario:

We will simply create a module with an export procedure attached to a service program wrapped inside a create function statement.

Module having export procedure

**FREE

// ============================================================
// Module   : DATEUTL
// Purpose  : Convert a date string to ISO format YYYY-MM-DD
// Exported : CVTDATEISO
// ============================================================

ctl-opt nomain                    // module — no program entry point
        option(*srcstmt *nodebugio)
        datfmt(*iso)               // default date format in this module
        decedit(*jobrun);

// --------------------------------------------------------
// Procedure definition
// --------------------------------------------------------
dcl-proc CVTDATEISO export;       // EXPORT makes it visible to *SRVPGM

  dcl-pi *n varchar(10);           // procedure interface — return type
    pInDate  varchar(10) const;
    pFmt     char(3)     const;
  end-pi;

  // Local variables
  dcl-s wDate    date(*iso);        // working date in ISO internally
  dcl-s wDD      char(2);
  dcl-s wMM      char(2);
  dcl-s wYYYY    char(4);
  dcl-s wResult  varchar(10);
  dcl-s wFmt     char(3);

  // Normalise format to uppercase
  wFmt = %upper(pFmt);

  // Guard: return empty string on blank input
  if %trimr(pInDate) = '';
    return '';
  endif;

  // Parse based on format hint
  select;
    when wFmt = 'DMY';              // DD/MM/YYYY  e.g. 25/03/2024
      wDD   = %subst(pInDate:1:2);
      wMM   = %subst(pInDate:4:2);
      wYYYY = %subst(pInDate:7:4);

    when wFmt = 'MDY';              // MM/DD/YYYY  e.g. 03/25/2024
      wMM   = %subst(pInDate:1:2);
      wDD   = %subst(pInDate:4:2);
      wYYYY = %subst(pInDate:7:4);

    when wFmt = 'YMD';              // YYYYMMDD   e.g. 20240325
      wYYYY = %subst(pInDate:1:4);
      wMM   = %subst(pInDate:5:2);
      wDD   = %subst(pInDate:7:2);

    when wFmt = 'ISO';              // already ISO YYYY-MM-DD
      return %subst(pInDate:1:10);

    other;                          // unknown format — signal error
      return 'INVALID-FMT';
  endsl;

  // Build ISO string YYYY-MM-DD
  wResult = wYYYY + '-' + wMM + '-' + wDD;

  // Validate by casting to DATE — catches impossible dates
  // If invalid, the monitor catches the error and returns 'BAD-DATE'
  monitor;
    wDate = %date(wResult : *iso);
    return %char(wDate : *iso);    // YYYY-MM-DD guaranteed
  on-error;
    return 'BAD-DATE';              // invalid calendar date
  endmon;

end-proc;

Compile the above module and attach it to a service program.

Create a create function statement.

-------------------- Registration ---------------------
CREATE OR REPLACE FUNCTION MYLIB.DATE_TO_ISO (
    P_DATE   VARCHAR(10),    -- input date string e.g. '25/03/2024'
    P_FORMAT CHAR(3)         -- format: 'DMY' | 'MDY' | 'YMD' | 'ISO'
)
RETURNS VARCHAR(10)          -- returns 'YYYY-MM-DD' or 'BAD-DATE'

EXTERNAL NAME 'MYLIB/DATEUTLSRV(CVTDATEISO)'
                             -- format: 'LIBRARY/SRVPGM(PROCEDURE)'
LANGUAGE RPGLE               -- tells DB2 this is an ILE program object

PARAMETER STYLE DB2SQL       -- DB2 passes null indicators alongside params

DETERMINISTIC                -- same input always gives same output

NO SQL                       -- our RPGLE does not run any SQL

NO EXTERNAL ACTION           -- no side effects outside the function

RETURNS NULL ON NULL INPUT;  -- skip call entirely if any param is NULL

----------------- USAGE -----------------
SELECT MYLIB.DATE_TO_ISO('25/03/2024', 'DMY') AS ISO_DATE
FROM   SYSIBM.SYSDUMMY1;

Note: All the code that we have written is specifically for DB2. For other SQL engines, errors may occur, so change accordingly.

That's it for this article. Hope you all liked the article.

Thank you!