Development Synopsis | Java External Stored Procedures | Teradata Vantage - Overall Development Synopsis - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
rin1593638965306.ditamap
dita:ditavalPath
rin1593638965306.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

System Requirements

Before you can run Java external stored procedures, your system must meet certain requirements. For more information, see System Requirements.

Procedure

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

  1. Write, test, and debug the Java source code for the external stored procedure outside of the database and place the resulting class or classes in a JAR or ZIP file.
  2. Call the SQLJ.INSTALL_JAR external stored procedure to register the JAR or ZIP 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 external stored procedure requires.
    IF the external stored procedure … THEN …
    does not perform I/O or accesses operating system resources that ordinary operating system users have access to the external stored procedure can run in protected execution mode as a separate process under 'tdatuser', a local operating system user that the database installation process creates.
    requires access to specific operating system resources use CREATE AUTHORIZATION or REPLACE AUTHORIZATION to create a context that identifies a native operating system user and allows the external stored procedure to access resources by using its own secure server under the authorization of that user.

    The meaning of I/O as it applies to external stored procedures is any operating system call that requires the operating system to retain a resource context, such as for open files or other operating system services. Such resource usage usually returns a handle to the caller that is used to access the resource and must be released when finished using it.

  4. Use CREATE PROCEDURE or REPLACE PROCEDURE with options that provide specific information about the external stored procedure.
    Option Description
    • NO SQL (Default)
    • CONTAINS SQL
    • READS SQL DATA
    • MODIFIES SQL DATA
    Indicates whether the external stored procedure executes SQL statements and, if so, whether the statements read or modify SQL data in the database.
    LANGUAGE JAVA Identifies the source code language of the external stored procedure.
    PARAMETER STYLE JAVA Indicates the parameter style.
    EXTERNAL NAME Provides the registered name of the JAR file, Java class within the JAR file, and Java method within the class to execute when the external stored procedure is invoked in a CALL statement.
    EXTERNAL SECURITY (Optional) Associates execution of the external stored procedure with the context created by the CREATE AUTHORIZATION or REPLACE AUTHORIZATION statement in the previous step.
  5. Test the external stored procedure in the database until you are satisfied it works correctly.
  6. Use GRANT to grant privileges to users who are authorized to use the external stored procedure.

Related Information

FOR more information on … SEE …
creating an external stored procedure related topics in this document.
debugging a Java external stored procedure Debugging Using Trace Tables.
registering a JAR file Registering the JAR or ZIP File.
  • CREATE PROCEDURE
  • REPLACE PROCEDURE
  • CREATE AUTHORIZATION
  • REPLACE AUTHORIZATION
  • Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • Teradata Vantage™ - Database Administration, B035-1093.
code examples for external stored procedures External Stored Procedure Code Examples.