Installing the UDM | C/C++ UDMs | Teradata Vantage - 17.10 - Installing the UDM - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

After you write and test a UDM, you can install it on the server.

In general, you should not create UDFs, UDMs, or external stored procedures in Teradata system databases such as SYSLIB or SYSUDTLIB. These databases are primarily used for Teradata system UDFs, UDTs, UDMs, and external stored procedures only, and they usually contain a large number of these system external routines. Every time you create, alter, or drop your external routine in these databases, Teradata must relink your routine to all the objects of the system external routines. In addition, to execute your routine, Teradata must load all the shared libraries referenced by the system external routines, and these libraries may not be related to your routine. This is very inefficient. However, note that there are cases where you have to create your UDF in a system database. For example, UDFs used for row level security must reside in the SYSLIB database.

CREATE METHOD Statement

To identify the file name and location of the source code and install it on the server, use the CREATE METHOD statement.

The method is compiled, linked to the dynamic linked library (DLL or SO) associated with the database in which the method resides, and distributed to all dabase nodes in the system.

Note that all UDMs that are defined in a specific database are linked into a single dynamically linked library.

Default and Temporary Paths

The default and temporary paths that Vantage uses to manage UDMs during creation and execution are the same default and temporary paths that are used for UDFs and external stored procedures. For example, Vantage uses the same temporary directory to compile UDFs, UDMs, and external stored procedures.

For more information on default and temporary paths, see Default and Temporary Paths.

Specifying Source File Locations

The EXTERNAL clause of the CREATE METHOD statement specifies the name and path of the source code file.

CREATE METHOD Clause Description
EXTERNAL Use the EXTERNAL clause when the method source is in the current or default directory on the client, and no other files need to be included.

The source name is the name that immediately follows the CREATE METHOD keywords.

If the client is...
  • workstation-attached, then BTEQ adds appropriate file extensions to the source name to locate the source file.
  • mainframe-attached, then the source name must be a DDNAME file name.

Here is an example for a source code file named toInches.c:

CREATE METHOD toInches()
RETURNS FLOAT
FOR meter
EXTERNAL;
EXTERNAL NAME method_name Use the EXTERNAL method_name clause when the source is in the current or default directory on the client, and no other files need to be included.

The source name is the same as method_name.

If the client is...
  • workstation-attached, then BTEQ adds appropriate file extensions to method_name to locate the source file.
  • mainframe-attached, then method_name must be a DDNAME file name.

Here is an example for a source code file named meter_toInches.c:

CREATE METHOD toInches()
RETURNS FLOAT
FOR meter
EXTERNAL NAME meter_toInches;
EXTERNAL NAME 'string' Use 'string' to specify names and locations of:
  • Function source, include header files, object files, libraries, and packages on the server.
  • Function source, include header files, and object files on the client.
If the client is...
  • workstation-attached, then if necessary, BTEQ adds appropriate file extensions to the names to locate the files.
  • mainframe-attached, then the names must be DDNAME file names.

Here is an example:

CREATE METHOD toInches()
RETURNS FLOAT
FOR meter
EXTERNAL NAME 'CS!toInches!udmsrc/toInches.c!F!meter_toInches';
where:
  • ! in 'string' specifies a delimiter.
  • C in 'string' specifies that the source is obtained from the client.
  • S in 'string' specifies that the information between the following two sets of delimiters identifies the name and location of a C or C++ function source file.
  • toInches in 'string' specifies the name, without the file extension, that the server uses to compile the source.
  • udmsrc/toInches.c in 'string' specifies a relative path (udmsrc) for the source file (toInches.c).
  • F in 'string' specifies that the information after the next delimiter identifies the C or C++ function name.
  • meter_toInches in 'string' specifies the C or C++ function name.

For more information on CREATE METHOD and the EXTERNAL clause, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.

Source File Locations for ODBC

If you use ODBC, you can only create UDMs from files that are stored on the server.

Source File Locations for JDBC

Using Teradata Driver for the JDBC Interface, you can create UDMs from files that are located on the Teradata server, or from resources located on the client.

A client-side UDM source file must be available as a resource in the class path. The Teradata JDBC driver can load the resource from the class path and transfer it to the server node without directly accessing the client file system.

Specifying the C/C++ Function Name

In addition to specifying the location of the source code, the EXTERNAL clause in the CREATE METHOD statement identifies the C/C++ function name that appears in the C/C++ function declaration or the function entry name when the C/C++ object is provided instead of the C/C++ source.

IF CREATE METHOD specifies this clause … THEN …
EXTERNAL The C/C++ function name must match the name that follows the CREATE METHOD keywords.

Consider the following CREATE METHOD statement:

CREATE METHOD toInches()
RETURNS FLOAT
FOR meter
EXTERNAL;

The C or C++ function name must be toInches.

If the client is mainframe-attached, then the C/C++ function name must be the DDNAME for the source.

EXTERNAL NAME method_name the C/C++ function name must match method_name.

Consider the following CREATE METHOD statement:

CREATE METHOD toInches()
RETURNS FLOAT
FOR meter
EXTERNAL NAME meter_toInches;

The C/C++ function name must be meter_toInches.

If the client is mainframe-attached, then method_name must be the DDNAME for the source.

EXTERNAL NAME 'string' 'string' can include the F option to specify the C/C++ function name.

Consider the following CREATE METHOD statement:

CREATE METHOD toInches()
RETURNS FLOAT
FOR meter
EXTERNAL NAME 'CS!toInches!udmsrc/toInches.c!F!meter_toInches';

The C/C++ function name must be meter_toInches.

If 'string' does not include the F option, then the C/C++ function name must match the name that follows the CREATE METHOD keywords.

Specifying Nonstandard Include Files

If a UDM includes a nonstandard header file, the EXTERNAL clause in the CREATE METHOD statement must specify the name and path of the header file.

Consider the following UDM that includes the header file ssn.h:

/*****  C source file name: p_ssn.c  *****/

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include "ssn.h"

void encrypt( UDT_HANDLE    *personalUdt,
              VARCHAR_LATIN *result,
              char           sqlstate[6])
{
     ...
}

Here is an example of CREATE METHOD that specifies the name and path of the nonstandard header file:

CREATE METHOD encrypt()
RETURNS VARCHAR(64)
FOR personal
EXTERNAL NAME
'CI!ssn!udm_home/ssn.h!CS!encrypt!udm_home/p_ssn.c!F!encrypt';

where:

This part of the string that follows EXTERNAL NAME … Specifies …
! a delimiter.
C that the header file is obtained from the client.
I that the information between the following two sets of delimiters identifies the name and location of an include file (.h).
ssn the name, without the file extension, of the header file.
udm_home/ssn.h the path and name of the header file on the client.
C that the source is obtained from the client.
S that the information between the following two sets of delimiters identifies the name and location of a C or C++ function source file.
encrypt the name, without the file extension, that the server uses to compile the source.
udm_home/p_ssn.c the path and name of the source file.
F that the information after the next delimiter identifies the C or C++ function name.
encrypt the C or C++ function name.

For more information on installing libraries, see the information about CREATE METHOD in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Related Information

FOR more information on … SEE …
the CREATE METHOD statement Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
the privileges that apply to UDTs and UDMs Teradata Vantage™ - Database Administration, B035-1093.
Linux limitations that affect name and number of external routines in a database Argument list too long.