This was in the days before IBM rebranded the AS/400 database as DB2/400 ( mainly because few customers realised that their beloved midrange system included a database ).
I got back into ODBC in the context of WebSphere Application Server (WAS) on Unix, connecting (again) to DB2/400, in the early 00s, specifically although I cannot think why we weren't using the JT400 JDBC driver .....
So, this time around, I'm working with IBM Integration Bus 9, formerly known as WebSphere Message Broker in an earlier invocation, and creating a flow that connects to ... DB2 ( albeit not on IBM i ) via ... ODBC.
I'm going to be writing a LOT about IBM Integration Bus (IIB) in future posts, so I'm mainly going to focus upon the ODBC side of the equation.
Briefly, IIB is connecting via ODBC, rather than JDBC, to DB2.
Both IIB and DB2 are running on the same OS ( Red Hat Enterprise Linux on VMware on my Mac ), so I don't need to install any additional DB2 client software.
Note - for my client, we're running IIB on one AIX LPAR and DB2 on another, so we've installed the DB2 client onto the IIB LPAR to provide the connectivity.
With my chosen Red Hat Enterprise Linux 6.6 installation, the open-source unixODBC driver comes free: -
rpm -qa | grep -i odbc
unixODBC-2.2.14-14.el6.x86_64
I had previously created a sample database in my DB2 environment: -
su - db2inst1
db2sampl
which I then validated: -
db2 connect to sample as db2inst1 using passw0rd
db2 "select empno,firstnme,lastname from db2inst1.employee"
...
EMPNO FIRSTNME LASTNAME
------ ------------ ---------------
000010 CHRISTINE HAAS
000020 MICHAEL THOMPSON
000030 SALLY KWAN
000050 JOHN GEYER
000060 IRVING STERN
000070 EVA PULASKI
000090 EILEEN HENDERSON
000100 THEODORE SPENSER
000110 VINCENZO LUCCHESSI
000120 SEAN O'CONNELL
000130 DELORES QUINTANA
000140 HEATHER NICHOLLS
000150 BRUCE ADAMSON
000160 ELIZABETH PIANKA
...
I then tested the connectivity using the wonderful unixODBC isql test tool: -
isql SAMPLE db2inst1 passw0rd
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select empno,firstnme,lastname from db2inst1.employee
+-------+-------------+----------------+
| EMPNO | FIRSTNME | LASTNAME |
+-------+-------------+----------------+
| 000010| CHRISTINE | HAAS |
| 000020| MICHAEL | THOMPSON |
| 000030| SALLY | KWAN |
| 000050| JOHN | GEYER |
| 000060| IRVING | STERN |
| 000070| EVA | PULASKI |
| 000090| EILEEN | HENDERSON |
| 000100| THEODORE | SPENSER |
| 000110| VINCENZO | LUCCHESSI |
| 000120| SEAN | O'CONNELL |
| 000130| DELORES | QUINTANA |
...
| 200280| EILEEN | SCHWARTZ |
| 200310| MICHELLE | SPRINGER |
| 200330| HELENA | WONG |
| 200340| ROY | ALONZO |
+-------+-------------+----------------+
SQLRowCount returns -1
42 rows fetched
SQL>
| 200310| MICHELLE | SPRINGER |
| 200330| HELENA | WONG |
| 200340| ROY | ALONZO |
+-------+-------------+----------------+
SQLRowCount returns -1
42 rows fetched
SQL>
which proved the basic connectivity.
I then setup ODBC for the IIB integration, by creating a pair of files in /etc : -
/etc/odbc.ini
[ODBC Data Sources]
SAMPLE=IBM DB2 ODBC Driver
[SAMPLE]
DRIVER=/opt/ibm/db2/V10.5/lib64/libdb2.so
Description=IBM DB2 ODBC Database
Database=SAMPLE
[ODBC]
InstallDir=/opt/ibm/mqsi/9.0.0.2/ODBC64/V7.0/lib
UseCursorLib=0
IANAAppCodePage=106
UNICODE=UTF-8
/etc/odbcinst.ini
[ODBC]
Threading=2
Trace=1
TraceOptions=5
TraceFile=/tmp/odbc.trc
Threading=2
Trace=1
TraceOptions=5
TraceFile=/tmp/odbc.trc
Note - in this example, I've configured tracing, but that's not the default :-)
I'll cover the IIB aspect of this in the next post ....
No comments:
Post a Comment