IBM Support

How to query Oracle by using Db2 Federation and the ODBC driver?

Question & Answer


Question

How to query Oracle using Db2 Federation by using the ODBC driver?

Answer

Before You Begin
 
Enable Federation feature and restart Db2 to make it effective. 
$ db2 update dbm cfg using federated YES
Pre-Configuration on Linux or Unix
1. Make sure the required ODBC drivers are installed correctly.
$ ls ~/sqllib/federation/odbc/lib/FOora*.so
2. Make sure the data source is accessible to Federation server. 
$ telnet test1.ibm.com 1521
Trying 9.30.100.100...
Connected to test1.ibm.com.
Escape character is '^]'.
3. Create server, user mapping, nickname and query the nickname.
$ create server server1 type oracle_odbc version 12.1 options (HOST 'test1.ibm.com',
 PORT '1521', SERVICE_NAME 'pdborcl.fyre.ibm.com'  )

$ create user mapping for user server server1 options (REMOTE_AUTHID 'user1', 
REMOTE_PASSWORD 'password')

$ create nickname nk1_oracle for serve1."J15USER3".alltype_test

$ select count(*) from nk1_oracle

1
-----------
          1

  1 record(s) selected.
Limitation
1. Federation ODBC wrapper does not support CLOB, BLOB insert and update operation currently.  For other data types IUD operation, set the server option DB2_IUD_ENABLE 'Y'. 
$ alter server SERVER_NAME options (add DB2_IUD_ENABLE 'Y')
Change this option use following command
$ alter server SERVER_NAME options (set DB2_IUD_ENABLE 'N')
2. Federation ODBC wrapper does not support call procedure now, if you want to call procedure of oracle, refer to

[{"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:
08 June 2022

UID

ibm16431133

Page Feedback