Creating a Table UDF or TPF

Set up an external table-valued user-defined function (Table UDF) or external table parameterized function (TPF) in C/C++, or an external table-valued user-defined function in Java (Java Table UDF) on an SAP Sybase IQ server.

Prerequisites

Database Version	Procedure Privileges
SAP Sybase IQ 15.3 and 15.4	Requires DBA authority.
SAP Sybase IQ 16.0	Table UDF or TPF to be owned by self – Requires all of: CREATE EXTERNAL REFERENCE system privilege. CREATE PROCEDURE system privilege. Table UDF or TPF to be owned by any user – Requires CREATE EXTERNAL REFERENCE system privilege. Also requires one of: CREATE ANY PROCEDURE system privilege. CREATE ANY OBJECT system privilege.

The SAP Sybase IQ resource is authenticated and running.
(Optional) If you are creating a Java procedure, install the required Java classes and JAR files in the database. (You can create the procedure if the classes and JAR files are not installed, but SAP Sybase IQ cannot execute the procedure.)
(Optional) If you are creating a C/C++ procedure, copy the library that contains the procedure to a location accessible to the SAP Sybase IQ server. (You can create the procedure if the library is not accessible to SAP Sybase IQ, but SAP Sybase IQ cannot execute the procedure.)

Task

For detailed information on UDFs, in the SAP Sybase IQ documentation, see SAP Sybase IQ User-Defined Functions.

In the Perspective Resources view, select the resource and select Resource > Administration Console.
In the left pane, select IQ Servers > Compiled Objects > Procedures.
Click the arrow next to Procedures and select New Table UDF/TPF.
The Create Table UDF / TPF Wizard appears.

On the Welcome page, specify:

Option	Description
Select a resource for which the procedure will be created	From the list, select the resource for which the procedure will be created.
Which user or group/role do you want to own the procedure?	From the list, select the user or role/group to own the procedure.
What do you want to name the procedure?	Enter a unique name for the new procedure; maximum of 128 characters.
What do you want to create?	Select the typeof table to create. Valid values are: External C/C++ table UDF External C/C++ TPF External Java table UDF procedure from the list.
What do you want to name the remote procedure?	Enter a unique name for the new remote procedure; maximum of 128 characters.
Use SQL template	Select provide comments with syntax examples to help you define parameters and results. Leave the box unchecked if you want to use the Return Structure page to define the result.

Click Next.
If you chose not to use the SQL template, define the result on the Return Structure page.
Click Next.
On the SQL page, edit the code provided by the wizard.
1. Define input parameters using the format provided.
2. Replace the placeholder external name at the end of the query with a real name that follows the format of the placeholder.
3. Make any other changes needed to complete your procedure.
(Optional) Enter a comment describing the procedure.
Comments can include both HTML and Javadoc tags, so you can incorporate them into your generated database documentation.
Click Finish.