Connect to Two oracle Servers in a single Oracle stage

samyamkrishna · Post by **samyamkrishna** » Thu Sep 06, 2012 6:24 am

Hi,

My requirement is to connect to two different oracle databases using a single oracle enterprise stage or an OCI stage.

Is this possible?

REgards,
Samyam

chulett · Post by **chulett** » Thu Sep 06, 2012 7:01 am

No. You could connect to one and access the other over a dblink, I suppose. Why this odd "requirement"? You need to do what across "two Oracle servers"?

samyamkrishna · Post by **samyamkrishna** » Thu Sep 06, 2012 7:33 am

We have a job which does this operation using two oracle stage.

But the job runs for 3 hrs every day.

Its a simple update insert job with 500 columns and 9 million record.

What i was trying to do here was to push that operation onto the database side rather
then fetching 9 million wide records fron DB to Datastage and then put it into another DB.

just trying to make this job run faster.

Is there anything else i can do to make it run better.

chulett · Post by **chulett** » Thu Sep 06, 2012 7:38 am

Pretty simple to push things to the database side when everything is in the same instance but if you're saying that these are two separate instances then the data still has to come out of one and travel across something to get to the other. It might help to explain your topography in more detail.

Otherwise, have you considered bulk loading the data from the first DB into a work table in the second DB and then using MERGE to perform the update/insert?

samyamkrishna · Post by **samyamkrishna** » Thu Sep 06, 2012 7:49 am

Ya even i am trying to the same. Bulk load into a temp table and then merge it with teh actual table.

Lets see. If the support team aggres to this approach and the creation of new table we may just go ahed with this idea.