UDFs that Implement UDT Functionality | Teradata Vantage - UDFs that Implement UDT Functionality - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

Here is a synopsis of the steps you take to develop, compile, install, and use a UDF that implements UDT transform, ordering, or cast functionality:

  1. Write, test, and debug the C or C++ code for the UDF.

    You can use the Teradata C/C++ UDF Debugger, which is a version of GDB (the gnu Source-Level Debugger) that contains extensions for the database. For more information, see C/C++ Command-line Debugging for UDFs.

  2. Use the CREATE FUNCTION or REPLACE FUNCTION statement to identify the location of the source code or object, and install it on the server.

    Recommendation: In general, you should not create UDFs in Teradata system databases such as SYSLIB or SYSUDTLIB. For more information, see Installing the Function.

    The function is compiled, if the source code is submitted, linked to the dynamic linked library (DLL or SO) associated with the database in which the function resides, and distributed to all database nodes in the system.

  3. Register the UDF as a transform, ordering, or cast routine.
    IF the UDF implements this functionality for a UDT … THEN use this statement to register the UDF …
    cast CREATE CAST or REPLACE CAST
    ordering CREATE ORDERING or REPLACE ORDERING
    transform CREATE TRANSFORM or REPLACE TRANSFORM
  4. Test the UDF in Teradata Database in protected execution mode until you are satisfied it works correctly.

    Protected mode is the default execution mode for a UDF. In protected mode, Teradata Database isolates all of the data the UDF might access as a separate process in its own local workspace. This makes the function run slower. If any memory violation or other system error occurs, the error is localized to the function and the transaction executing the function.

  5. Use ALTER FUNCTION to change the UDF to run in nonprotected execution mode.
    If you subsequently use REPLACE FUNCTION to replace the function, the execution mode reverts back to protected mode.
  6. Rerun the tests from Step 4 to test the UDF in nonprotected execution mode until you are satisfied it works correctly.
  7. Use GRANT to grant privileges to users who are authorized to use the UDF and the UDT.