Sharing table or view in multiple ODBC Connections

Database

Sharing table or view in multiple ODBC Connections

Hi all,

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.

For example:

select *

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.

Any advise?

Thanks

9 REPLIES
Enthusiast

Re: Sharing table or view in multiple ODBC Connections

This link should give you a fair idea about both global temporary tables and volatile tables

http://forums.teradata.com/forum/database/difference-between-gtt-and-volatile-tables

In short the data is not available in either case across different sessions!

Re: Sharing table or view in multiple ODBC Connections

Thanks Qaisar,

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).

For example:

I have 2 ODBC drivers set up as follow:

1. Teradataserver1.abc.com.au

2. Teradataserver2.abc.com.au

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.

Enthusiast

Re: Sharing table or view in multiple ODBC Connections

Hi,

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.

Regards,

Suresh

Senior Apprentice

Re: Sharing table or view in multiple ODBC Connections

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?

Dieter

Enthusiast

Re: Sharing table or view in multiple ODBC Connections

Dieter,

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.....!

Suresh

Senior Apprentice

Re: Sharing table or view in multiple ODBC Connections

Hi Suresh,

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.

Dieter

Enthusiast

Re: Sharing table or view in multiple ODBC Connections

Yeah Dieter,

you are correct, this is not possible with in the same query.

Suresh

Re: Sharing table or view in multiple ODBC Connections

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?

F

Senior Apprentice

Re: Sharing table or view in multiple ODBC Connections

Yep, you're right, Volatile and Globale Temp tables are only usable within a single session.

Dieter