Using user-defined functions

 

In writing SQL applications, you can implement some actions or operations as a user-defined function (UDF) or as a subroutine in your application. Although it might appear easier to implement new operations as subroutines, you might want to consider the advantages of using a UDF instead.

For example, if the new operation is something that other users or programs can take advantage of, a UDF can help to reuse it. In addition, the function can be called directly in SQL wherever an expression can be used. The database takes care of many data type promotions of the function arguments automatically. For example, with DECIMAL to DOUBLE, the database allows your function to be applied to different, but compatible data types.

In certain cases, calling the UDF directly from the database engine instead of from your application can have a considerable performance advantage. You will notice this advantage in cases where the function may be used in the qualification of data for further processing. These cases occur when the function is used in row selection processing.

Consider a simple scenario where you want to process some data. You can meet some selection criteria which can be expressed as a function SELECTION_CRITERIA(). Your application can issue the following select statement:

     SELECT A, B, C FROM T

When it receives each row, it runs the program's SELECTION_CRITERIA function against the data to decide if it is interested in processing the data further. Here, every row of table T must be passed back to the application. But, if SELECTION_CRITERIA() is implemented as a UDF, your application can issue the following statement:

     SELECT C FROM T WHERE SELECTION_CRITERIA(A,B)=1

In this case, only the rows and one column of interest are passed across the interface between the application and the database.

Another case where a UDF can offer a performance benefit is when you deal with large objects (LOBs). Suppose that you have a function that extracts some information from a value of a LOB. You can perform this extraction right on the database server and pass only the extracted value back to the application. This is more efficient than passing the entire LOB value back to the application and then performing the extraction. The performance value of packaging this function as a UDF can be enormous, depending on the particular situation.

• UDF concepts
  A user-defined function (UDF) is a function that is defined to the DB2^® database system through the CREATE FUNCTION statement and that can be referenced in SQL statements. A UDF can be an external function or an SQL function.

• Writing UDFs as SQL functions
  An SQL function is a user-defined function (UDF) that you define, write, and register using the CREATE FUNCTION statement.

• Writing UDFs as external functions
  You can write the executable code of a user-defined function (UDF) in a language other than SQL.

• Examples: UDF code
  These examples show how to implement user-defined function (UDF) code by using SQL functions and external functions.

• Using UDFs in SQL statements
  Scalar and column user-defined functions (UDFs) can be called within an SQL statement almost everywhere an expression is valid. Table UDFs can be called in the FROM clause of a SELECT statement. Listed here are a few restrictions on the use of UDFs.

 

Parent topic:

Routines

 

Related concepts

User-defined functions