Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   TABLE Function
 
From:   David Clough

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016