7.15. 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
  						

  This function takes an employee number as input and returns the salary rounded to the highest integer value. SQL functions can be developed by using the Development Center, as illustrated in Figure 7.29.

  Figure 7.29. Using the Development Center tool to develop, test, and run the csmmgr.salary_round user-defined function

  [View full size image]

  

• 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
  						

  This statement registers the UDF csmmgr.db2killapp to DB2. It is an external function written in C. The C executable code is stored in the db2killapplib library, which is stored under the sqllibfunction subdirectory.