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