This section explains how you can:
At times you might want DB2 to invoke a data source function that it does not recognize. Such a function might be a user-defined function or a new built-in function that is unknown to DB2.
Before DB2 can access a data source function that it does not recognize, you must create a mapping between this function and a counterpart that is stored in the federated database. To create the mapping, select the counterpart and submit the DDL statement for creating the mapping. This statement is called CREATE FUNCTION MAPPING.
The counterpart can be an existing function or function template, or a function or function template that you create. (A function template is a partial function that has no executable code.) You can create a function or function template with the CREATE FUNCTION statement.
The data source function and its federated database counterpart should correspond in the following ways:
For documentation on the CREATE FUNCTION MAPPING and CREATE FUNCTION statements, see the SQL Reference.
The DDL for mapping a federated server function to a data source function--the CREATE FUNCTION MAPPING statement--can include estimated statistics on the overhead that would be consumed when the data source function is invoked. For example, the statement can specify the estimated number of instructions that would be required to invoke the data source function, and the estimated number of I/Os that would be expended for each byte of the argument set that is passed to this function. These estimates are stored in the global catalog; you can see them in the SYSCAT.FUNCMAPOPTIONS view. In addition, if a DB2 function (rather than a function template) participates in the mapping, the catalog contains estimates of overhead that would be consumed when this function is invoked. You can see these latter estimates in the SYSCAT.FUNCTIONS view.
After the mapping is created, you can submit distributed requests that reference the DB2 function. For example, if you mapped a DB2 user-defined function called DOLLAR to an Oracle user-defined function called US_DOLLAR, your request would specify DOLLAR rather than US_DOLLAR. When the request is processed, the optimizer evaluates multiple access strategies. Some of them reflect the estimated overhead of invoking the DB2 function; others reflect the estimated overhead of invoking the data source function. The strategy that is expected to cost the least amount of overhead is the one that is used.
If any estimates of consumed overhead change, you can record the change in the global catalog. To record new estimates for the data source function, first drop or disable the function mapping (for information about how to do this, see Discontinuing Function Mappings). Then recreate the mapping with the CREATE FUNCTION MAPPING statement, specifying the new estimates in the statement. When you run the statement, the new estimates will be added to the SYSCAT.FUNCTIONS catalog view. To record changed estimates for the DB2 function, update the SYSSTAT.FUNCTIONS catalog view directly.
You specify estimated statistics in the CREATE FUNCTION MAPPING statement
by assigning them as values to parameters called function mapping
options.
Table 29 describes these options and their values.
Table 29. Function Mapping Options and Their Settings
| Option | Valid Settings | Default Setting |
|---|---|---|
| ios_per_invoc | Estimated number of I/Os per invocation of a data source function. | '0' |
| insts_per_invoc | Estimated number of instructions processed per invocation of the data source function. | '450' |
| ios_per_argbyte | Estimated number of I/Os expended for each byte of the argument set that is passed to the data source function. | '0' |
| insts_per_argbyte | Estimated number of instructions processed for each byte of the argument set that is passed to the data source function. | '0' |
| percent_argbytes | Estimated average percent of input argument bytes that the data source function will actually read. | '100' |
| initial_ios | Estimated number of I/Os performed the first and last time that the data source function is invoked. | '0' |
| initial_insts | Estimated number of instructions processed the first and last time that the data source function is invoked. | '0' |
For more information about the DROP FUNCTION MAPPING statement, the SYSCAT.FUNCTIONS and SYSSTAT.FUNCTIONS views, and the SYSCAT.FUNCMAPOPTIONS view, see the SQL Reference.
How you code the CREATE FUNCTION MAPPING statement depends on part on whether the names of the objects that you are mapping together are the same or different. If you are creating a mapping between two functions (or a function template and a function) that have the same name, you must assign this name to the function-name parameter.
But if the names differ, then:
If you want to discontinue using a function mapping, follow these guidelines: