Question & Answer
Question
Demonstrates how to configure DataDirect ODBC driver bundled with Infosphere Federation server for connectivity to DB2 and MS SQL.
Answer
Please see related links "Installing and configuring the IBM® branded DataDirect® ODBC drivers with InfoSphere® Federation Server Version 9.x and 10.x" for general directions.
(A) Configuration
We assume DataDirect driver has been installed to /opt/branded_odbc and installed on Linux.
1) Make a copy of system odbc.ini to db2inst1's home directory
cp /opt/branded_odbc/IBM_Tools/odbc.ini .
2) Modify .profile, or if your environment uses a startup script for the shell modify the corresponding (example .kshrc, .bashrc). Depending on your operating system, uncomment the correct line. This example assumes it is Linux so we uncomment export LD_LIBRARY_PATH
# Linux, HP-UX use:
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/branded_odbc/lib
# AIX use:
# export LIBPATH=$LIBPATH:/opt/branded_odbc/lib
# HP-UX use:
# export SHLIB_PATH=$SHLIB_PATH:/opt/branded_odbc/lib
export ODBCINI=/home/db2inst1/odbc.ini
# Uncomment if Federating to MS SQL Server
# export DJX_ODBC_LIBRARY_PATH=/opt/branded_odbc/lib
2) Edit odbc.ini.
There is a sample /opt/branded_odbc/IBM_Tools/odbc.ini file containing example configurations to additional data sources like AS400 (i-Series), Informix, Oracle, etc...).
For DB2
[SAMPLE] = Database name
IpAddress = IP address of DB2 database server
TcpPort = DB2 port #
odbc.ini
[SAMPLE]
QEWSD=2457360
Driver=/opt/branded_odbc/lib/VMdb200.so
Description=DataDirect 7.1 DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Collection=
Database=SAMPLE
DynamicSections=200
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=localhost
IsolationLevel=CURSOR_STABILITY
Location=
LogonID=
password=
Package=DB2 package name
PackageOwner=
TcpPort=50100
WithHold=1
[ODBC]
IANAAppCodePage=4
InstallDir=/opt/branded_odbc
Trace=0
TraceDll=/opt/branded_odbc/lib/VMtrc00.so
TraceFile=odbctrace.out
UseCursorLib=0
For MS SQL Server
sqlserv.ibm.com = hostname of MS SQL
1433 = default port used by MS SQL
odbc.ini
[SQLServer Wire Protocol]
Driver=/opt/branded_odbc/lib/VMmsss00.so
Description=DataDirect 7.1 SQL Server Wire Protocol
Address=sqlserv.ibm.com,1433
AnsiNPW=Yes
Database=db
LogonID=UID
password=PWD
QuotedId=No
[ODBC]
IANAAppCodePage=4
InstallDir=/opt/branded_odbc
Trace=0
TraceDll=/opt/branded_odbc/lib/VMtrc00.so
TraceFile=odbctrace.out
UseCursorLib=0
(B) Test connectivity via "example" application
./example DataDirect Technologies, Inc. ODBC Example Application.
Enter the data source name : SAMPLE
Enter the user name : db2inst1
Enter the password : 123456
SQLSTATE = 01000
NATIVE ERROR = 8369
MSG = [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver]Packages successfully bound and created with 200 Dynamic Sections.
Enter SQL statements (Press ENTER to QUIT)
SQL> select count(*) from syscat.tables
Warning:
[IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver]The DataDirect product you are attempting to access has been provided to you by IBM Corporation for exclusive use with IBM InfoSphere Information Server. You are not licensed to use this product with any application other than IBM InfoSphere Information Server. If you would like to purchase this product for some other use, please call 800-876-3101 or visit DataDirect at www.datadirect.com
429
Enter SQL statements (Press ENTER to QUIT)
SQL> select count(*) from syscat.packages
341
When the "example" application can successfully access the remote database, we know that ODBC and Datadirect is working correctly. The next step is to create a Federated data source (CREATE WRAPPER, CREATE SERVER, CREATE NICKNAME) to the remote data source via Configuring data sources.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21972497