Pushdown of predicates with function templates

In a federated system, each remote data source has its own functions. Most of these functions have semantically equivalent Db2® functions and have associated function mappings by default. However, some remote source functions might not have equivalent functions on the federated server. Consequently, only the remote data source can execute these functions. To write queries that use these functions, you must create a function template on the federated server.

A function template acts as a local description of the remote function. You create a function template with the CREATE FUNCTION statement by using the AS TEMPLATE clause. There is no executable code associated with the function template at the federated server. When the template is defined, you can use it to create a function mapping, which maps the function template to its remote counterpart. Then it is possible to refer to the function template in the SQL statements that are issued to the federated server and for the function to be evaluated at the data source.

The query optimizer makes cost-based decisions to determine where a predicate can be evaluated. When possible, the optimizer generates a plan to evaluate a predicate with a function template at the corresponding remote server. In some cases, it might not be possible for the optimizer to generate a plan that evaluates the function template at the data source. When this occurs you might receive an SQL0142N error with the following error message:

The SQL statement is not supported.

To avoid this error, the query can be rewritten to enable pushdown while maintaining the semantics of the original query.

For a function template to be pushed down, it must be defined with the DETERMINISTIC and NO EXTERNAL ACTION clauses.