Archives of the TeradataForum

Message Posted: Tue, 11 Nov 2014 @ 13:57:04 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I stumbled across a suggested use of a Function, such that you can compare Tables between two different Teradata systems, by making use of a Function and then using the TABLE Function :

     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';

In the example, dbc.Tables were being compared :

     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;

However, do you think this sort of Function could be used in order to do an Insert/Select between two different Terdata Systems ?

If so then, assuming it from taking data from the Prod system down to the Test system, where would the resource be taken up - in the production machine or the test machine ? Or both possibly ?

Also, is it a BAD idea, even in principle ?

Regards

David Clough
Senior BI Database Designer
BI Competency Centre

	<Prev	Next>		<<First	<Prev	Next>	Last>>