The CREATE FUNCTION MAPPING statement is used to:
- Define 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:
- A specified data source
- 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.
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 .-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
- 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.
- 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. 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 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, 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 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.
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')