I have my Teradata set up to connect to three different ODBC connections at the same time.
I would like to be able to do select queries joining tables from different ODBC connections.
from ODBC1.Employee e
join ODBC2.Address a
on e.id = a.id
Someone told me that this may be possible by creating a volatile/GTT tables. In the example above, I create a volatile/GTT table with data from ODBC2, and therefore
I tried that, but I can't run a select on the table from the other connection.
This link should give you a fair idea about both global temporary tables and volatile tables
In short the data is not available in either case across different sessions!
But what I'm looking for is whether it is possible to use the tables globally through different ODBC connection (not database under the same connection).
I have 2 ODBC drivers set up as follow:
I open Teradata SQL Assistant, then connect to both Teradata servers.
Then how do I use the table in TeradataServer1 in TeradataServer2?
I tried creating a GTT and VT in one of the servers, but the other server don't recognise their existence.
I don't think you can use query to disconnect from the existing ODBC connection and connect to some other ODBC connection through SQL Assistant.
But yes, you can do this in BTEQ using CLI connection between two servers.
Please post any suggestions if you can do this in SQL Assistant.
Teradata doesn't provide any builtin way to access data from two different Teradata servers within the same query, not in ODBC and not in BTEQ.
You have to write a table UDF or use a 3rd party application which allows that, but the data will be moved across the network and joined locally on the client, so better don't try that on a large table.
Why do you need to access data from two servers?
But in BTEQ while logging in we can mention the host name of server1 and do the transaformation...what ever is required..
And then log off from server1...connect to server2 with hostname..etc...and can work on it...right ?
Because I have used the same way to export production data from prod server(TD5555) and then loaded it to development server(TD5400)...using fast export script which also generates multi load script.....
I think, this work around can be used.....!
of course you can connect to multiple servers in BTEQ one after the other, but fmartinus wanted to access both servers within the same query.
Hi Dieter, Suresh,
Because the database admin somehow manage to have two tables in separate system, which is annoying.
I thought if I spool the output of one server to a volatile table, then joining it when I ran the query in the other table, that might work.
But it seems that even volatile table is restricted to a server, am I right?
Yep, you're right, Volatile and Globale Temp tables are only usable within a single session.