Administration Guide

Creating a User-Defined Function (UDF) or Method

User-defined functions (UDFs) extend and add to the support provided by built-in functions of SQL, and can be used wherever a built-in function can be used. You can create UDFs as either:

An external function, which is written in a programming language
A sourced function, whose implementation is inherited from some other existing function

There are three types of UDFs:

Scalar

Returns a single-valued answer each time it is called. For example, the built-in function SUBSTR() is a scalar function. Scalar UDFs can be either external or sourced.

Column

Returns a single-valued answer from a set of like values (a column). It is also sometimes called an aggregating function in DB2. An example of a column function is the built-in function AVG(). An external column UDF cannot be defined to DB2, but a column UDF which is sourced upon one of the built-in column functions can be defined. This is useful for distinct types.

For example, if there is a distinct type SHOESIZE defined with base type INTEGER, a UDF AVG(SHOESIZE) which is sourced on the built-in function AVG(INTEGER) could be defined, and it would be a column function.

Table

Returns a table to the SQL statement which references it. Table functions may only be referenced in the FROM clause of a SELECT statement. Such a function can be used to apply SQL language processing power to data which is not DB2 data, or to convert such data into a DB2 table.

For example, table functions can take a file and convert it to a table, tabularize sample data from the World Wide Web, or access a Lotus Notes database and return information such as the date, sender, and text of mail messages. This information can be joined with other tables in the database.

A table function can only be an external function. It cannot be a sourced function.

Information about existing UDFs is recorded in the SYSCAT.FUNCTIONS and SYSCAT.FUNCPARMS catalog views. The system catalog does not contain the executable code for the UDF. (Therefore, when creating your backup and recovery plans you should consider how you will manage your UDF executables.)

Statistics about the performance of UDFs are important when compiling SQL statements. See Updating Statistics for User-Defined Functions for information about how to update UDF statistics in the system catalog.

For details on using the CREATE FUNCTION statement to write a UDF to suit your specific application, refer to the Application Development Guide. Refer to the SQL Reference for details on UDF syntax.

Creating a Function Mapping

In a federated database, create a function mapping when you need to map a local function or a local function template (described in Creating a Function Template) with a function at one or more data sources. Default function mappings are provided for many data source functions.

Function mappings are useful when:

New, built-in functions become available at a data source.
You need to map a user-defined function at a data source to a local function.
An application requires different default behavior than that provided by the default mapping.

Function mappings defined with CREATE FUNCTION MAPPING statements are stored in the federated database.

Functions (or function templates) must have the same number of input parameters as the data source function. Additionally, the data types of the input parameters on the federated side should be compatible with the data types of the input parameters on the data source side.

Use the CREATE FUNCTION MAPPING statement to create a function mapping. For example, to create a function mapping between an Oracle AVGNEW function and a DB2 equivalent at server ORACLE1:

   CREATE FUNCTION MAPPING ORAVGNEW FOR SYSIBM.AVG(INT) SERVER ORACLE1 
   OPTIONS (REMOTE_NAME 'AVGNEW')

You must hold one of the SYSADM or DBADM authorities at the federated database to use this statement. Function mapping attributes are stored in SYSCAT.FUNCMAPPINGS.

The federated server will not bind input host variables or retrieve results of LOB, LONG VARCHAR/VARGRAPHIC, DATALINK, distinct and structured types. No function mapping can be created when an input parameter or the returned value includes one of these types.

For additional details on using and creating function mappings, refer to the Application Development Guide. Refer to the SQL Reference for details on CREATE FUNCTION MAPPING syntax.

Creating a Function Template

In a federated system, function templates provide "anchors" for function mappings. They are used to enable the mapping of a data source function when a corresponding DB2 function does not exist at the federated server. A function mapping requires the presence of a function template or an existing similar function at DB2.

The template is just a function shell: name, input parameters, and the return value. There is no local executable for the function.

Because there is no local executable for the function, it is possible that a call to the function template will fail even though the function is available at the data source. For example, consider the query:

   SELECT myfunc(C1) 
      FROM nick1
      WHERE C2 < 'A'

If DB2 and the data source containing the object referenced by nick1 do not have the same collating sequence, the query will fail because the comparison must be done at DB2 while the function is at the data source. If the collating sequences were the same, the comparison operation could be done at the data source that has the underlying function referenced by myfunc.

Functions (or function templates) must have the same number of input parameters as the data source function. The data types of the input parameters on the federated side should be compatible with the data types of the input parameters on the data source side. These requirements apply to returned values as well.

You create function templates using the CREATE FUNCTION statement with the AS TEMPLATE keyword. After the template is created, you map the template to the data source using the CREATE FUNCTION MAPPING statement.

For example, to create a function template and a function mapping for function MYS1FUNC on server S1:

   CREATE FUNCTION MYFUNC(INT) RETURNS INT AS TEMPLATE
 
   CREATE FUNCTION MAPPING S1_MYFUNC FOR MYFUNC(INT) SERVER S1 OPTIONS
   (REMOTE_NAME 'MYS1FUNC')

For details on using and creating function templates, refer to the Application Development Guide. Refer to the SQL Reference for details on CREATE FUNCTION syntax.

[ Top of Page | Previous Page | Next Page ]