DB2 User-Defined Functions

DB2 provides built-in functions that allow you to manipulate your data within an SQL statement. For example, the year function can retrieve the year of a timestamp column, as shown here.

db2 select year(current timestamp) from sysibm.sysdummy1

1
-----------
2005
In addition to built-in functions, DB2 allows you to create your own functions.
These user-defined functions (UDFs) allow you to simplify database application development by moving some of the logic to the database. A UDF takes zero to many input parameters and returns a value, a row or a table. To create a UDF, use the CREATE FUNCTION statement.

UDFs can be classified as follows.

• Sourced functions: These functions are created on top of DB2 built-in functions. Here's an example.

CREATE FUNCTION trim (p_var1 VARCHAR(50))
            RETURNS VARCHAR(50)
            RETURN RTRIM(LTRIM(p_var1))
In this example, RTRIM is a DB2 built-in function that removes all the blanks at the end of a string. LTRIM is a DB2 built-in function that removes all the blanks at the beginning of a string. The UDF trim is created to remove blanks at the beginning and the end of a string by using these two built-in functions. To test the function, you can use the VALUES statement as follows:
  VALUES (trim('       hello ')
 
which returns:

1
--------------------------------------------------
hello

• SQL functions: These functions are written in SQL PL language. They can return a scalar value, a single row, or a table of data. The following code shows an example of an SQL UDF returning a scalar value: the rounded salary of an employee.

  CREATE FUNCTION csmmgr.salary_round(p_empno CHAR(6))
              RETURNS INTEGER
              LANGUAGE SQL
  F1: BEGIN ATOMIC
             DECLARE v_salary INTEGER;
             SET v_salary = (SELECT ceiling(salary) FROM employee
                                  WHERE empno = p_empno);
              RETURN v_salary;
 END

• External functions: These functions are defined in the database with references to object code libraries that are written in C, Java, or OLE. Consider this example.

CREATE FUNCTION csmmgr.db2killapp(INT)
            RETURNS INT
            EXTERNAL NAME 'db2killapplib!db2killapp'
            LANGUAGE C
            PARAMETER STYLE SQL
            NOT FENCED
            RETURNS NULL ON NULL INPUT
            NOT DETERMINISTIC
            NO SQL
            NO EXTERNAL ACTION

Created with Artisteer