Steps to configure Oracle database federation from DB2 on zLinux. (Or any kind of *nix)
These steps are for Oracle database (any supported version from Oracle) to DB2 9.5 but they should work as it is for DB2 9.7 or later with proper Oracle client install.
1. Install DB2
3. Install Relational connectors
or Install InfoSphere DB2 (The InfoSphere DB2 is a limited edition of DB2 with relational connectors to connect to other relational databases such as Oracle, SQL Server, Sybase etc.). You do not need to install IBM branded DataDirect ODBC drivers in order to connect to Oracle since we will be installing Oracle client to connect to Oracle from DB2 server. (InfoSphere DB2 is used to be known as IBM Webspehere Federation Server.)
Install Oracle Client
Get the proper Oracle client to install it on your zLinux (or any kind of *Nix) DB2 server.
Follow these steps to make Oracle and DB2 talking to each other properly.
1. Find out the group id which was used to create a DB2 instance. You can do ls -l in your DB22 home directory to find out the group name. If you followed default group name, it could be db2iadm1. We will use this group name to create oracle user name under which we will install the Oracle client.
2. Create a user id for Oracle client such as oracle and specify the same group name to which DB22 instance belongs to. We are doing this so that we do not have to change the file permissions in Oracle client install for DB2 instance to access Oracle client’s files.
3. Launch Oracle client installation GUI to install the Oracle client. Apply right patches as recommended by the Oracle.
4. After install is complete, ask your Oracle DBA to give you tnsnames.ora entries so that you can copy those entries in your tnsnames.ora file. Take help from your Oracle DBA to complete this step.
5. Login as Oracle user and type command tnsping to successfully ping the Oracle database from the zLinux.
6. Type $ echo $ORACLE_HOME and note down the entry. You will need this information when you configure federation for Oracle tables.
7. Login as DB2 instance user and add following entries to your .bashrc.
export ORACLE_HOME=/opt/oracle/product/10.2/db_1 export PATH=$ORACLE_HOME/bin:$PATH
Change the ORACLE_HOME to the same value that you got in step-6.
8. Logout and login as db2 instance user. Type command tnsping and you should be able to successfully ping.
9. Make a connection to your Oracle database using your SQL*Plus.
After you have make a successful connection to Oracle from your DB2 instance, you are now ready to install Oracle relational connector to create nick names for the Oracle tables in DB2.
Install InfoSphere DB2 or DB2 / Relational Connectors
Install DB2 either using IBM InfoSphere DB2 or install DB2 relational connectors in your existing DB2 instance where you like to do federation from other data sources like SQL Server, Oracle etc.
Follow the GUI install for InfoSphere DB2 or relational connectors for an existing DB2 instance and complete the installation. On the Select features to Install screen, select Oracle data source support only.
After GUI install is over, complete these steps to make sure that we selected right choices for the Oracle database. The GUI install also builds library libdb2net8F.so and this might fail if GUI can not find g++ command.
Go to your ~/sqllib/lib64 directory and you should see main library for Oracle libdb2net8.so. The other 2 additional files are libdb2net8F.so and libdb2net8U.so.
Go to the $HOME/sqllib/cfg directory. Check contents of the db2dj.ini file and you can make changes in this file if you were not sure of specified information during the GUI install.
$ cat db2dj.ini DJX_ODBC_LIBRARY_PATH=/opt/ibm/WSII/odbc/lib ODBCINI=/home/db2inst1/odbc.ini ORACLE_HOME=/opt/oracle/product/10.2/db_1
Make sure that the ORACLE_HOME matches with the ORACLE_HOME entry that you specified in your .bashrc file. The DJX_ODBC_LIBRARY_PATH and ODBCINI entries are for the SQL Server.
If you make changes to the db2dj.ini file, please stop and start the instance.
$ db2stop force $ db2start
Now, you are ready to create Oracle wrapper etc. The easiest way to create these without making mistakes is to use the DB2 Control Center GUI tool. You can save commands in a file for each step so that you can use the script in future when you need to configure it again on other servers. This is the best approach to do it right.
1. Make sure that you have DBM CFG param FEDERATED=YES.
$ db2 get dbm cfg | grep -i federate Federated Database System Support (FEDERATED) = YES
2. Launch db2cc either on your server or from your client machine. Catalog DB2 system, instance and database name.
A. Create a Wrapper. Specify ORACLE_HOME value in the variable window.
B. Create a Server Definition. Specify NODE name as the Oracle Service name or Oracle SID. This is important for a proper connection to establish.
C. Create User Mappings.
D. Create Nicknames. You should be able to discover the Oracle tables names. If that does not work, repeat the steps to make sure that your ORACLE_HOME, SID etc are defined correctly.
You have now nick names for the Oracle database tables that you selected.