Creating function mappings for all data source objects on a specific SSPD server in Data Virtualization

You can create a mapping to a function that is used by all data sources objects on a specific remote server.

Before you begin

The privileges that are held by the authorization ID of the statement must have SYSADM or DBADM authority.

About this task

When you create a function mapping for a specific server, the mapping can be used for that server type only.

You cannot override the existing function mappings or create new mappings for nonrelational data sources.

If customer creates mappings for one single function on both server type level and server level, the mapping of server level will override the mapping of server type for that server.

For example:

CREATE FUNCTION MAPPING QPLEX_UPPER_1 FOR SYSIBM.UPPER(CHAR)SERVER TYPE QUERYPLEX_PREST OPTIONS (REMOTE_NAME 'UPPER(:1P)');

CREATE FUNCTION MAPPING QPLEX_UPPER_2 FOR SYSIBM.UPPER(CHAR) SERVER QPLEX OPTIONS (REMOTE_NAME 'UPPER2(:1P)', CID 'PREST10000');

QPLEX_UPPER_2 will be used for PREST10000.

FLUSH PACKAGE CACHE DYNAMIC;

Procedure

To map a function template that is called QPLEX_UPPER_2 to a user-defined function, use the CREATE FUNCTION MAPPING statement, as shown in the following example:

CREATE FUNCTION MAPPING QPLEX_UPPER_2 FOR SYSIBM.UPPER(CHAR) 
     SERVER QPLEX 
     OPTIONS (REMOTE_NAME 'UPPER(:1P)', CID 'PREST10000');

CREATE FUNCTION MAPPING FOR SYSIBM.UPPER(CHAR) SERVER QPLEX OPTIONS (REMOTE_NAME 'UPPER(:1P)', CID 'PREST10000');

The option CID is the unique identifier of the data source connection. Run the following command to get the CID from the dvsys.rdb_connections table:

select cid, url from dvsys.rdb_connections;

You can also find this identifier in the UI. Go to Data > Data Virtualization and expand the connection on the Data sources page to see the CID.

The update of creating or dropping the CID function mapping goes into effect immediately without any other action. The update goes into effect for the next query that has no planned cached. To make sure that the update is in effect, please run the following command:

FLUSH PACKAGE CACHE DYNAMIC;