Example: Using a Table Function to Pass Data Between Two Vantage Systems - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

If you are running a dual active Vantage system, or if you have a second Vantage 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