SQL Reference

CREATE FUNCTION MAPPING

The CREATE FUNCTION MAPPING statement is used to:

Create a mapping between a federated database function or function template and a data source function. The mapping can associate the federated database function or template with a function at either (1) a specified data source or (2) a range of data sources; for example, all data sources of a particular type and version.
Disable a default mapping between a federated database function and a data source function.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The authorization ID of the statement must have SYSADM or 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  .-ADD--.                                           |
|---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

Is the qualified or unqualified name of the function or function template to map from.

data-type

For a function or function template that has any input parameters, data-type specifies the data type of such a parameter. The data type cannot be LONG VARCHAR, LONG VARGRAPHIC, DATALINK, a large object (LOB) type, or a user-defined type.

SPECIFIC specific-name

Identifies the function or function template to map from. Specify specific-name if the function or function template does not have a unique function-name in the federated database.

SERVER server-name

Names the data source that contains the function that is being mapped to.

TYPE server-type

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

VERSION

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

version: Specifies the version number. version must be an integer.
release: Specifies the number of the release of the version denoted by version. release must be an integer.
mod: Specifies the number of the modification of the release denoted by release. mod 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. Refer to Function Mapping Options for descriptions of function-option-names and their settings.

ADD: Enables one or more function mapping options.
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.

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 that are defined for the data source function.
If a distributed request references a DB2 function that maps to a data source function, the optimizer develops strategies for invoking either function when the request is processed. The DB2 function is invoked if doing so requires less overhead than invoking the data source function. Otherwise, if invoking the DB2 function requires more overhead, then the data source function is invoked.
If a distributed request references a DB2 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 DB2 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 with qualifier SYSIBM and function name (such as LENGTH).
A CREATE FUNCTION MAPPING statement within a given unit of work (UOW) cannot be processed under either of the following conditions:
- The statement references a single data source, and the UOW already includes a SELECT statement that references 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 a SELECT statement that references a nickname for a table or view within one of these data sources.

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)
      TYPE ORACLE
      OPTIONS ( DISABLE 'Y' )

Example 4: Map the local function UCASE(CHAR) to a UDF that's 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' )

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]