Registering/Distributing JAR & ZIP Files for Java External Routines | Vantage - 17.10 - Registering and Distributing JAR and ZIP Files for Java External Routines - 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, test, and debug the source code that implements a Java UDF or external stored procedure (or both), you place the resulting class or classes in a JAR or ZIP file (collectively called archive files). Next, you must call the SQLJ.INSTALL_JAR external stored procedure to:
  • Register the archive file and its classes with the database
  • Distribute the archive file to all nodes of a system
  • Create an SQL identifier for the archive file that you use in the CREATE/REPLACE PROCEDURE or CREATE/REPLACE FUNCTION statement to define the Java external routine

When you call SQLJ.INSTALL_JAR, you register the archive file with the default database. Later, when you use the CREATE/REPLACE PROCEDURE or CREATE/REPLACE FUNCTION statement to define an external routine that is implemented by a class in the archive file, you must define the external routine in the same database in which the file was registered.

If you have other JAR or ZIP files that contain application classes required by an external routine, you can use SQLJ.INSTALL_JAR to register those files with the same database. Then, you can use SQLJ.ALTER_JAVA_PATH to specify that the search path of classes include classes from other JAR or ZIP files registered with the same database.

If you call SQLJ.INSTALL_JAR, you must have sufficient privileges on the default database to use the CREATE/REPLACE PROCEDURE or CREATE/REPLACE FUNCTION statement (or both). If other users need to define an external routine that is implemented by a class in the archive file, they must have the CREATE EXTERNAL PROCEDURE or CREATE FUNCTION privilege on the database in which the archive file was registered.

A recommended practice is to register all archive files and define all Java external routines in one database and then grant access to the external routines to users who need to execute them.

Before You Begin

Before you can register a JAR or ZIP file for an external routine, verify the SQLJ system database contains the INSTALL_JAR external stored procedure and that you have sufficient privileges on the procedure and on the default database.

  1. Use the following SQL statement to view the objects in the SQLJ system database:
    HELP DATABASE SQLJ;
  2. If the information that HELP DATABASE returns does not include the INSTALL_JAR external stored procedure, execute the DIPSQLJ script using the Database Initialization Program (DIP) utility.

    For more information on DIP, see Teradata Vantageā„¢ - Database Utilities, B035-1102.

  3. Verify you have the EXECUTE privilege on the INSTALL_JAR external stored procedure.
  4. If the default database is not the database with which you want to register the JAR or ZIP file, use the DATABASE statement to change the default database.
  5. Verify you have the CREATE EXTERNAL PROCEDURE or CREATE FUNCTION privilege on the default database.