type is specified as a parameter of the type. This parameter is called the 

target type of the reference. The target of a reference is always a row in a 

typed table or a typed view. When a reference type is used, it may have a 

scope defined. 

10.5  User defined functions

DB2 UDB provides feature for creating additional functions, which can be used 

as utilities or a methods on existing data types. This provides you the capability 

to extend and customize your SQL according to your requirements. They can be 

invoked in same way as your built in DB2 UDB functions. 

Another main advantage of using UDF is it allows you to perform your logic inside 

the DB2 UDB server. By this way you can pass some of your application logic to 

server side for faster execution. Also, DB2 UDB supports implementation of 

these user defined functions in SQL or number of external programming 

languages. This provides you a feature to use the power of languages like C, 

C++, Java, OLE, etc. 

The UDF can be of the types:

Scalar function

A scalar function is a user defined function, which returns only single values 

all the time. They are quite useful while writing a utility functions based on 

existing SQL functions. A scalar function can be referenced in the same 

contexts as any built in function.

A scalar function can be sourced or external. A sourced function is defined to 

the database with a reference to another built in or user defined function, 

whereas an external function is defined to the database with a reference to an 

external library or code. Example 10 7 shows both source and external 

creation for the function 

stringId, 

which ID type to varchar(20).

Example 10 7   Scalar functions

db2>create function stringId(ID) returns varchar(20) source DIGITS(INTEGER)

db2>create function stringId(ID) returns varchar(10) external name  itso!id  

language C parameter style SQL deterministic no SQL no external action

Column function

A column function is a user defined function which single valued answers 

from a set of like values. They are quite useful when you want to aggregate a 

result from set of rows. Currently, only sourced column functions are 

supported in DB2 UDB. It means you can create a column function sourced 

upon one of the built in column functions, and you cannot write a column 

 Chapter 10. Advanced DB2 UDB features 

327