Configuring access to Oracle databases

You can configure access to an Oracle database from the Oracle client system by setting environment variables and by updating Oracle configuration files such as tnsnames.ora and sqlnet.ora. For more information, see the Oracle product documentation.

Before you begin

Install client libraries.
Make sure that the library path contains paths to the Oracle client libraries.
If you are running a 64–bit version of InfoSphere® Information Server, make sure that the database clients you use are also 64–bit. If you are running a 32–bit version of InfoSphere Information Server, make sure that the database clients you use are also 32–bit. For example, on 64-bit Linux when Information Server is running as a 64-bit application, the library path should point to the 64-bit Oracle client libraries.
Ensure that your system meets the system requirements and that you have a supported version of the Oracle client and Oracle server. For system requirement information, see http://www.ibm.com/software/data/infosphere/info-server/overview/.
Ensure that the Oracle client can access the Oracle database. To test the connectivity between the Oracle client and Oracle database server, you can use the Oracle SQL*Plus utility.

About this task

You can use the dsenv script to update the environment variables that are used to configure access to Oracle databases. If you use the script, you must restart the server engine and the ASB Agent after you update the environment variables.

Procedure

Set either the ORACLE_HOME or the TNS_ADMIN environment variable so that the Oracle connector is able to access the Oracle configuration file, tnsnames.ora.
- If the ORACLE_HOME environment variable is specified, then the tnsnames.ora file must be in the $ORACLE_HOME/network/admin directory.
- If the TNS_ADMIN environment variable is specified, then the tnsnames.ora file must be in the $TNS_ADMIN directory.
- If both environment variables are specified, then the TNS_ADMIN environment variable takes precedence.
- Setting these environment variables is not mandatory. However, if one or both environment variables are not specified, then you cannot select a connect descriptor name to define the connection to the Oracle database. Instead, when you define the connection, you must provide the complete connect descriptor definition or specify an Oracle Easy Connect string.
Note: If you use the Oracle Basic Instant Client or the Basic Lite Instant Client, the tnsnames.ora file is not automatically created for you. You must manually create the file and save it to a directory. Then specify the location of the file in the TNS_ADMIN environment variable. For information about creating the tnsnames.ora file manually, see the Oracle documentation.
Set the library path environment variable to include the directory where the Oracle client libraries are located. The default location for client libraries are as follows:
- On Windows, C:\app\username\product\11.2.0\client_1\BIN, where username represents a local operating system user name. If the complete Oracle database product is installed on the InfoSphere Information Server engine computer instead of just the Oracle client product, then the path would be C:\app\username\product\11.2.0\dbhome_1\BIN.
- On Linux or UNIX, u01/app/oracle/product/11.2.0/client_1
Set the NLS_LANG environment variable to a value that is compatible with the NLS map name that is specified for the job. The default value for the NLS_LANG environment variable is AMERICAN_AMERICA.US7ASCII.
The Oracle client assumes that the data that is exchanged with the stage is encoded according to the NLS_LANG setting. However, the data might be encoded according to the NLS map name setting. If the NLS_LANG setting and the NLS map name setting are not compatible, data might be corrupted, and invalid values might be stored in the database or retrieved from the database. Ensure that you synchronize the NLS_LANG environment variable and NLS map name values that are used for the job.

On Microsoft Windows installations, if the NLS_LANG environment variable is not set, the Oracle client uses the value from the Windows registry.