DSXchange

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???

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.

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).

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.

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.

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.

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.

Exactly, as already explained.