Technical Blog Post
Abstract
50 DB2 Nuggets #16 : Tech Tip - How are ODBC DSN's affected during Windows XP to Windows 7 migration?
Body
Hello!
Last week, I ran into a customer problem where the 32-bit ODBC User DSN test from 32-bit ODBC Data Source Administrator (C:\Windows\sysWow64\odbcad32.exe) for a remote database running on AIX was failing with below error with 32-bit DB2 Data Server Driver Package 9.7 FP0 on Windows 7 64-bit:
We tried to create a new User DSN from 32-bit ODBC Data Source Administrator, but we could not, since it failed with the same error.
On their Windows XP, machine they have the same setup, the DSN test connection works fine, only on Windows 7, these problems started happening.
We went ahead and launched cmd.exe,browsed to C:\Program Files (x86), IBM\IBM Data Server Driver\bin, ran db2level command it worked, db2cli registerdsn -list -allcopies -user did not work, it did not list any User DSN's even though they seem to exist (User DSN tab in the 32-bit ODBC administrator).
Explained to customer that based on my experience, these kind of setup routine errors happen if there is a corrupted DB2 install/non-working DB2copy was chosen, recommended approach was to re-install DS driver copy.
To uninstall DS driver copy, we were looking under Programs and Features in the Control Panel. noticed two copies of IBM Data Server Driver IBMDBCL1 for same version and FixPack.
We looked under C:\Program Files\IBM\IBM Data Server Driver to make sure, we found one here as well. The reason they had two DS driver copies under Program files and Program files (x86) both was:
Their network team migrated all the applications including DB2 from Windows XP to Windows 7 as part of OS migration, and it has resulted in these ODBC issues.
Explained to customer that we do not support this kind of migration for DB2, its not the right approach, would run into ODBC problems and similar since on Windows 32-bit only 32-bit DB2 can be installed, which will be installed by default under C:\Program Files, but on Windows 7, 32-bit DB2 copies will be installed under Program files (x86) and 64-bit DB2 under C:\Program files, it would break DB2.
Here we go, we found the culprit, this seems to be the source of the ODBC problems, the reason being:
DB2 does not support both 32-bit and 64-bit DB2 to co-exist on the same Windows 64-bit machine It can be all 32-bit DB2 or all 64-bit DB2, but not both. Its documented here:
Note: The instances can also belong to different DB2 copies on a computer that can be at different levels of the database manager. If you are running a 64-bit Windows system, you can install 32-bit DB2, or 64-bit DB2 but they cannot co-exist on the same machine.
Uninstalled both DSdriver copies from Control Panel, re-installed 32-bit DB2 Data Server Driver copy since the customer's applications were going to be just 32-bit.
Launched 32-bit ODBC Data Source Administrator located under C:\Windows\Syswow64\odbcad32.exe. Configured User DSN, tested the connection, it worked.
Thank you for reading!
UID
ibm11141576