Access Oracle through the DB2 Federation

Access Oracle through the DB2 Federation

1. View OS and DB versions
OS version:
C: \ Users \ Administrator> ver
Microsoft Windows [version 6.1.7601]
Oracle version:
SQL> select * from v $ version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
DB2 version:
C: \ Users \ Administrator> db2level
DB21085I instance "DB2" uses "32" bit and DB2 code release version "SQL09076", which is identified
"08070107 ".
Refer to "DB2 v9.7.600.413", "s120516", and "IP23322", revised to "6 ".
The product uses the DB2 copy name "DB2COPY1" installed in "C: \ PROGRA ~ 2 \ IBM \ SQLLIB.
2. Configure the sqllib/cfg/db2dj. ini file in the DB2 installation directory and add the following ORACLE environment variables:
ORACLE_HOME = D: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1
ORACLE_BASE = E: \ app \ Administrator
ORA_NLS33 = D: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ nls \ data
TNS_ADMIN = D: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ NETWORK \ ADMIN
NLS_LANG = american_CHINA.ZHS16GBK

3. Modify the DBM parameter FEDERATED to YES to support federation.
C: \> db2 update dbm cfg using FEDERATED YES
The DB20000I update database manager configuration command is successfully completed.
SQL1362W does not dynamically change one or more parameters submitted for immediate modification. Until the next time the application is started or sent
After the TERMINATE command, the client changes will take effect. Until the next DB2START
After the command, the server change will take effect.
C: \> db2 terminate
The DB20000I TERMINATE command is successfully completed.
4. I will not create a Test Database
5. Register oracle wrappers
C: \> db2 disconnect all
The DB20000I SQL DISCONNECT command is successfully completed.

C: \> db2stop
SQL1064N DB2STOP is successfully processed.

C: \> db2admin stop
Sql00007w has successfully stopped the DB2 management server ".

C: \> db2admin start
Sql00006w has successfully started the DB2 management server.

C: \> db2start

SQL1063N DB2START is successfully processed.

C: \> db2 create wrapper net8
The DB20000I SQL command is successfully completed.

Alternatively, you can create one as follows.
C: \> db2 create wrapper net8 LIBRARY 'C: \ Program Files (x86) \ IBM \ SQLLIB \ BIN \ db2net8. dll'
The DB20000I SQL command is successfully completed.
The DB2 official documentation clearly states that net8 must be used for federated access to oracle, because the corresponding db2net8. dll file is supported, as is db2drda. ddl.
The official explanation is as follows:
Register the NET8 wrapper on a federated server to access Oracle data sources. NET8 is the predefined name for the wrapper that you can use to access Oracle data sources.
6. Define an oracle Data Source registration server
C: \> db2 create server f_oracle type oracle version 11.2.0.3 wrapper net8 options (node 'orcl ')
DB21034E this command is processed as an SQL statement because it is an invalid "command line processor. In
During SQL processing, it returns:
The SQL0103N numeric text "11.2.0.3" is invalid. SQLSTATE = 42604

C: \> db2 create server f_oracle type oracle version 11.2.0 wrapper net8 options (node 'orcl ')
The DB20000I SQL command is successfully completed.
7. Create a user ing for the oracle Data source:
C: \> db2 create user mapping for db2admin Server f_oracle options (remote_authid 'line', remote_password 'line ')
The DB20000I SQL command is successfully completed.
8. Create a user nickname and Test
C: \> db2 create nickname nk_orcl for f_oracle.sys.all_users
The DB20000I SQL command is successfully completed.
C: \> db2 select count (*) from nk_orcl

1
-----------
31

1 record selected.
C: \> db2 select count (*) from sys. all_users

COUNT (*)
------------------------
+ 3.20.000000000e + 001

1 record selected.

Q: Do I have to create a nickname every time I access a table in Oracle user lyn?