Is the issue you detail in your last para baout not being able to use CF's DataDairect DB2 drivers a vagary of those drivers, or is it JDBC drivers connecting to your DB2 DB in general? What's the incompatibility? Could you use a different JDBC driver?
I dunno anything about connecting to DB2, but I would really really shy away from connecting via JDBC to ODBC to the DB if there were any other possibilities.
Not much help, sorry.
Thanks for trying to answer, much appreciated. First, the only way we (employees) can connect to our company's IBM DB2 datamart is via IBM's DB2 Connect drivers. I've used them for years with WinXP, Windows 2000 and CF 5 and 7. The CF 5, 7 and 9 built-in drivers have never worked. Something to do with the architechture of the company's DB2 servers, and possibly security.
Second, I found out this is a Windows problem, not a CF Admin problem. With Windows 7 64-bit, you have to use IBM's 64-bit drivers. As mentioned, I successfully installed the same IBM 32-bit drivers on a Windows 7 Enterprise 32-bit PC in the company's IT lab at work.
However, this is my first time trying to install them and connect to the DB2 servers on a 64-bit platform. Since we don't use 64-bit workstations at the office, no help there. By the way, the error message from CF Admin is: "The specified DSN contains an architecture mismatch between the Driver and Application." I found several Microsoft blogs that discuss using ODBC connections on 64-bit machines, but still haven't figured out the solution.
If you Google the above highlighted error message, you get tons of sites, with people having same problem.
I was hoping to find someone with a 64-bit Win 7 or Windows 2005/2008 that has successfully configured the DB2 drivers in CF Admin, or successfully enabled ANY 64-bit ODBC connection using external 64-bit drivers to a DB2 server, who might offer a suggestion.
The above 3 sites offer different solutions, and what I'm concerned with is messing up my SQL Server connection (have SQL Server 2008 R2 installed on this Win 7 64-bit workstation), or messing up my 3 Oracle connections. It took awhile to get those configured and working.
If anyone has experience with this, and/or any suggestions, they are much appreciated. Thank you,
The bad news is that I have no experience with DB2 ODBC drivers, 32-bit or 64-bit. The good news is that you don't have to worry about whether changes to ODBC will affect your other database connections - they won't. Connections by CF to SQL Server and Oracle use JDBC only by default.
Dave Watts, CTO, Fig Leaf Software
Thanks for the reply. After much research, does CF Admin (and the JDBC behind it) support 64-bit ODBC drivers? Can anyone confirm this? I installed the 64-bit version of CF9 Enterprise Trial/Developer 2 months ago. Because I successfully installed the IBM DB2 32-bit drivers on work computers, only different was that it was on Windows 7 32-bit PCs. I'm wondering if CF9 64-bit, including the JDBC behind it, only supports 32-bit. Because within the ODBC settings, I can now successfully connect to the DB2 server with my UserID and password. It's only when using CF Admin, that I get the "architechture mismatch" error.
When you download the IBM drivers, you can choose between 32 or 64 bit. I chose 64-bit and installed those. Even though 2 sets of drivers (one 32 and one 64-bit) are shown in the DB2 folders created on the C: drive, the registry shows the 64-bit drivers are installed.
Microsoft's web site says Windows 7 64-bit comes with 2 ways to access/configure ODBC drivers:
1. The 32 bit (default) c:\windows\system32\odbcad32.exe , (the one I used to add my IBM ODBC DSN)
2. The 64-bit c:\windows\sysWOW64\odbcad32.exe (which you run manually, I haven't done this)
With my Window 7 64-bit, when you configure ODBC DSNs, the default program that runs is: c:\windows\system32\odbcad32.exe, the 32-bit version.
To install/configure 64-bit drivers in Windows 7 ODBC, I think you need to run: c:\windows\sysWOW64\odbcad32.exe
So what I think I've got are 64-bit drivers, installed in the 32-bit section of Windows 7 ODBC settings. So either that, or CF 9 inability to support 64-bit drivers is causing the "mismatch" error. Wish I knew which one.
Microsoft's web site also says: To manage a data source that connects to a 32-bit driver under 64-bit platform, use c:\windows\sysWOW64\odbcad32.exe. To manage a data source that connects to a 64-bit driver, use c:\windows\system32\odbcad32.exe
There are only 2 things I can think of to try:
1. Remove the current IBM ODBC System DSN, and re-install using c:\windows\sysWOW64\odbcad32.exe.
If that doesn't solve the problem, then
2. Uinstall program for the IBM 64-bit drivers, and install the 32-bit drivers. Then, run: c:\windows\sysWOW64\odbcad32.exe . This is supposed to install the 32-bit drivers in the sysWOW64 directory. But I read that if you have CF 9 64-bit, it won't show 32-bit drivers in the CF Admin ODBC-Socket drop-down. Seems others have removed CF 9 64-bit, and re-installed CF9 32-bit. Would like to avoid that if possible.
If neither of these work, I'm not sure what else to try. I've got to get this connection working, because a number of my CF programs need access to this data source. And since transitioning 2 months ago from WinXP to Win 7 64-bit, these CF programs won't run.
Hopefully, one of these 2 efforts will work. I was hoping someone had some experience with installing 64-bit ODBC drivers in CF9 Admin, or whether it even supports it. Thank you,
FYI, I tried loading the 32-bit drivers using c:\windows\sysWOW64\odbcad32.exe and it didn't work. In the Win 7's ODBC Admin, I could connect to the IBM server, but when I went to CF Admin to add the System DSN, it didn't show up in the drop-down list.
I've read on this forum where CF 9 64-bit will only display 64-bit configured ODBC drivers. So uninstalling IBM's 64-bit drivers, and installing the 32-bit won't make any difference.
I read where others removed CF 9 64-bit, and installed CF 9 32-bit. Just want to make sure that's possible. Can you install CF 9 32-bit, on Windows 7 Pro 64-bit? I think you can, just want to confirm. That seems to be the only solution, until Adobe comes out with some fix for this issue. Thank you.
I have no experience working with ODBC in 64-bit systems, as most databases support JDBC directly.
I would try the two items in your list before going with a 32-bit version of CF, but if neither of them work, you can certainly use a 32-bit version of CF on a 64-bit version of Windows. If you're hooking it to IIS, you'll need to configure the IIS application pool to be 32-bit instead of 64-bit before doing so.
Dave Watts, CTO, Fig Leaf Software
Finally got the IBM DB2/JDBC drivers installed, configured and working on my Win7 64-bit PC. Should be the same set up on our Windows Server 2008 R2 production server at work, so am all set. Still using CF 9.0.1 64-bit, thankfully. This is the 2nd JDBC connection I've been able to configure successfully (using "OTHER" as the DSN), including Teradata JDBC.
When you download and install the IBM DB2 9.5 drivers from IBM, you'll find the 2 JDBC drivers you'll need, including a class 4 JDBC driver, on your hard drive (in folder: c:\Program Files\IBM\SQLLIB\java ). The DB2 drivers that come with CF9 are for mainframe DB2 and won't work in server-based DB2 environments. Although we have 4 IBM 3090 mainframes, I needed this connection to go to our 30-server based DB2 Data Warehouse. After 4 months of trying, finally figured it out and it works great. And just as fast, if not faster, than the 32-bit ODBC connection we've been using on our older, 32-bit servers.
What I dont' understand is that, even after calling Adobe tech support, they were no help. They wanted $250 for a "pay-per-incident" call, and there was probably no guarantee they would have the answer I needed. Below is the answer I figured out myself today. Hope it helps others, and saves the cost of an expensive phone call (or hours of Googl'ing).
Here is the procedure, if this helps anyone else. Or if you'd like to get rid of your 32-bit DB2 ODBC-Socket connection, and try the class 4 JDBC drivers, which will work on 32 or 64 bit machines.
1. Copy these 2 files to your root CF bin folder (e.g., C:\ColdFusion9\lib ) db2jcc4.jar and db2jcc_license_cu.jar
2. Add db2jcc4.jar and db2jcc_license_cu.jar to Java and JVM Paths (re-start CF Application Server)
3. JDBC URL: jdbc:db2://MyServerName:MyPort/MyDBName
4. Driver Class: com.ibm.db2.jcc.DB2Driver
5. Enter your UserID and PW and SUBMIT. No connect string is required, and no other changes needed in Advance Settings.
When I got the green letters that said "connected successfully" I was thrilled. Then I ran a few queries to test, and they all ran fine, and fast.
As mentioned, been trying for months to find out this procedure, as configuring JDBC drivers manually is something new to me. It's not hard, but if you've never done it, you don't really know, do you? Anyway, I hope this helps someone else using a 64-bit PC/server.