Accessing Oracle data sources after upgrading

Use this procedure to access Oracle data sources if you upgraded the instance and database or only the database.

1. Check the settings of the Oracle environment variables:
   1. Locate the db2dj.ini file. The db2dj.ini file is located in the file that is specified by DB2 registry variable, DB2_DJ_INI. When this variable is not set, the default is as follows:

      UNIX

      instancehome/sqllib/cfg/db2dj.ini, where instancehome is the home directory of the instance owner.

      Windows

      DB2PATH\cfg\db2dj.ini, where DB2PATH is the directory where the DB2 database system is installed, for example, C:\Program Files\IBM\SQLLIB.

   2. Ensure the environment variables in the db2dj.ini file are set as necessary. These variables are:

      DB2LIBPATH

      Append the DB2LIBPATH variable to the fully-qualified directory path where the Oracle client OCI libraries are located.

      On UNIX operating systems:

      DB2LIBPATH=oracle_home_directory/lib

      On Windows operating systems:

      DB2LIBPATH=oracle_home_directory\lib

      This environment variable is required.

      ORACLE_HOME

      The fully-qualified directory path where the Oracle client software is installed. For example:

      ORACLE_HOME=/usr/oracle/8.1.7

      /usr/oracle/8.1.7 is the fully-qualified path of the Oracle home directory. This environment variable is required.

      Optional: ORACLE_BASE

      The root of the Oracle client directory tree. For example:

      ORACLE_BASE=oracle_root_directory

      Optional: ORA_NLS

      The directory where location-specific data is stored for the version of Oracle that you are using. For example:

      ORACLE_NLS33=oracle_root_directory/ocommon/nls/admin/data

      In this example, the location-specific data is stored in the oracle_root_directory/ocommon/nls/admin/data directory on UNIX federated servers that access Oracle 8.1 data sources.

      Optional: TNS_ADMIN

      The directory where the tnsnames.ora file is located. Set this parameter only if the location of the tnsnames.ora is different from the default location.

      • On UNIX operating systems, the default location for the tnsnames.ora file is the $ORACLE_HOME/network/admin directory.
      • On Windows operating systems, the default location for the tnsnames.ora file is the %ORACLE_HOME%\NETWORK\ADMIN directory.

      For example, if the tnsnames.ora file resides in the /home/thomasj directory, set the variable as follows:

      TNS_ADMIN=/home/thomasj

2. For UNIX operating systems only: Update the .profile file of the DB2 instance with the Oracle environment variable by adding the following line:

   export ORACLE_HOME=oracle_home_directory

   oracle_home_directory is the directory where the Oracle client software is installed.
3. Perform the DB2 instance .profile by entering:

   . .profile

4. If you changed any environment setting in the preceding steps, recycle the DB2 instance: By recycling the DB2 instance, you ensure that the environment variable changes are properly set on the federated server.
   1. Issue the db2stop command.
   2. Issue the db2start command.
5. Set up and test the Oracle client configuration files (tnsnames.ora and sqlnet.ora). Use Oracle sqlplus to test connectivity to the Oracle server.
6. Verify that the upgrade was successful:
   1. Connect to the database.
   2. Issue SELECT, INSERT, UPDATE, and DELETE statements on the nicknames to verify that you can view and modify the data. You can also open a passthrough session to the remote database, and issue a SELECT statement on a remote table.
   Restriction: For upgrades from Version 8, if you have LOB columns that are mapped to Oracle LONG data types, and if you want to perform INSERT, UPDATE, and DELETE operations on the LOB columns, you must drop the existing nickname and recreate the nickname with LOB columns that map to the Oracle LOB columns.