Skip to main content
A
Anonymous
May 6, 2011
Answered

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

  • May 6, 2011
  • 1 reply
  • 3466 views

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

This topic has been closed for replies.
Correct answer

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/


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

1 reply

A
Adam Cameron.
Inspiring
May 6, 2011

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

A
Anonymous
May 7, 2011

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/57c970ee-02c4-4eb6-90fe-051cc588fe4d  or

http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/1c63e3ae-e001-4066-9eac-ad9162116603  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

D
Community Expert
Dave WattsCommunity Expert
Community Expert
May 7, 2011

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/

Dave Watts, Eidolon LLC
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices