Ah, ODBC, it's been a while

Now I started using ODBC back in the early 90s, when I was helping customers connect their Windows for Workgroups systems to the AS/400 database via PC Support/400.

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> 

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

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 ....