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

You must be a:

Data Virtualization Admin with authorization ID privileges.

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 you create mappings for one single function on both server type level and server level, then the mapping of server level will override the mapping of server type for that server.

Note:

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;

Pre-requisite step

Before you map a function template, determine the connection identifier (CID):

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 the CID from the web client. Go to Data > Data Virtualization > Data sources to view the CID.

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. Ensure you replace the <CID> with the CID you identified from the pre-requisite step:

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

For example, the CID is PREST10000:

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

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

FLUSH PACKAGE CACHE DYNAMIC;