Question & Answer
Question
How to query Oracle data source by using Db2 Federation through Oracle Instant Client?
Answer
Db2 version | Oracle data source version supported |
Oracle instantclient verison suggested
|
Db2 v11.1 | 11gR2, 12cR1, 12cR2 |
instantclient-basic-platform-12
|
Db2 v11.5 |
11gR2, 12cR1, 12cR2, 18c, 19c1, 21c
|
instantclient-basic-platform-21, 23
|
1.1 Download zip files instantclient-basic-<platfrom><version>.zip and instantclient-sqlplus-<platfrom><version>.zip from Oracle Instant Client Downloads, according to your server platform. Take the Linux platform as an example, the link is Instant Client for Linux x86-64.
1.2 unzip these two .zip files, and folder instantclient_<version> will be generated.
$ pwd
/home/db2inst8
$ ls
db2inst8 instantclient-sqlplus-linux.x64-21.5.0.0.0dbru.zip tnsnames.ora
instantclient-basic-linux.x64-21.5.0.0.0dbru.zip sqllib
$ unzip instantclient-basic-linux.x64-21.5.0.0.0dbru.zip
$ unzip instantclient-sqlplus-linux.x64-21.5.0.0.0dbru.zip
$ cd instantclient_21_5
$ ls
adrci libclntshcore.so libclntsh.so libclntsh.so.20.1 libocci.so.10.1 libocci.so.21.1 network uidrvci
BASIC_LICENSE libclntshcore.so.12.1 libclntsh.so.10.1 libclntsh.so.21.1 libocci.so.11.1 libociei.so ojdbc8.jar xstreams.jar
BASIC_README libclntshcore.so.18.1 libclntsh.so.11.1 libnnz21.so libocci.so.12.1 libocijdbc21.so sqlplus
genezi libclntshcore.so.19.1 libclntsh.so.12.1 libocci_gcc53.so libocci.so.18.1 liboramysql.so SQLPLUS_LICENSE
glogin.sql libclntshcore.so.20.1 libclntsh.so.18.1 libocci_gcc53.so.21.1 libocci.so.19.1 libsqlplusic.so SQLPLUS_README
libclntshcore.so.21.1 libclntsh.so.19.1 libocci.so libocci.so.20.1 libsqlplus.so ucp.jar
1.3 Make a link named as lib to the extracted instance client folder.
Notice: The link must be named as lib. Otherwise federation returns "SQL30090N" when issuing create wrapper statement.
In following example, the ORACLE_HOME path is
/home/db2inst8/instantclient_21_5
$ ln -s /home/db2inst8/instantclient_21_5 lib
$ ls
adrci libclntshcore.so libclntshcore.so.21.1 libclntsh.so.18.1 libocci_gcc53.so libocci.so.12.1 libociei.so ucp.jar
BASIC_LICENSE libclntshcore.so.12.1 libclntsh.so libclntsh.so.19.1 libocci_gcc53.so.21.1 libocci.so.18.1 libocijdbc21.so uidrvci
BASIC_README libclntshcore.so.18.1 libclntsh.so.10.1 libclntsh.so.20.1 libocci.so libocci.so.19.1 liboramysql.so xstreams.jar
genezi libclntshcore.so.19.1 libclntsh.so.11.1 libclntsh.so.21.1 libocci.so.10.1 libocci.so.20.1 network
lib libclntshcore.so.20.1 libclntsh.so.12.1 libnnz21.so libocci.so.11.1 libocci.so.21.1 ojdbc8.jar
1.4 Check libclntsh.so and libocci.so. If they don't exist, create links for the version of Instant Client.
# ls libclntsh.so
# ls libocci.so
// if they does not exist, create links
# ln -s libclntsh.so.21.1 libclntsh.so
# ln -s libocci.so.21.1 libocci.so
2. Configure tnsnames.ora file to add HOST, PORT, and SERVICE_NAME.
$ pwd
/home/db2inst8
$ cat tnsnames.ora
ora18c =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test1.fyre.ibm.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcdb.fyre.ibm.com)
)
)
$ chmod 755 tnsnames.ora
3. Test the installation and connection.
3.1 Set environment variables.
export ORACLE_HOME=/home/db2inst8/instantclient_21_5
export LD_LIBRARY_PATH=/home/db2inst8/instantclient_21_5/lib:$LD_LIBRARY_PATH
export PATH=/home/db2inst8/instantclient_21_5/lib:$PATH
export TNS_ADMIN=/home/db2inst8/
export NLS_LANG=American_America.UTF8
3.2 Test by using SQL*Plus.
[db2inst8@snore1 instantclient_21_5]$ sqlplus J15USER1/J15USER1@ora18c
SQL*Plus: Release 21.0.0.0.0 - Production on Sun Feb 6 23:58:24 2022
Version 21.5.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Sun Feb 06 2022 23:20:17 -08:00
Connected to:
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> select count(*) from alltype_test;
COUNT(*)
----------
4
4. Modify sqllib/cfg/db2dj.ini, add ORACLE_HOME, DB2LIBPATH, TNS_ADMIN and NLS_LANG variables.
The db2dj.ini file should cover the following information:
$ cat ~/sqllib/cfg/db2dj.ini
DJX_ODBC_LIBRARY_PATH=/home/db2inst8/sqllib/federation/odbc/lib:
DB2_FED_LIBPATH=/home/db2inst8/sqllib/federation/odbc/lib:/home/db2inst8/sqllib/federation/netezza/lib64:
ODBCINST=/home/db2inst8/sqllib/cfg/odbcinst.ini
NZ_ODBC_INI_PATH=/home/db2inst8/sqllib/cfg
ORACLE_HOME=/home/db2inst8/instantclient_21_5
DB2LIBPATH=/home/db2inst8/instantclient_21_5/lib
TNS_ADMIN=/home/db2inst8
NLS_LANG=American_America.UTF8
NLS_LANG is also required under unfenced mode from 11.1.4.5 and 11.5. Refer to this page for more details.
5. Clear the DB2LIBPATH variable in db2set and system environment, restart Db2
$ export DB2LIBPATH=
$ db2set DB2LIBPATH=
$ db2stop force
$ db2start
Note: if these two variables are not cleared, it reports following error messages,
CREATE WRAPPER NET8
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL30090N Operation invalid for application execution environment. Reason
code = "31". SQLSTATE=25000
6. Create wrapper, server, user mapping, nickname, and query the nickname.
CREATE WRAPPER NET8;
create server SERVER1 type oracle version 18 wrapper NET8 authorization "J15USER1" password "J15USER1" options (node 'ora18c');
create user mapping for user server SERVER1 options ( REMOTE_AUTHID 'J15USER1', REMOTE_PASSWORD 'J15USER1' );
set passthru server1;
drop table alltype_test;
create table alltype_test(col1_int integer, col2_smallint smallint, col3_double double precision, col4_char char(30), col5_varchar varchar(30), col6_date date, col7_time timestamp, col8_timestamp timestamp, col9_decimal numeric(10, 5));
insert into alltype_test values(1, 2, 3.1, 'hello4', 'hello world5', to_date('2020-02-14','yyyy-mm-dd'),'2020-09-17 10:44:27', '2020-09-17 10:44:28', 12.39);
insert into alltype_test values(21, 22, 23.1, 'hello24', 'hello world25', to_date('2020-02-14','yyyy-mm-dd'),'2020-09-17 10:44:27', '2020-09-17 10:44:28', 12.39);
insert into alltype_test values(31, 32, 33.1, 'hello34', 'hello world35', to_date('2020-02-14','yyyy-mm-dd'),'2020-09-17 10:44:27', '2020-09-17 10:44:28', 12.39);
insert into alltype_test values(41, 42, 43.1, 'hello44', 'hello world45', to_date('2020-02-14','yyyy-mm-dd'),'2020-09-17 10:44:27', '2020-09-17 10:44:28', 12.39);
set passthru reset;
drop nickname nk1;
create nickname nk1 for server1."J15USER1".alltype_test;
select * from nk1;
Connection method |
Oracle Instant Client
|
Oracle ODBC driver
|
Oracle JDBC driver |
Download driver | Yes | No | No |
Modify configuration file | Yes | No | No |
Support call procedure | Yes | No | No |
Support CLOB, BLOB insert and update | Yes | No | No |
Import to nickname | Yes | No | No |
Transparent DDL | Yes | No | No |
Was this topic helpful?
Document Information
Modified date:
17 July 2024
UID
swg21978338