IBM Support

How to query Oracle using Db2 Federation through JDBC driver?

Question & Answer


Question

How to query Oracle using Db2 Federation through JDBC driver?

Cause

Customer wants to query Oracle using Db2 Federation through JDBC driver.

Answer

To configure a federation to access Oracle data source through JDBC wrapper, you must provide the federation with information about the data sources and objects that you want to access. Then create server, user mapping and nicknames for tables in remote Oracle server.
Before you begin
Check the driver FOoracle.jar in the path $/INSTANCE_HOME/sqllib/federation/jdbc/lib.
1. Enable Federation server and restart Db2.
  
$ db2 update dbm cfg using federated YES
$ db2stop force
$ db2start
2. Test the connection to the Oracle and verify the service is started correctly.
telnet <oracle_ip> <port> 
// If the connection is successful, you receive the following similar output from the command.

$ telnet test1.fyre.ibm.com 1521
Trying 9.30.99.101...
Connected to test1.fyre.ibm.com.
Escape character is '^]'.

If the connection fails, you will receive an error, please check the Oracle service status.
3.  Create wrapper, server, user mapping, nickname, and query the nickname.
(1) For Db2 on Linux or Unix platform
  
$ connect to testdb

$ CREATE SERVER server1 type jdbc VERSION 19 OPTIONS (DRIVER_CLASS 'com.ibm.fluidquery.jdbc.oracle.OracleDriver',DRIVER_PACKAGE  '/home/haijun/sqllib/federation/jdbc/lib/FOoracle.jar',URL 'jdbc:ibm:oracle://test1.fyre.ibm.com:1521;ServiceName=ora12c')

$ CREATE USER MAPPING FOR USER SERVER server1 OPTIONS  (REMOTE_AUTHID 'USER1', REMOTE_PASSWORD 'password1')

$ create nickname n1 for SERVER1.USER1.test1

$ select * from n1

NAME       ID
---------- ------------------------
haijun     +1.00000000000000E+001

  1 record(s) selected.
(2) For Db2 on windows platform
db2 => connect to sample

   Database Connection Information

 Database server        = DB2/NT64 11.5.6.0
 SQL authorization ID   = ADMINIST...
 Local database alias   = SAMPLE

db2 => CREATE SERVER server1 type jdbc VERSION 19 OPTIONS (DRIVER_CLASS 'com.ibm.fluidquery.jdbc.oracle.OracleDriver',DRIVER_PACKAGE  'C:\Program Files\IBM\SQLLIB_04\federation\jdbc\lib\FOoracle.jar',URL 'jdbc:ibm:oracle://test1.fyre.ibm.com:1521;ServiceName=orclpdb.fyre.ibm.com')

db2 => CREATE USER MAPPING FOR PUBLIC SERVER SERVER1 OPTIONS (REMOTE_AUTHID 'USER1', REMOTE_PASSWORD 'USER1')

db2 => set passthru server1

db2 => drop table test1

db2 => create table test1(col1 numeric, col2 varchar(10))

db2 => insert into test1 values(1, 'haijunshen')

db2 => set passthru reset

db2 => create nickname nk_test1 for server1."J15USER1".test1

db2 => select * from nk_test1

COL1                     COL2
------------------------ ----------
  +1.00000000000000E+000 haijunshen

  1 record(s) selected.

[{"Type":"SW","Line of Business":{"code":"","label":""},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCAVPX","label":"Federated Server"},"ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Document Information

Modified date:
15 December 2022

UID

ibm16440437

Page Feedback