Remote Oracle access with ODBC

magma · Post by **magma** » Mon Aug 08, 2005 11:19 pm

I've had a quick look around this site, but didn't find the magic document that explained all.

We have a need now to use DStage Server on one platform and talk to an Oracle instance on another platform (both AIX).

Whilst I can probably find a manual somewhere that talks me through configuring AIX ODBC to Oracle (I haven't attempted this before), I wanted to ask the forum for 'gotchas'.

Do you recommend remote access w/ODBC? performance? security issues?

Is the Bulk Loaded a good choice for remote-update and ODBC a good choice for remote-select?

adamski · Post by **adamski** » Mon Aug 08, 2005 11:40 pm

Hey Guy!

There are Oracle stages for all types of transactions and bulk loads. I cannot see the point of using ODBC. Some Oracle sites considder ODBC a securtiy risk when there is SQL *Net. Talk tou your DBA.

Keep an eye on viewtopic.php?t=94513 for some potential gotchas.

ogmios · Post by **ogmios** » Tue Aug 09, 2005 12:04 am

In version 6 of DataStage there were problems with the error codes using ODBC to Oracle (you wouldn't see the specific ORA error message), which wasn't the case when using Ora stage to Oracle. In one case this cost me 4 hours to figure out the "tablespace" was full using ODBC. Haven't tried it out on v7.

If you're not thinking of changing to a different database in the near future

, try to stick as close as to what you want to do. And the Oracle stages in DataStage are as close as you're going to get in DataStage.

[snipped]

Ogmios

chulett · Post by **chulett** » Tue Aug 09, 2005 12:12 am

ogmios wrote:One other thing to note is that the Oracle stages work differently for dates than the ODBC stage... ODBC wants to have to the external format for its dates as input, while the Oracle stages use the internal DataStage format (when inserting via an Oracle stage e.g.)

That would be the DB2 stage you are thinking of, I do believe. The Oracle stages still like their dates in external flavor.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 09, 2005 12:53 am

Hi Michael,

DataStage is, as far as Oracle is concerned, just another client application. So all you have to do is to configure the DataStage "server" machine with whatever Oracle client software is required; that is, the ODBC driver for Oracle (one ships with DataStage) and the Oracle client software.

That's it. Create ODBC data source definitions by editing the .odbc.ini file, and create entries in uvodbc.config to refer to the DSN and identify it as an ODBC data source rather than a UniVerse data source.