Example: Using a Table Function to Pass Data between Two Vantage Systems - Teradata VantageCloud Lake

If you are running a dual active Vantage system, or have a second Vantage system for a development or test machine, you may need to pass data between the two platforms. You may want to query the dictionary tables from one system to correlate them with the other. This example shows a way to do that.

The following SELECT request uses table function tdat, which runs a query on a remote Teradata system and returns the answer set to the requesting system. The UDF returns 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 runs 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