How-to: ODBC connection to DB2 instance (e.g. Mitel CSM)

I’m sure this is a very niche article. Which means if you’ve arrived here, you’ve almost certainly been as frustrated as I have with the documentation for DB2 ODBC connections.

Background: I’m trying to connect to a DB2 instance, running on a Windows machine. I imagine that this procedure will work just as well for instances running on other architectures. And I’m trying to connect from another Windows machine, to pass data into a Microsoft SQL-powered data warehouse.

You will need the “IBM Data Server Driver for ODBC and CLI (Windows/x86-32 32 bit) V10.5 Fix Pack 8“. If the link doesn’t work any more, go to IBM Fix Central and search for “Windows Data Server Driver ODBC 10.5”. Possibly other versions will work, but this is the one I found most reliable.

The process is as follows:

1. Copy the entire extracted folder to the root of a data drive (e.g. to D:\DB2, E:\DB2 as the case may be).
2. Add the bin folder to the computer’s PATH environment variable (DB2\clidriver\bin).
   
3. Launch an elevated command prompt.
4. Navigate to the DB2 bin folder. E.g.:
   e:
cd e:\DB2\clidriver\bin
5. Install the ODBC driver:
   db2oreg1.exe -i
6. On Windows Server 2012 R2, also run:
   db2oreg1 -setup
7. The driver will now appear in the 32-bit ODBC driver list:
   

To connect:

1. Launch the 32-bit ODBC data source administration applet.
2. On the User DSN or System DSN tab, click Add.
   
3. Select the ODBC driver and click Finish.
   
4. Name the data source (e.g. “CSM”, in my case) and then click “Add”, next to the Database alias dropdown.
   
5. Enter User ID and password.
   
6. Check the “Save password” box. Note the warning and click OK.
   
7. Switch to the Advanced Settings tab.
   
8. Use the “Add” button, to enter the following values.

   Hostname: [host DNS name or IP address]
   Port:     50000
   Protocol: TCP/IP
   Database: [DB name, e.g. CTI_DATA]

9. Review the settings and click OK:
   
10. To test the connection, first click the “Configure” button.
    
11. The credentials are stored in the ini file, so you do not need to enter them here. Simply click “Connect”.
    
12. You should see a success message.
    

If you’re looking for a free, Windows-based ODBC interrogation program, there are a few out there. All the ones I tried had quirks in their interfaces. I’ve had most success with ODBC query tool though. Here it is, running under Windows 10:

If that doesn’t work for you, you can try the almost identically named ODBC QueryTool.