RE: data move concept
Date: Wed, 19 Aug 2009 09:35:18 -0400
Message-ID: <23C4836D8E9C5F4280A66C0C247BC16F2CA27051_at_US-BOS-MX011.na.pxl.int>
WGB, Sorry, I've had the worst of luck with Heterogeneous Services and have not had a need to connect to DB2. Now someone told me the other day that Oracle is "giving away" the gateways. Seems odd and out of character for them, but you may want to check it out.
Dick Goulet
Senior Oracle DBA
PAREXEL International
From: Blanchard, William [mailto:wblanchard_at_societyinsurance.com] Sent: Wednesday, August 19, 2009 9:29 AM To: chet.justice_at_gmail.com; Goulet, Richard Cc: Brian.Zelli_at_roswellpark.org; Oracle L Subject: RE: data move concept
Dick,
Sorry to hijack this thread but I have a similar issue. I need to query one table in a DB2 database but don't see a way to do this, other than TG ($ issue). Did you use HS to connect to DB2 from within Oracle?
WGB
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of chet justice
Sent: Tuesday, August 18, 2009 9:36 PM
To: Richard.Goulet_at_parexel.com
Cc: Brian.Zelli_at_roswellpark.org; Oracle L
Subject: Re: data move concept
You don't even need Transparent Gateway, you can just use Heterogenous Services. Here's a link on AskTom as well: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406 709207206#18830681837358
I used it to connect to DB2.
Also, you might look into DBMS_ERRLOG,
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_errlog.
htm#BABGGCDF, as they will allow you to do set operations without having
to worry about potential errors (datatype conversion?).
It's fairly easy to roll your own PL/SQL solution, just a simple (hah!) INSERT INTO SELECT * FROM remote_table_at_db_link;
chet
On Tue, Aug 18, 2009 at 3:03 PM, Goulet, Richard <Richard.Goulet_at_parexel.com> wrote:
Brian,
Database links have been around for a long time and are reliable and dependable for connecting to other Oracle databases. As for Sql*Server try to get your hands on a copy of Oracle's transparent gateway for Sql*Server. It's easy top set up and if memory works right not that expensive. Then just write your scripts & your off.
Dick Goulet
Senior Oracle DBA
PAREXEL International
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zelli, Brian
Sent: Tuesday, August 18, 2009 2:37 PM
To: Oracle L
Subject: data move concept
Hello all,
I have systems that need to take data from other systems like most of you probably have to do. I need to take from sql server and other oracle systems. I can do it the old fashion way of creating flat files, ftp and sqlload. But with the tools of 10g is there a better way? Something that is reliable, re-runnable, error trapping, etc.....
I tested some things like:
- DTS from sql server 2000 into a temp table and then ran scripts against the temp table
- Created a link to oracle and did a select into
- flat file and sqlload
Anything else? I just want to try and use something current and reliable for moving and loading data. I am open to all kinds of ideas.
ciao,
Brian
This email message may contain legally privileged and/or confidential information. If you are not the intended recipient(s), or the employee or agent responsible for the delivery of this message to the intended recipient(s), you are hereby notified that any disclosure, copying, distribution, or use of this email message is prohibited. If you have received this message in error, please notify the sender immediately by e-mail and delete this email message from your computer. Thank you.
-- chet justice www.oraclenerd.com -- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 19 2009 - 08:35:18 CDT