query teradata and oracle DB with queryman at the same time

[bkj123]

Hello -

Would like to update a teradata table with data found in an oracle table.

I have ODBC connections for both Teradata and Oracle. I can also connect to each database, one at a time, via Queryman. Is there a way I can hit both in the same queryman session?

Thanks, Brian

 

[BillDHS]

MS Access is the only tool I have found to connect to multiple DB's at the same time.

Teradata has a utility called OLE Mod that will connect to Oracle, create an ETL script, and fastload the data to TD. Unfortunitely you need OLE DB, fastload, and WinCli to make the process work. Teradata has also changed there pricing structure as of 1/1/2003, so if you are missing any piece of the package, it could cost you upwards of $100,000 per node to get the products.

 

[RogerCruse]

This is the classic problem of keeping two databases in check.

There isn't any "out-of-the-box" solution, you'll have to create your our solution.

One day, when the database vendors have grown up and the processing power exists, maybe you'll be able to do this with a single piece of SQL... until then "code it"!

Roger...

 

[RanD]

I'm sure (almost) some QMan developer will come along behind me and negate what I'm going to say here...

Answer is 'no'. Reason. You connect to one machine (data source) at a time with QMan. I cannot even make simultaneous connections to 2 Teradata machines with QMan,
let alone an ORACLE machine and a Teradata machine.


You could however, run two QMan sessions, make the connections and use export and import to transfer data, if
amount of rows is small. Otherwise, use the utilities.

Regards.

Randy Volters
Certified Teradata Master
Class of '01

 

[SamBones]

You can just use Oracle and the teradata ODBC driver alone to connect the two.

You can create an Oracle DB link that uses an ODBC connection. This ODBC connection can be a Teradata ODBC driver. Then, with appropriate synonyms, you can actually make Teradata based tables accessible from within Oracle. It's Oracle-centric in that you have to work within Oracle, but you can do both selects and inserts into Teradata tables from within Oracle, and the TD tables look like local Oracle tables to your SQL*Plus or PL/SQL code.

We're doing this with Oracle on Solaris with the Teradata ODBC drivers for Solaris. It took a few days to get it working, but it's really clean and transparent once it's working.

The only downside is that if you try to join tables through the db link, it gets pretty slow. It's great for moving data back and forth though. It also requires no third party software if you already have Oracle and Teradata.

 

[SamBones]

P.S. So, once you've set up the db link from Oracle to Teradata via the TDODBC driver, you CAN just connect Queryman to the Oracle database and do something like...
[tt]
insert into TD.sometable
as
select *
from ORA.sometable
where ...
;
[/tt]
If you have a lot of data to update the TD table with, just move it over first with a command like this to a temp table, then do the update on the Teradata side so it's all teradata. That way the update is all at BYNET speed.

 

[TheMartian]

Sambones

An excellent suggestion. Can you confirm whether you are solely using the drivers or whether the Oracle Transparent Gateway for Teradata is part of the equation?

Thanx
Martin

 

[bkj123]

SamBones -

Thanks for the info. Understand some but looking for clarification. Please see comments/questions below...

You can just use Oracle and the teradata ODBC driver alone to connect the two.
/****************************
Are we talking from within oracle? Right now I access oracle via a dos prompt or from queryman - I do not have permission to hit the unix box directly (god bless). Looking below, how is the oracle DB link created?
*********************************/

You can create an Oracle DB link that uses an ODBC connection. This ODBC connection can be a Teradata ODBC driver. Then, with appropriate synonyms, you can actually make Teradata based tables accessible from within Oracle. It's Oracle-centric in that you have to work within Oracle, but you can do both selects and inserts into Teradata tables from within Oracle, and the TD tables look like local Oracle tables to your SQL*Plus or PL/SQL code.

We're doing this with Oracle on Solaris with the Teradata ODBC drivers for Solaris. It took a few days to get it working, but it's really clean and transparent once it's working.

The only downside is that if you try to join tables through the db link, it gets pretty slow. It's great for moving data back and forth though. It also requires no third party software if you already have Oracle and Teradata.

/****************************
definitely get the other post you made with the SQL code to insert as well as the performance issues you mention above. Really looking for clarification of the DB links

Any help is appreciated. Thank you - Brian
*********************************/

 

[SamBones]

Hi Martin - No, this solution is NOT using Oracle's Transparent Gateway product. And I would suggest that you never use OTG! Oracle doesn't support it very well and you end up with broken data feeds because they don't keep up with the latest versions of the other databases. We've been burned enough times that we've tossed it out!

Hi Brian - Where is your Oracle database running? If it's running on a Unix machine, you need to get the Teradata tools for that OS. To be specific, we have Oracle running on Sun Solaris. We got the Teradata Utilities Foundation (TUF) for Solaris. This includes things like ODBC, JDBC, BTEQ, FastExport, FastLoad, and the lower level CLI & CLI2 to make them all work. These all get installed on Solaris. Then, define an ODBC data source that points to your Teradata Database. Then, from within the Oracle database, you create a database link that uses the ODBC data source. You can create synonyms to make the link easier to use, but that's basically it. We have it working and it works great.

The details are a bit much to post here, but basically, get the Teradata TUF for Solaris and get it installed. Then get a good Oracle DBA level book and look up creating DB links using ODBC data sources.

Hope this helps.