Linking Servers

UDA
Enthusiast

Linking Servers

Is there any plans to be able to link servers in Teradata. Now or in the future.
Does anybody think it would be useful?
For example run a query as follows:

SEL * FROM Primary_Server.TblA WHERE Col1 = 'marc';
SEL * FROM Secondary_Server.TblA WHERE Col1 = 'marc';

??
5 REPLIES
Enthusiast

Re: Linking Servers

If you're talking about a way to have multiple instances of Teradata accessed in a single SQL statement (i.e. cross-platform join), I think the answer is no. Teradata has always recommended that there should be a single data warehouse platform. Therefore, you would not have a need to join data from multiple platforms/instances in a single SQL statement.

If you need to move data from one Teradata instance to another, there are various tools that can be used to help accomplish this, Teradata Parallel Transporter being one of them. However, this functionality is pretty much limited to "insert into table on platform A, selecting data from platform B". It is not really being done in a single SQL statement, but is utilizing the existing utilities under the covers.

There are ways to do cross-platform joins with 3rd party tools. However, these tools tend to give poor performance once any kind of volume needs to pass from the originating platform to the platform performing the join. So, they work in small/limited cases, but fall apart as volumes grow.

I don't believe that Teradata should pursue this as the resulting "optimizer" would need to be much more complex than the existing optimizer since it would also have to take into account multiple platforms. I would rather have them spend their resources making their existing optimizer more robust as well as working on other DBMS features. That's my two cents.

Enthusiast

Re: Linking Servers

Thanks I appreciate that.
I don't mind if we cannot do it in an SQL Stmt.
A Utility is probably a better way. I like the uder the covers useage of the Teradata Parallel Transporter. What packages is it shipped with?

Also, can you suggest a simple to use 3rd party tool that does you mention. I would like to evaluate them.

Thanks again.
Enthusiast

Re: Linking Servers

Teradata Parallel Transporter is part of TTU 8.1 or 8.2. Prior to that, it did exist, but was named "Teradata Warehouse Builder".

As for tools that can do cross platform joins, Microsoft Access is the one that comes to mind first. IBM had a product that did this (I don't know what it's called anymore) as did Information Builders (called EDA/SQL). You might want to Google "cross platform join" to see companies that have products in this area.
Enthusiast

Re: Linking Servers

What about a table function?

-pt
Enthusiast

Re: Linking Servers

There is a class of product known as EII (Enterprise Information Integration) that uses data federation or data virtualization to turn multiple database sources into a single virtual database. When finding a vendor make sure they support your version of Teradata via native connectivity.

The EII server has an optimizer on it that decides how to execute a query against the various databases. If you have a scenario of joining two tables on different Teradata databases the optimizer decides what SQL to run on each table and how many rows need to be brought up and joined on the server.

These queries are slower than direct database queries since they need to go through the optimizer. These tools do offer caching options so you can cache popular queries on the server for very fast query response times. So if left unchecked it could hammer your Teradata but if cached it could ease the load.

There are drawbacks - they can be costly (but not as costly as Teradata nodes), they can put too much load on your databases, they need to be supported, they don't do much for your data quality. There are benefits - they can join different database platforms, they allow rapid delivery of new data sources to users, they provide stop gaps measures as you migrate between systems, they can access unstructured data sources.

Some of the established vendors include Ipedo, Certive, Denodo, IBM Federation Server and Composite. In addition the following vendors have recently added EII tools to their suite either by acquisition or partnership: Sybase, Informatica (Composite), Cognos (Composite), Group 1, SAP, SAS, Sun and Business Objects.

The Gartner Data Integration Magic Quadrant talked about how some of these new vendors are struggling to get customers for EII so it looks like the market is dominated by the established providers.