Set up unixODBC to connect DB2 database on Oracle database
A. Installation driver:
1. Unzipping and extracting
unixodbc:
Download from: http://www.unixodbc.org/download.html
gzip -d unixODBC-2.3.2.tar.gz
tar xf unixODBC-2.3.2.tar
DB2 client:
Download from: http://www-01.ibm.com/support/docview.wss?uid=swg24034634
gzip -d v9.7fp8_hpipf64_odbc_cli.tar.gz
tar xf v9.7fp8_hpipf64_odbc_cli.tar
2. Change Directory
cd unixODBC-2.3.2
3. Set PATH and Compiler flags
PATH=$PATH:/usr/ccs/bin;export PATH
PATH=$PATH:/usr/sbin;export PATH
CXXFLAGS=+DD64;export CPPFLAGS
CFLAGS=+DD64;export CFLAGS
4. Configure, compliing and installing
./configure --prefix=/u01/app/oraodbc/unixODBC-2.3.2 --enable-gui=no --enable-drivers=no
make
make install
5. Confgiure the db2 odbc client:
Location:
/u01/app/oraodbc/db2_odbc_client/odbc_cli/clidriver/cfg
File:
db2cli.ini
[DB2]
database=
protocol=TCPIP
hostname=
servicename=
BitData=1
BlockLobs=1
SQLCODEMAP=1
6. Configure the odbc ini:
Location:
/u01/app/oraodbc/unixODBC-2.3.2/etc/
File:
odbc.ini
Location:
/u01/app/oracle/product/11.2.0/dbhome_1
File: .odbc.ini
Add to
odbc.ini and
.odbc.ini
[ODBC Data Sources]
DB2=IBM DB2 ODBC DRIVER
[DB2]
Driver=/u01/app/oraodbc/db2_odbc_client/odbc_cli_o/odbc_cli/clidriver/lib/libdb2o.so
AUTHENTICATION=SERVER
SECURITY=SSL
CHARSET=AMERICAN_AMERICA.WE8MSWIN1252
7. Create ora file for Oracle HS (e.g. inidb2.ora)
Location:
/u01/app/oracle/product/11.2.0/dbhome_1/hs/admin
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = DB2
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME = /u01/app/oraodbc/unixODBC-2.3.2/lib/libodbc.so
HS_FDS_SQLLEN_INTERPRETATION=64
HS_FDS_TIMESTAMP_MAPPING="TIMESTAMP(6)"
HS_FDS_DATE_MAPPING=CHAR
# fixed triple size problem
HS_KEEP_REMOTE_COLUMN_SIZE=ALL
HS_FDS_SUPPORT_STATISTICS=FALSE
#HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252
HS_FDS_REMOTE_DB_CHARSET=AMERICAN_AMERICA.AL32UTF8
HS_NLS_NCHAR=AL16UTF16
#
# ODBC specific environment variables
#
set ODBCINI=/u01/app/oraodbc/unixODBC-2.3.2/etc/odbc.ini
# Environment variables required for the non-Oracle system
#
#set DB2INSTANCE=db2inst1
set DELIMIDENT=y
8. Update listener.ora and tnsnames.ora
Location:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
Add to
listener.ora
SID_LIST_LISTENER1521=
(SID_LIST=
(SID_DESC=
(SID_NAME=DB2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(ENVS=LD_LIBRARY_PATH64=/u01/app/oracle/product/11.2.0/dbhome_1/hs/lib:/u01/app/oraodbc/db2_odbc_client/odbc_cli_o/odbc_cli/clidriver/lib:/u01/app/oracle/product/11.2.0/dbhome_1/lib)
(PROGRAM=dg4odbc)
)
Add to
tsnames.ora
DG4ODBC_DB2_SSL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host001)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DB2)
)
(HS=OK)
)
9. Reload Listener
lsnrctl status
lsnrctl reload
10. Create DBLINK
create database link DBLINK_ODBC_DB2
connect to ibmdb2user identified by *******
using 'DG4ODBC_DB2_SSL';
11. Testing the connection:
select * from user.table01@DBLINK_ODBC_DB2;
B. SSL Configuration:
Reference:
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.sec.doc/doc/t0053518.html
1. Download GSKit Package from IBM
Note:
1. The GSKit is not free for download from IBM sites and it required use root to install.
Install GSKit 8 Scripts:
http://www-01.ibm.com/support/docview.wss?uid=swg21628888
Operation Steps : HP-UX - Install Instructions for GSKit 8
1. Extract the file
==> cd
==> gunzip -c DB2_SF_SSL_10_HP-UX_IA64.tar.gz | tar -xf –
2. The contents of the folder:
==> cd gskit/hpipf64/
./hpipf64:
gskcrypt64-8.0.14.14.hpux.ia64.tar.Z
gskssl64-8.0.14.14.hpux.ia64.tar.Z
Description of the files:
For TDS 64 bit Client components:
Global Security Kit V8.r.m.f for HPUX IA 64-bit Crypto - gskcrypt64-8.r.m.f.hpux.ia64.tar.Z
Global Security Kit V8.r.m.f for HPUX IA 64-bit - gskssl64-8.r.m.f.hpux.ia64.tar.Z
Note: r.m.f are release, maintenance and fix level of GSKit V8.
3. Now extract individual package files:
==> uncompress gsk*Z
==> tar -xf gskcrypt64*.tar
==> tar -xf gskssl64*.tar
==> ls -1d gsk*64 # the following output will be seen
gskcrypt64
gskssl64
4. Install the packages:
For 64 bit GSKit: (From the 64 subfolder where you can see the listing of gskssl64 and gskcrypt64)
==> cd 8*-TIV-GSKIT-HPUXIA64-*/64 # this full path becomes $PWD and is set in most shells.
==> ls -1d gsk*64
gskcrypt64
gskssl64
==> swinstall -s $PWD/gskcrypt64 gskcrypt64
==> swinstall -s $PWD/gskssl64 gskssl64
5. Verify the installed fileset levels:
==> swlist | egrep "gskcrypt|gskssl"
2. Upload ".arm" key to db server (E.g. "/u01/app/oraodbc/keystore")
3. Set PATH
PATH=$PATH:/bin:/lib;export PATH
echo $PATH
4. Create key database under keystore folder
Location:
/u01/app/oraodbc/keystore
client key database name:
ibmdb2db.kdb
gsk8capicmd_64 -keydb -create -db "keystore db name" -pw "keystore password" -stash
5. Import server certificate to the client key database
client key database name:
ibmdb2db.kdb
server certificate:
serverdb2.arm
gsk8capicmd_64 -cert -add -db "keystore db name" -pw "keystore password" -label "dbselfsigned" -file "" -format ascii -fips
– Note:
For example, the following command creates a key database called
mydbclient.kdb and a stash file called
mydbclient.sth -stash:
gsk8capicmd_64 -keydb -create -db "mydbclient.kdb" -pw "mydbclientpw0"
The -stash option creates a stash file at the same path as the key database, with a file extension of .sth. At connect time, GSKit uses the stash file to obtain the password to the key database.
–
6. update the db2 odbc client config (red color):
Location:
/u01/app/oraodbc/db2_odbc_client/odbc_cli/clidriver/cfg
File:
db2cli.ini
[DB2]
database=
protocol=TCPIP
hostname=
servicename=
security=ssl
ssl_client_keystoredb=/u01/app/oraodbc/keystore/ibmdb2db.kdb
ssl_client_keystash=/u01/app/oraodbc/keystore/ibmdb2db.sth
7. Reload Listener
lsnrctl status
lsnrctl reload
Enjoy =)
