CREATE FUNCTION MAPPING statement

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). This statement can only be executed while in the default SYSTEM tenant (SQLSTATE 58004).

Authorization

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

Syntax

server-version

function-options

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

Specify configuration options for the function mapping to be created. Which options you can specify depends on the data source of the object for which a function mapping is being created. For a list of data sources and the function mapping options that apply to each, see Data source options. Each option value is a character string constant that must be enclosed in single quotation marks.

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')