Pushing a Custom Function to the Native Data Source

You can force a custom function to be executed by the native data source rather than by the TDV query engine. For a description of the types of SQL scripts that can be made into custom functions, see Promoting Procedures to Custom Functions.

To promote a SQL script to a custom function that is always pushed to a native data source, you use the Native Only check box, as described in the procedure below.

Note: The TDV query engine might push a custom function to the data source even without your checking the Native Only check box. The check box is available to guarantee that the custom function is pushed.

To push a custom function to the native data source

1. Log in as the administrator, or have the administrator perform these steps for you.

2. Locate and open a SQL script with one scalar output and check the Native Only check box.

3. In Studio, use the Administration > Custom Functions menu.

If the number of candidate SQL scripts is large, it might take Studio a while to prepare the list.

4. Select the procedure to promote to a custom function.

6. Determine which adapter file you want to modify using the following guidelines and tips:

— Changes made under /conf/adapters remain after applying hotfixes, and are included in any CAR file.
This is true for /conf/adapters/custom and /conf/adapters/system.

— Changes made to system adapters under /conf/adapters/system might require slightly more editing. For example, for Oracle you would need to define a <common:attribute> </common:attribute> for 11g.

Note: If you want a custom function to be pushed to all versions of a data source, add the name and signature of that custom function to the “generic” capabilities file for that data source.

— You can choose to create a custom adapter to manage your customizations, but if you are adding missing capabilities it might not be recommended.

For example, to add a custom function, ss10, intended for a custom Netezza data source, open the file:

<TDV_install_dir>\conf\adapters\system\<netezza_ver>\<netezza>.xml

8. From a text editor, open and add XML with the name, type, value, and configID of the custom function. For example, to add custom function ss10, add XML similar to this:

<common:attribute xmlns:common="http://www.compositesw.com/services/system/util/common">

<common:name>/custom/ss10(~string,~string)</common:name>

<common:configID>ss10(~string,~string)</common:configID>

The custom function, when invoked, will be pushed to the native data source for execution. For example, when ss10 is invoked in a SQL statement that includes, a Netezza table, the function will be pushed to the Netezza data source for execution.