Create and use SQL user-defined functions in DB2 (1)

This article will introduce you in detail how to create custom rows in the DB2 database to extend the built-in DB2 functions for your reference.

You can create user-defined functions to extend the built-in DB2 functions. For example, you can create a function that computes complex arithmetic expressions or string operations, and then reference these functions in an SQL statement like any existing built-in functions.

Suppose we need a function to return the area of the circle. The input parameter of this function is the radius of the circle. The built-in DB2 functions do not have such a function, but you can create a user-defined SQL scalar function to execute this task. You can reference this function wherever a scalar function is supported in SQL statements. CREATE function ca (r DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN 3.14159 * (r * r );

The no external action clause indicates that this function does not affect the status of objects not managed by the database administrator. The DETERMINISTIC keyword indicates that this function always returns the same result for a given parameter value. This information is used during query optimization. The easy way to execute this function is to reference it in a query. In the following example, there is only one row in the SYSIBM. SYSDUMMY1 cataloguing view) You can SELECT any query target to execute this query): db2 SELECT ca (96.8) AS area FROM sysibm. sysdummy1
AREA
------------------------
+ 2.94374522816000E + 004
1 record (s) selected.

You can also create a user-defined table function that accepts zero or more input parameters and returns data in the form of a table. Table functions can only be used in the FROM clause of SQL statements.