How to query Oracle data source by using Db2 Federation through Oracle Instant Client?

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.

$ 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;