15.10 - EXTERNAL - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Introduction to the external function body reference clause.

This clause is mandatory for all table UDFs.

This clause can specify four different things:

  • The keyword EXTERNAL only.
  • The keywords EXTERNAL NAME plus an external function name (with optional Parameter Style specification).

    This is a mandatory attribute for all UDFs.

  • The keywords EXTERNAL NAME plus a set of external string literals.
  • The keywords EXTERNAL NAME plus a Java JAR ID specification.

Example: Using a Table Function to Pass Data Between Two Teradata Database Systems

If you are running a dual active Teradata system, or if you have a second Teradata system for a development or test machine, there might be times when you would need to pass data between the two platforms. For example, it might be useful to query the dictionary tables from one system so they can be correlated with the other. This example illustrates how you might do that.

The following SELECT request uses a table function named tdat that executes a query on a remote Teradata system and then returns the answer set to the requesting system. In this example, the UDF returns all the database names in the dictionary tables of the other system.

    SELECT * 
    FROM table(rdg.tdat(2,1,'adw1/rdg,rdg', 'SELECT databasename FROM                dbc.databasesV')); 

This SELECT request is passed as a fixed input argument of the table function from the requesting system and is executed on the target system, as is the logon string for that system.

The DDL to create the table function is as follows:

    CREATE FUNCTION rdg.tdat 
        (rowc       INTEGER, 
         InLineNum  INTEGER, 
         logonstr   VARCHAR(50)   CHARACTER SET LATIN, 
         sqlRqst    VARCHAR(512)  CHARACTER SET LATIN) 
     RETURNS TABLE (
         ampId      INTEGER, 
         cnt        INTEGER, 
         OutLineNum INTEGER, 
         str1       VARCHAR(256)  CHARACTER SET LATIN, 
           .
           .
           .
         str20      VARCHAR(256)  CHARACTER SET LATIN) 
    SPECIFIC tdat 
    LANGUAGE C 
    NO SQL  
    PARAMETER STYLE SQL 
    NOT DETERMINISTIC 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'SS:tdat:/home/rdg/tdat/Tdat.c:SL:cliv2';

By creating a view across two Teradata systems you can compare dictionary content across both platforms and compare detailed data such as table space or privileges.

The following view compares the rows that appear in the DBC.Tables view of each system.

    CREATE VIEW allTables AS
      SELECT 'Local System' AS system, databasename, tablename, version,
             tablekind, protectionType, JournalFlag, CreatorName,              requesttext(VARCHAR(100))
      FROM DBC.tables
    UNION
      SELECT 'Remote System', str1 (CHAR(30)), str2 (CHAR(30)), 
             str3 (INTEGER), str4 (CHAR(1)), str5 (CHAR(1)), 
             str6 (CHAR(2)), str7 (CHAR(30)), str8 (VARCHAR(100))
      FROM table(rdg.tdat(2,1,'adw1/rdg,rdg', 'SELECT databasename,                  tablename, version, tablekind, protectionType,
                 JournalFlag, CreatorName, requesttext(VARCHAR(100))
                 FROM DBC.tables')) T;

A sampling of data returned from the remotely performed SELECT query, when ordered by tablename (for easy cross comparison, looks like this:

    System         DatabaseName  TableName     Version  TableKind
    -------------- ------------  ------------  -------  ---------
    Remote System  test          a             1        T
    Local System   DBC           AccessRights  1        T
    Remote System  DBC           AccessRights  1        T
    Remote System  DBC           AllSpaceV     1        V
    Local System   DBC           AllSpaceV     1        V
    Local System   rdg           allamp        1        T
    Remote System  test          allamp        1        T