ITM Integration Series - Making Connections the ODBC Way

by Oneil Richardson

In this blog I will explore the data source connections used by ITM when running on a Windows host and take you through the steps I recently used to troubleshoot a problem connection between the warehouse proxy agent and a remote Tivoli Data Warehouse (TDW).

One distinction that is important is if you are using a 64-bit Windows machine, you may have two sets of DSN (Data Source Names) defined for both 64-bit and 32-bit applications. If the HD agent is installed as a 64-bit application, you will want to focus on the DSN settings found when opening the ODBC Connections settings tool from the following location...

C:\Windows\System32\odbcad32.exe (default location for 64-bit connections)

Also, ensure that you launch the ODBC tool as Administrator or an administrative user

The problem at hand was that although the remote database connection was successful via the DB2 CLI, and the same credentials were then entered in the ODBC settings for the 'ITM Warehouse' entry for use by ITM, when the HD agent was started it was apparent that the connection was not being made. The following error was seen in the HD agent logs...

[From MyWPAHost_HD_XXXXXX-1.log...]

10:37:18-{14E0}khdxodbc.cpp,996,"connectDatasource") Attempt to connect with ODBC Datasource "ITM Warehouse" User "ITMUser" failed!
(Tuesday, January 10, 2017, 10:37:18-{14E0}khdxodbc.cpp,998,"connectDatasource") ODBC get Connection failed(SQLConnect)
(Tuesday, January 10, 2017, 10:37:18-{14E0}khdxbase.cpp,337,"setError")
+5874AB4E.0020  Error Type= CTX_ODBCError
+5874AB4E.0020  Severity= CTX_Critical
+5874AB4E.0020  Native Error Code = 0
+5874AB4E.0020  SQL State= IM014
+5874AB4E.0020  Reason Code= 0
+5874AB4E.0020  executing: connectDataSource
(Tuesday, January 10, 2017, 10:37:18-{14E0}khdxbase.cpp,340,"setError")
+5874AB4E.0021  ERROR MESSAGE: "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application"

To resolve the issue, it was necessary to rebuild the ODBC connection. Modifying the DSN settings using the 'Configure' option in the GUI made no difference to the error so we had to remove the entry from the GUI and also directly from the DB2 ini file - db2cli.ini - before recreating it again using the GUI

These are the steps that were used to rebuild the ODBC DSN connection and to successfully configure the warehouse proxy agent so it could make the connection to the remote Tivoli Data Warehouse...

(1) Uninstall the HD agent using the Add/Remove Programs (see p.924 of the Installation and Setup Guide for full steps on how to do this if needed)

(2) After the agent has been removed, please ensure you save the details for all your ODBC connections and then do the following...
- Open the ODBC connections by launching the following executable - C:\Windows\system32\odbcad32.exe
- In the ODBC Connections panel please remove the entry for 'ITM Warehouse' by selecting it and then hitting the button to 'Remove'
- Hit OK to save your changes
 image
(3) Repeat the step by opening the 32-bit ODBC connection setting GUI and ensure you have no entries in here for the ITM Warehouse entry

- To launch the 32-bit ODBC Data Sources GUI locate the following executable - C:\Windows\SysWOW64\odbcad32.exe
(If you have upgraded the HD agent from a 32-bit to a 64-bit application you may have remnants here that need to be cleaned up) 

(4) Locate the db2cli.ini file (by default this will be located in C:\ProgramData\IBM\DB2\DB2COPY1\cfg\...) and remove ONLY the ITM Warehouse stanza entry from the file

[ITM Warehouse]
DBALIAS=Warehous
PWD=itmuser
UID=ITMUser
DESCRIPTION=Warehouse Database

- Save the file

(5) Once the ITM Warehouse entry has been cleared, we can recreate the 'ITM Warehouse' entry again using the 64-bit odbcad32.exe file found under
C:\Windows\System32\... to launch the ODBC Data Sources GUI
- enter the username and password details
- confirm the connection is successful
 
(6) Finally install and reconfigure the HD agent to utilize the new connection, and confirm in the agent logs that the ODBC connection is now successful.

If you find a similar problem with the TEPS ODBC connection not working as expected, e.g. if the TEPS password is changed, you can use these steps to rebuild the TEPS DSN connection also.

Recent Stories
Making Your Life Easier: Installing TCR 3.1 with Non-Root Installing JazzSM & Websphere

Making your life easier: Installing TCR 3.1 with Non-Root Creating Configuring Content Store

Making Your Life Easier: Installing TCR 3.1 with Non-Root DB2 Installation