How to set up a DB2 linked server on a 64-bit SQL Server 2005

Question

I need to create a linked server to a DB2 database on a mainframe. Has anyone done this successfully on a 64-bit version of SQL Server 2005? If so, which provider and settings were used.

It's important that the linked server work whether we are using a windows authenticated account to login to SQL Server or a SQL Server login. It's also important that both the 4-part name and OPENQUERY query methods are functional. We have one set up on a SQL Server 2000 machine that works well, but it uses a provider that's not available for 64-bit SS 2005.

Answer 1

What provider are you using for Sql 2000? I'm pretty sure MS has an x64 OLEDB driver for DB2 (part of Host Integration Server, but available as a separate download). IBM has x64 for .NET and ODBC, and possible OLEDB as well (though it's a PITA to find).

Once you get the linked server setup, I'm pretty sure all of your other requirements would be automatic....

Answer 2

We had this same issue with a production system late last year (sept 2007) and the official word from our Microsoft contact was that they had a 64 bit oledb driver to connect to ASI/DB2 but it was in BETA at the time.

Not sure when it will be out of beta but that was the news as of last year.

We decided to move the production server onto a 32 bit machine since we were not comfortable using beta drivers on production systems.

I know this doesn't answer your question but it hopefully gives you some insight

Answer 3

@Mark - We used the Microsoft OLE DB Provider for ODBC Drivers on the 32-bit SQL 2000 box. I believe that provider isn't available for 64-bit.

We are using the the Microsoft OLE DB provider for DB2 (DB2OLEDB) provider now and it's semi-functional. It seems to really struggle with performance when certain date/time column types are involved. We also have some problems when logged in with a SQL Server authenticated account. However, we only have a few jobs that use this linked server and it's working reliably for those few jobs. I'd still be interested in hearing from anyone who has this type of link server set up with all normal linked server functions working reliably.

Answer 4

From the Sql 2005 February 2007 Feature Pack:

The Microsoft OLE DB Provider for DB2 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2005 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with Integration Services, Analysis Services, Replication, Reporting Services, and Distributed Query Processor. Run the self-extracting download package to create an installation folder. The single setup program will install the provider and tools on x86, x64, and IA64 computers.