SQL Reference

CREATE FUNCTION MAPPING

The CREATE FUNCTION MAPPING statement is used to:

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

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 ]