Can anyone please help where the datastage picks the tnsname

hellboy · Post by **hellboy** » Fri Nov 20, 2009 1:26 am

Can anyone please help where the datastage picks the tnsnames.ora or is there any other config regarding the tns entries from Data stage side???

ragavendradineshdsx · Post by **ragavendradineshdsx** » Fri Nov 20, 2009 2:34 am

As far as i know, Datastage always refer to .odbc.ini in your engine installation folder. Inside this file, you would have given the ORACLE_SID, username/password & port number and based on this, Datastage will connect to the oracle using the oracle client installed on the same server.

ArndW · Post by **ArndW** » Fri Nov 20, 2009 5:00 am

Actually, the path to the tnsnames.ora file is retrieved from the environment variables settings, the ORACLE_HOME points to the oracle directory, which in turn redirects to the system file location (/home/oracle/net/admin on AIX).

chulett · Post by **chulett** » Fri Nov 20, 2009 7:43 am

Right, it's got nothing to do with ODBC and for Windows it uses the environment variables - ORACLE_HOME or TNS_ADMIN if set to override the default location of $ORACLE_HOME/network/admin.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 20, 2009 6:51 pm

While Windows does not have an .odbc.ini file nor the ODBCINI environment variable, it does have a Registry entry called ODBC.INI (a hangover from Windows 3.1 and earlier when there wasn't a registry).

This contains ODBC data source names and definitions. Within any DSN for an Oracle connection explicit reference to the TNS name is made. Oracle stage types do not use ODBC at all, so the above discussion becomes moot. In Oracle stage types, direct reference is made to the TNS name.

chulett · Post by **chulett** » Fri Nov 20, 2009 8:07 pm

Yes, direct reference is made to the TNS name, a.k.a. the appropriate entry in the tnsnames.ora file... or possibly out on a names server. The question was how does DataStage (technically any app leveraging the client) know where to find the tnsnames.ora file.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 20, 2009 10:11 pm

Right, technically then, DataStage doesn't "pick" it at all. DataStage (merely another client application as far as Oracle is concerned) supplies the name and the Oracle client software "picks" the name and corresponding definition out of the tnsnames.ora file, whose location is specified as noted in earlier posts in this thread, most usually via the setting of the ORACLE_HOME environment variable.

chulett · Post by **chulett** » Sat Nov 21, 2009 7:56 am

Exactly, as already explained.