Unable to get CF 9 Admin to Connect to IBM/DB2 Server

[Guest]

Am trying to set up a new Data Source in ColdFusion 9.0.1, CF Admin, to connect to my company's IBM/DB2 database/server.  The IBM server itself is 64-bit, and I'm using my home computer with Windows 7 Pro 64-bit.

We have to use the IBM/DB2 Client version 9.5 FixPak 4.  It comes in 32-bit and 64-bit versions.  I downloaded both, and installed 64-bit driver, and it's not working.

In Control Panel/ODBC Data Sources, System DSN tab, it shows the IBM DB2 ODBC Driver.  I've configured it with correct hostname, DB name, UserID and Password, etc., and the connection tested fine from there.  When I click TEST CONNECTION, the response is "successful connection."

But when I go to CF Admin and add this as a Data Source, using ODBC Socket, it displays my "named" connection from Control Panel/ODBC.  But when I try to connect, I get the response:  "UNABLE TO VERIFY LOGON, and something about ARCHITECTURE MISMATCH."

Question:  Does CF 9 Admin support 64-bit ODBC drivers? Am wondering if that's the reason Windows ODBC will make the connection, but CF Admin won't.  If so, I could uninstall the 64-bit driver, and install the 32-bit driver and try again.  Since few people at work know ColdFusion, I don't have much support there.

I successfully installed the 32-bit driver on my Office Windows 7 PC, but it has Windows 7 32-bit.  I've also upgraded the drivers on several Windows XP SP2 computers, using the same IBM DB2 Client, but 32-bit version.  All those work fine in CF Admin.

Am hoping someone has tried this, and has some advice on how to connect to an IBM/DB2 database, with CF 9.0.1.  And if CF9 Admin supports 64-bit drivers for connecting to other data sources?  FWIW, all my other CF Admin data sources (to various company Oracle and other servers) are working fine in CF Admin, but I'm using the default ORACLE drivers that came with CF9.  I can't use CF9's built-in DB2 drivers, they aren't compatible.  You must install the IBM DB2 client, in order to connet to the IBM database/server.

Thanks for any help/advice.

Gary

[Adam Cameron.]

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.

--
Adam

[Guest]

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.

See: http://msdn.microsoft.com/en-us/library/ms712362(v=vs.85).aspx  or

http://answers.microsoft.com/en-us/windows/forum/windows_7-networking/odbc-architecture-mismatch/57c...  or

http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/1c63e3ae-e001-4066-9eac-ad9...  or

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,

Gary

[Dave Watts]

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

http://www.figleaf.com/

http://training.figleaf.com/

[Guest]

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)

or

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,

Gary

[Guest]

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.

Gary

[Dave Watts]

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

http://www.figleaf.com/

http://training.figleaf.com/

[Guest · Accepted solution]

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. 

Gary