15.10 - SQLJ System Views - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

The SQLJ database and its views are used by the system to manage JAR files that implement Java external stored procedures.

The SQLJ database and its components are created via a DIP script called DIPSQLJ. The DIPSQLJ script is run as part of DIPALL and follows the pattern set by the DBC Data Dictionary initialization process. The DIPSQLJ script revokes all privileges, which could result in modification of SQLJ database views and external stored procedures.

The SQLJ database requires sufficient space for all required components to be defined within it, and the initial space allocation for this database is determined based upon that fact. The best practice is to avoid the placement of additional items into this database, as the SQLJ database could be considered an extension to the Teradata Data Dictionary defined in the DBC database.

The SQLJ system database contains the views:

  • JAR_JAR_USAGE
  • JARS
  • ROUTINE_JAR_USAGE
  • JAR_JAR_USAGE

    The JAR_JAR_USAGE view identifies each JAR owned by a given user or database on which other JARs defined on the system are dependent.

     

    View Column Name

    Description

    Data Type

    Format

    DatabaseId

    The identifier of the database or user in which the JAR identified by JarName is defined.

    BYTE(4)
    NOT NULL

    X(8)

    JarName

    The name designator for a JAR that depends on code from another JAR.

    VARCHAR(128)
    UNICODE UPPERCASE
    NOT CASESPECIFIC
    NOT NULL

    X(128)

    PathJarName

    The name designator for a JAR, which is in the SQL-Java path of the JAR identified by JarName.

    The SQL-Java path is the search path defined for a particular JAR. With a SQL-Java path defined, a method defined in a particular JAR (JAR A) may invoke a method which is defined in another JAR (JAR B), if JAR B (and all its classes) is contained in the SQL-Java path of JAR A. A SQL-Java path for a JAR can only be created, altered, or dropped via a call to SQLJ.Alter_Java_Path.

    VARCHAR(128)
    UNICODE
    UPPERCASE
    NOT CASESPECIFIC
    NOT NULL

    X(128)

    The corresponding system tables for SQLJ.JAR_JAR_USAGE are:

  • DBC.JAR_JAR_USAGE
  • DBC.DBase
  • DBC.TVM
  • Example  

    The following SELECT statement retrieves information about each JAR owned by a given user or database on which other JARs are dependent.

    ==> SELECT * FROM sqlj.jar_jar_usage;

    Result:

    DatabaseId  JarName  PathJarName
    ----------  -------  -----------
    0000B905    JAR1     JAR4
    0000B905    JAR2     JAR3

    JARS

    The JARS view identifies the installed JARs defined on the system that are accessible to the current user or database.

     

    View Column Name

    Description

    Data Type

    Format

    DatabaseId

    The identifier of the database or user in which the JAR identified by JarName is defined.

    BYTE(4)
    NOT NULL

    X(8)

    JarName

    The name designator for an installed JAR.

    VARCHAR(128)
    UNICODE
    UPPERCASE
    NOT CASESPECIFIC
    NOT NULL

    X(128)

    JarId

    The unique identifier for the JAR identified by JarName.

    BYTE(6)
    NOT NULL

    X(12)

    The corresponding system tables for SQLJ.JARS are:

  • DBC.DBase
  • DBC.JARS
  • Example  

    The following SELECT statement retrieves information about all JAR objects that are accessible to the current user or database.

    ==> SELECT * FROM sqlj.jars;

    Result:

    DatabaseId  JarName  JarId
    ----------  -------  ------------
    0000B905    JAR1     000061280000
    0000B905    JAR2     000062280000
    0000B905    JAR3     000063280000 

    ROUTINE_JAR_USAGE

    The ROUTINE_JAR_USAGE view identifies the JARs owned by a given user or database on which external Java routines defined on the system are dependent.

     

    View Column Name

    Description

    Data Type

    Format

    DatabaseId

    The identifier of the database or user in which the JAR identified by JarName is defined.

    BYTE(4)
    NOT NULL

    X(8)

    RoutineName

    The name designator for a Java external stored procedure that depends on the JAR identified by JarName.

    VARCHAR(128)
    UNICODE
    UPPERCASE
    NOT CASESPECIFIC
    NOT NULL

    X(128)

    JarName

    The name designator for a JAR that contains code that the RoutineName external stored procedure depends on.

    VARCHAR(128)
    UNICODE
    UPPERCASE
    NOT CASESPECIFIC
    NOT NULL

    X(128)

    Signature

    The signature defined for Java external stored procedure identified by RoutineName.

    VARCHAR(6400)
    LATIN
    CASESPECIFIC

    X(255)

    The corresponding system tables for SQLJ.ROUTINE_JAR_USAGE are:

  • DBC.ROUTINE_JAR_USAGE
  • DBC.DBase
  • DBC.TVM
  • Example  

    The following SELECT statement retrieves information about all of the JARs owned by a given user or database on which dependent external Java routines are defined. Information about these routines is also returned.

    ==> SELECT * FROM sqlj.routine_jar_usage;

    Result:

    DatabaseId  0000B905
        JarName JAR1
    RoutineName MYINT
      Signature (I[I)V
     DatabaseId 0000B905
        JarName JAR2
    RoutineName MYDEC
      Signature (Ljava/math/BigDecimal;[Ljava/math/BigDecimal;)V
     DatabaseId 0000B905
        JarName JAR3
    RoutineName MYVARBYTE
      Signature ([B[Ljava/lang/String;)V