UDF Parameter Styles
The following three parameter styles are all valid for external functions.
| Style | Description |
|---|---|
| SQL | Uses indicator variables to pass arguments. This is the default parameter style for all UDFs. Therefore, you can pass nulls as inputs and return them in results. If a row-level security policy constraint permits nulls, then its parameter style must be SQL. If the function runs a row-level security policy constraint, each input and output parameter for the function has a second indicator parameter. The following indicator parameter values apply.
No other values are valid for an indicator parameter in a row-level security policy constraint UDF. |
| TD_GENERAL | Uses parameters to pass arguments. Can neither be passed nulls nor return nulls. If a row-level security policy constraint does not permit nulls, then its parameter style must be TD_GENERAL. If the function runs a row-level security policy constraint, there are no indicator parameters. |
| JAVA | If the Java function must accept null arguments, then the EXTERNAL NAME clause must include the list of parameters and specify data types that map to Java objects. PARAMETER STYLE JAVA must be specified for all Java functions. A row-level security policy constraint function cannot have a parameter style of JAVA. |
Downloadable UDF Samples
For downloadable samples of Teradata application software, see:
Relationship of UDFs, Table UDFs, Methods, and External Procedures
UDFs, table UDFs, methods, and external procedures are specific variations of each other and share most properties in common. The generic term used to describe all these is external routine.
Protected and Unprotected Execution Modes in CREATE FUNCTION and REPLACE FUNCTION (External Form)
By default, all UDFs are created to run in protected mode. Protected and secure modes are states in which each instance of a UDF runs in a separate process. The difference between a protected mode server and a secure mode server is that a protected mode server process runs under the predefined OS user tdatuser, while a secure server process runs under the OS user specified by the UDF in its EXTERNAL SECURITY clause. The two processes are otherwise identical.
This is done to protect the system from many common programming errors such as non-valid pointers, corrupted stacks, and illegal computations such as divide-by-zero errors that otherwise stop the database, produce problems with memory leakage, or cause other potentially damaging results.
These problems all cause the database to stop if they occur in unprotected mode. UDFs can also cause the database to crash in protected mode if they corrupt the shared data areas between the database and the protected mode UDF.
- Testing all UDFs that are in development.
- Running Java UDFs.
Java UDFs must be run in protected mode at all times. Therefore, they are slower than the equivalent C or C++ functions.
- Running any UDFs that cause the OS to consume system resources. This includes anything that causes the OS to allocate system context, including open files, pipes, semaphores, tokens, threads (processes), and so on.
Do not use protected mode for production-ready C or C++ functions that do not make OS system calls. Protected mode puts additional processing burdens on the system that often degrade performance, while UDFs in unprotected mode run in the context of the AMP worker task that is already being used by that query step. If you run C or C++ UDFs in protected mode, they run more slowly.
The best practice is to develop and test your UDFs on a non-production test system. Run newly created UDFs several hundred times to make sure it does not crash the system and to find any performance issues that you can avoid with better function design and coding techniques.
Table cardinality is an important factor in UDF performance, so tests run against smaller test tables on a nonproduction system may not scale to the same level of performance when run against significantly larger tables in production databases.
Protected mode servers consume disk resources for C and C++ functions as follows:
In unprotected mode, a UDF is called directly by the database rather than running as a separate process. Only alter a new C or C++ function that does not require the OS to allocate system context to run in unprotected mode after you have thoroughly tested and evaluated its robustness and performance impact. When the newly created CPU-operations-only C or C++ UDF has passed your quality measures and is ready to be put into production use, alter it to run in unprotected mode.
Each Java server for UDFs requires roughly 30 MB of memory for swap space, and there can be two such Java servers per node. A Java UDF multithreaded server for non-secure mode Java UDFs uses a minimum of an additional 30 MB (the amount required can be larger, depending on the size of the JARs for a user), so each node requires approximately 100 MB of swap space if all server flavors are used.
UDF Handling of SQL Result Codes
Because UDFs are user-written applications, they must generate all success, warning, and exception messages.
The ANSI SQL:2011 standard defines a return code variable named SQLSTATE to accept status code messages. All condition messages are returned to this variable in a standard ASCII character string format.
All SQLSTATE messages are 5 characters in length. The first 2 characters define the message class and the last 3 characters define the message subclass.
For example, consider the SQLSTATE return code ‘22021’. The class of this message, 22, indicates a data exception condition. Its subclass, 021, indicates that a character not in the defined repertoire was encountered.
A SQL warning does not stop a request. A SQL exception stops a request.
Run UDFs in protected mode. Otherwise, the database may not trap errors that the function generates and a system error may occur. For more information, see When to Specify Unprotected Mode (ALTER FUNCTION External Form).
Make sure your UDFs return valid SQLSTATE codes. The database does not map SQLSTATE values returned by user-defined functions to their equivalent SQLCODE values. All SQLSTATE codes generated by UDFs are explicitly mapped to database messages as indicated by the following table:
| UDF SQL Return Code | Error Message | Description |
|---|---|---|
| Warning | 7505 | *** Warning 7505 in dbname.udfname: SQLSTATE 01Hxx: < user_function_message_text > |
| Exception | 7504 | *** Error 7504 in dbname.udfname: SQLSTATE U0xxx: < user_function_message_text > |
The string represented by user_function_message_text is a user-defined error message generated by the UDF.
The text represented by x characters in the SQLSTATE subclass is also assigned by the user-written UDF code. All characters must be either digits or uppercase Latin characters.
For more information about SQL exception and warning codes, see Return Codes.
For more information about SQLSTATE and SQLCODE, see Result Code Variables.
User-Defined Functions and Large Objects
The usage characteristics for a user-defined function with large object parameters or a large object return value are the same as the usage characteristics for any other UDF. You can specify a UDF that accepts a LOB value as an argument in any context in which a UDF is otherwise allowed. You can also use a UDF that returns a LOB value in any context in which a value of that type is appropriate and a UDF is otherwise allowed.
As with other functions and operators, automatic type conversions can be applied to the arguments or the return values of a UDF. Be careful about the possible performance implications of automatic type conversions with large objects used as UDF parameters. For example, a function whose formal parameter is a BLOB type can be passed a VARBYTE column as the actual parameter. The system converts the VARBYTE value into a temporary BLOB and then passes that to the UDF. Because even a temporary BLOB is stored on disk, the performance cost of the conversion is significant.
To avoid this, consider creating an overloaded function that explicitly accepts a VARBYTE TD_ANYTYPE argument. You cannot create overloaded functions to enforce row-level security constraints.
Another possible cause of undesired conversions is truncation. Data type includes declared length. Suppose the formal parameter of a UDF is declared as BLOB(100000) AS LOCATOR and the actual parameter is a column declared as BLOB without a locator specification. The maximum length of the source type defaults to 2,097,088,000 bytes. If the source type is longer than the target type, truncation may occur. Therefore, an automatic conversion operation, which produces a data copy and a temporary BLOB, must be generated. Whenever possible, define UDFs to accept the maximum length object by omitting the length specification.
This rule contradicts the policy for all other data types, which is that data type length declarations be no longer than necessary.
Restrictions on Declaring an External C++ Function
If you specify CPP in the Language Clause, then you must declare the main C++ function as extern "C" to make sure that the function name is not converted to an overloaded C++ name, for example:
extern "C"
void my_cpp(long int *input_int, long int *result, char sqlstate[6])
{
Client-Server UDF Code Specification
You must specify whether the UDF code for include files, object files, and source files is located on the client system or on the server. To do this, you specify C for client or S for server.
UDF code for library and package files is located on the server, but you still must specify the S location code for any library or package file paths you specify.
The character used to separate entities in the path specification is platform-specific when the file is stored on a client system, but not when the file is on the server.
The following table provides more information about writing the client and server location specifications for include, object, or source files:
| Location Code | Format Specification |
|---|---|
| C | Form required by client application (for example, BTEQ). See client documentation for form. |
| S | Use SOLIDUS character (/) or REVERSE SOLIDUS character (\) as separator in path specification for platform operating systems. |
UDF .so File Linkage Information
There is only one UDF .so file per application category per database per node.
| Application Category | .so File Format |
|---|---|
| > 0 | libudf_dbid_librevno_AppCat.so |
| = 0 | libudf_dbid_librevno.so |
When you create, replace, or drop a UDF, the UDF .so file for that database must be relinked with all the other UDF object files and then redistributed to all the nodes. The 5607 Warning message is normal.