16.20 - Procedure - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Advanced SQL Engine
Teradata Database
Release Number
Release Date
April 2020
Content Type
Programming Reference
Publication ID
English (United States)

Here is a synopsis of the steps you take to develop, compile, install, and invoke a Java UDF. You can find details for each step in subsequent sections.

  1. Write, test, and debug the Java source code for the UDF outside of the database and place the resulting class or classes in a JAR or ZIP file (collectively called archive files).
  2. Call the SQLJ.INSTALL_JAR external stored procedure to register the archive file and its classes with the database, providing an SQL identifier for the JAR or ZIP file.
  3. Determine the level of access to operating system services that the UDF requires.
    IF the UDF … THEN …
    does not access local files and does not perform any restricted actions that require special permissions the UDF can run in protected execution mode as a thread of the Java hybrid server.

    Teradata Database creates one Java hybrid server for each node, and the server provides multiple threaded execution of Java external routines to all AMPs and PEs on the node.

    For security, the hybrid server and all of its resources, such as shared map files and events, use 'tdatuser' as the owner, a local operating system user that the Teradata Database installation process creates.

    accesses local files or performs actions that ordinary operating system users have permissions for
    requires access to specific resources that require special permissions use CREATE AUTHORIZATION or REPLACE AUTHORIZATION to create a context that identifies a native operating system user and allows the UDF to access resources by using its own secure server under the authorization of that user.
  4. Use CREATE FUNCTION or REPLACE FUNCTION with options that provide specific information about the Java UDF.
    Option Description
    LANGUAGE JAVA Identifies the source code language of the UDF.
    PARAMETER STYLE JAVA Indicates the parameter style.
    EXTERNAL NAME Provides the registered name of the JAR or ZIP file, Java class within the JAR or ZIP file, and Java method within the class to execute when the UDF is invoked in an SQL statement.
    EXTERNAL SECURITY (Optional) Associates execution of the UDF with the context created by the CREATE AUTHORIZATION or REPLACE AUTHORIZATION statement in the previous step.
  5. Test the UDF in Teradata Database until you are satisfied it works correctly.
  6. Use GRANT to grant privileges to users who are authorized to use the UDF.