Oracle environment variables

There are required and optional environment variables for Oracle data sources. These variables are set in the db2dj.ini file.

The following environment variables are valid for Oracle::
  • ORACLE_HOME
  • ORACLE_BASE (optional)
  • ORA_NLS (optional)
  • NLS_LANG (optional)
  • TNS_ADMIN (optional)

Variable descriptions

ORACLE_HOME

Set the ORACLE_HOME environment variable to the directory path where the Oracle client software is installed. Specify the fully qualified path for the environment variable: ORACLE_HOME=oracle_home_directory. For example, if the Oracle home directory is \usr\oracle\8.1.7, the entry in the db2dj.ini file is ORACLE_HOME=\usr\oracle\8.1.7.

If an individual user of the federated instance sets the ORACLE_HOME environment variable locally, the federated instance does not use that setting. The federated instance uses only the value of ORACLE_HOME that is set in the db2dj.ini file.

ORACLE_BASE

ORACLE_BASE represents the root of the Oracle client directory tree. If you set the ORACLE_BASE environment variable when you installed the Oracle client software, set the ORACLE_BASE environment variable on the federated server.

For example:


ORACLE_BASE=oracle_root_directory
ORA_NLS
If multiple versions of Oracle are running on your system, you must ensure that:
  • The appropriate ORA_NLS environment variable is set
  • The corresponding NLS data files for the versions that you are using are available
The location-specific data is stored in a directory that is specified by the ORA_NLS environment variable. Each version of Oracle has a different ORA_NLS data directory.
Table 1. ORA_NLS environment variable by version
Oracle version Environment variable
8.x, 9.x ORA_NLS33
10.x ORA_NLS10

For example, on federated servers that run UNIX that access Oracle 8.1 data sources, the ORA_NLS33 environment variable setting is:


ORA_NLS33=oracle_home_directory/ocommon/nls/admin/<data>
NLS_LANG
The NLS_LANG environment variable is a code page environment variable. Refer to the Oracle NLS documentation for information about setting this variable.
TNS_ADMIN
On federated servers that run Windows
The Oracle client looks for the tnsnames.ora file in the %ORACLE_HOME%\NETWORK\ADMIN directory, where %ORACLE_HOME% is defined in the db2dj.ini file. If the tnsnames.ora file is not in the %ORACLE_HOME%\NETWORK\ADMIN directory, you must set the TNS_ADMIN environment variable in the db2dj.ini file on the federated server. You set the environment variable in the db2dj.ini file to the path where the tnsnames.ora file is located.
On federated servers that run AIX® or Linux®
The Oracle client looks for the tnsnames.ora file in the /etc directory. If the tnsnames.ora file is not in the /etc directory, then the Oracle client looks for the tnsnames.ora file in the $ORACLE_HOME/network/admin directory, where $ORACLE_HOME is defined in db2dj.ini file. If the tnsnames.ora file is not in the $ORACLE_HOME/network/admin directory, you must set the TNS_ADMIN environment variable on the federated server. You set the environment variable in the db2dj.ini file to the path where the tnsnames.ora file is located.
For example, if the tnsnames.ora file is in the /home/oracle directory, you set the environment variable to: 

TNS_ADMIN=/home/oracle
On federated servers that run Solaris
The Oracle client looks for the tnsnames.ora file in the /var/opt/oracle directory. If the tnsnames.ora file is not in the /var/opt/oracle directory, then the Oracle client looks for the tnsnames.ora file in the $ORACLE_HOME/network/admin directory, where $ORACLE_HOME is defined in db2dj.ini file. If the tnsnames.ora file is not in the $ORACLE_HOME/network/admin directory, you must set the TNS_ADMIN environment variable. You set the variable in the db2dj.ini file to the path where the tnsnames.ora file is located.
For example, if the tnsnames.ora file is in the /home/oracle directory, you set the environment variable to: 

TNS_ADMIN=/home/oracle