DB2 10.5 for Linux, UNIX, and Windows

CREATE FUNCTION MAPPING statement

The CREATE FUNCTION MAPPING statement can define a mapping between a federated database function or function template and a data source function, or disable a default mapping between a federated database function and a data source function.

When defining a mapping, the CREATE FUNCTION MAPPING statement can associate the federated database function or template with a function at the following sources:

A specified data source
A range of data sources; for example, all data sources of a particular type and version

If multiple function mappings are applicable to a function, the most recent one is applied.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include DBADM authority.

Syntax


>>-CREATE FUNCTION MAPPING--+-----------------------+--FOR------>
                            '-function-mapping-name-'        

                       .-,-------------.        
                       V               |        
>--+-function-name--(----+-----------+-+--)-+------------------->
   |                     '-data-type-'      |   
   '-SPECIFIC--specific-name----------------'   

>--+-SERVER--server-name------------------------------------------------------------------+-->
   '-SERVER TYPE--server-type--+--------------------------------------------------------+-'   
                               '-VERSION--| server-version |--+-----------------------+-'     
                                                              '-WRAPPER--wrapper-name-'       

>--+----------------------+--+------------+--------------------><
   '-| function-options |-'  '-WITH INFIX-'   

server-version

|--+-version--+------------------------+-+----------------------|
   |          '-.--release--+--------+-' |   
   |                        '-.--mod-'   |   
   '-version-string-constant-------------'   

function-options

               .-,-------------------------------------.      
               V                                       |      
|--OPTIONS -(----function-option-name--string-constant-+--)-----|

Description

function-mapping-name

Names the function mapping. The name must not identify a function mapping that is already described in the catalog (SQLSTATE 42710).

If the function-mapping-name is omitted, a system-generated unique name is assigned.

function-name

Specifies the qualified or unqualified name of the federated database function or federated database function template from which to map.

data-type

For a function or function template that has input parameters, data-type specifies the data type of each parameter. The data type cannot be an XML or a user-defined type.

Empty parentheses can be used instead of specifying length, precision, or scale for the parameterized data types. It is recommended to use empty parentheses for the parameterized data types; for example, CHAR(). A parameterized data type is any one of the data types that can be defined with a specific length, scale, or precision. The parameterized data types are the string data types and the decimal data types. If you specify length, precision, or scale, it must be the same as that of the function template. If you omit parentheses altogether, the default length for the data type is used (see the description of the CREATE TABLE statement).

SPECIFIC specific-name

Identifies the function or function template from which to map. Specify specific-name to create a convenient function name.

SERVER server-name

Names the data source containing the function that is being mapped.

SERVER TYPE server-type

Identifies the type of data source containing the function that is being mapped.

VERSION

Identifies the version of the data source denoted by server-type.

version: Specifies the version number. The value must be an integer.
release: Specifies the number of the release of the version denoted by version. The value must be an integer.
mod: Specifies the number of the modification of the release denoted by release. The value must be an integer.
version-string-constant: Specifies the complete designation of the version. The version-string-constant can be a single value (for example, '8i'); or it can be the concatenated values of version, release and, if applicable, mod (for example, '8.0.3').

WRAPPER wrapper-name

Specifies the name of the wrapper that the federated server uses to interact with data sources of the type and version denoted by server-type and server-version.

OPTIONS

Indicates what function mapping options are to be enabled.

function-option-name: Names a function mapping option that applies either to the function mapping or to the data source function included in the mapping.
string-constant: Specifies the setting for function-option-name as a character string constant.

WITH INFIX

Specifies that the data source function be generated in infix format. The federated database system converts prefix notation to the infix notation that is used by the remote data source.

Notes

A federated database function or function template can map to a data source function if:
- The federated database function or template has the same number of input parameters as the data source function.
- The data types that are defined for the federated function or template are compatible with the corresponding data types defined for the data source function.
If a distributed request references a built-in database function that maps to a data source function, the optimizer develops strategies for invoking either function when the request is processed. The built-in database function is invoked if doing so requires less overhead than invoking the data source function. Otherwise, if invoking the built-in database function requires more overhead, the data source function is invoked.
If a distributed request references a built-in database function template that maps to a data source function, only the data source function can be invoked when the request is processed. The template cannot be invoked because it has no executable code.
Default function mappings can be rendered inoperable by disabling them (they cannot be dropped). To disable a default function mapping, code the CREATE FUNCTION MAPPING statement so that it specifies the name of the built-in database function within the mapping and sets the DISABLE option to 'Y'.
Functions in the SYSIBM schema do not have a specific name. To override the default function mapping for a function in the SYSIBM schema, specify function-name using the explicit qualifier SYSIBM; for example, SYSIBM.LENGTH().
A CREATE FUNCTION MAPPING statement within a given unit of work (UOW) cannot be processed (SQLSTATE 55007) under either of the following conditions:
- The statement references a single data source, and the UOW already includes one of the following:
  - A SELECT statement that references a nickname for a table or view within this data source
  - An open cursor on a nickname for a table or view within this data source
  - Either an INSERT, DELETE, or UPDATE statement issued against a nickname for a table or view within this data source
- The statement references a category of data sources (for example, all data sources of a specific type and version), and the UOW already includes one of the following:
  - A SELECT statement that references a nickname for a table or view within one of these data sources
  - An open cursor on a nickname for a table or view within one of these data sources
  - Either an INSERT, DELETE, or UPDATE statement issued against a nickname for a table or view within one of these data sources
Create function mapping to table or row functions: A create function mapping to remote functions that returns a table or a row is not supported in a federated database.
Syntax alternatives: The following syntax is supported for compatibility with previous versions of DB2®:
- ADD can be specified before function-option-name string-constant.

Examples

Example 1: Map a function template to a UDF that all Oracle data sources can access. The template is called STATS and belongs to a schema called NOVA. The Oracle UDF is called STATISTICS and belongs to a schema called STAR.
```
   CREATE FUNCTION MAPPING MY_ORACLE_FUN1
     FOR NOVA.STATS (DOUBLE, DOUBLE)
     SERVER TYPE ORACLE
     OPTIONS (REMOTE_NAME 'STAR.STATISTICS')
```

Example 2: Map a function template called BONUS to a UDF, also called BONUS, that is used at an Oracle data source called ORACLE1.

   CREATE FUNCTION MAPPING MY_ORACLE_FUN2
     FOR BONUS()
     SERVER ORACLE1
     OPTIONS (REMOTE_NAME 'BONUS')

Example 3: Assume that there is a default function mapping between the WEEK system function that is defined to the federated database and a similar function that is defined to Oracle data sources. When a query that requests Oracle data and that references WEEK is processed, either WEEK or its Oracle counterpart will be invoked, depending on which one is estimated by the optimizer to require less overhead. The DBA wants to find out how performance would be affected if only WEEK were invoked for such queries. To ensure that WEEK is invoked each time, the DBA must disable the mapping.
```
   CREATE FUNCTION MAPPING
     FOR SYSFUN.WEEK(INT)
     SERVER TYPE ORACLE
     OPTIONS (DISABLE 'Y')
```

Example 4: Map the federated function UCASE(CHAR) to a UDF that is used at an Oracle data source called ORACLE2. Include the estimated number of instructions per invocation of the Oracle UDF.

   CREATE FUNCTION MAPPING MY_ORACLE_FUN4
     FOR SYSFUN.UCASE(CHAR)
     SERVER ORACLE2
     OPTIONS
       (REMOTE_NAME 'UPPERCASE',
       INSTS_PER_INVOC '1000')