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.
About this task
If you upgraded only the database, follow all of the steps in this procedure.
If you upgraded the instance and database
on UNIX operating systems, you might need to perform
only some of the steps in this procedure. The steps that you might
need to perform are:
- Recycling the Db2® instance
- Verifying the upgrade
Procedure
- Check the settings of the Oracle environment variables:
- 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.
- 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:
This environment variable is required.DB2LIBPATH=oracle_home_directory\lib
- ORACLE_HOME
- The fully-qualified directory path where the Oracle client software
is installed. For example:
/usr/oracle/11.2.0 is the fully-qualified path of the Oracle home directory. This environment variable is required.ORACLE_HOME=/usr/oracle/11.2.0
- 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:
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 11.2 data sources.ORACLE_NLS33=oracle_root_directory/ocommon/nls/admin/data
- 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.
TNS_ADMIN=/home/thomasj
- Locate the db2dj.ini file.
-
For UNIX
operating systems only: Update the .profile file of the Db2 instance with the
Oracle environment variable by adding the following line:
oracle_home_directory is the directory where the Oracle client software is installed.export ORACLE_HOME=oracle_home_directory
-
Perform the Db2 instance
.profile
by entering:. .profile
-
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.
- Issue the db2stop command.
- Issue the db2start command.
- Set up and test the Oracle client configuration files (tnsnames.ora and sqlnet.ora). Use Oracle sqlplus to test connectivity to the Oracle server.
- Verify that the upgrade was successful:
- Connect to the database.
- 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.