Creating function templates

The federated server recognizes a data source function when there is a mapping between the data source function and a counterpart function at the federated database. You can create a function template to act as the counterpart when no counterpart exists.

Before you begin

The privileges held by the authorization ID of the statement must include at least one of the following:

SYSADM or DBADM authority
IMPICIT_SCHEMA authority on the databases, if the implicit or explicit schema name of the function does not exist
CREATEIN privilege on the schema, if the schema name of the function exists

About this task

Restrictions

If the data source function has input parameters:

The database counterpart function must have the same number of input parameters that the data source function has.
The data types of the input parameters for the database counterpart function must be compatible with the corresponding data types of the input parameters for data source function. The data type cannot be LONG VARCHAR, LONG VARGRAPHIC, or a user-defined type.

If the data source function has no input parameters, the database counterpart function cannot have any input parameters.

Procedure

Use the CREATE FUNCTION statement with the AS TEMPLATE parameter.
For example:
```
CREATE FUNCTION BONUS ()   
  RETURNS DECIMAL(8,2)   
  AS TEMPLATE   
  DETERMINISTIC   
  NO EXTERNAL ACTION 
```
BONUS ()

The name you give to the function template.

RETURNS DECIMAL(8,2)

The data type of the output.

AS TEMPLATE

Indicates that this is a function template, not a function.

DETERMINISTIC

Specifies that the function always returns the same results for a given set of argument values.

NO EXTERNAL ACTION

Specifies that the function has no external impact on objects that are not managed by the database manager.

You must specify the DETERMINISTIC and NO EXTERNAL ACTION clauses according to whether the function itself is deterministic and whether it causes any external action. Otherwise, restrictions will be imposed on the SQL operations that are supported with this function template.
After you create a function template, you must then create the function mapping between the template and the data source function. A function mapping is created using the CREATE FUNCTION MAPPING statement.
For example:
```
CREATE FUNCTION MAPPING MY_INFORMIX_FUN FOR BONUS()   
  SERVER TYPE INFORMIX OPTIONS (REMOTE_NAME 'BONUS()') 
```
MY_INFORMIX_FUN

The name you give to the function mapping. The name cannot duplicate a function mapping name that is already described in the federated database global catalog. It must be unique.

FOR BONUS()

The local Db2® function template name. Include data type input parameters in parenthesis.

SERVER TYPE INFORMIX

Identifies the type of data source which contains the function that you want to map to.

OPTIONS (REMOTE_NAME 'BONUS()')

An option that identifies the name of the remote data source function that you are mapping to the local database function template.