SQL Server 2005/2008/2012: How to connect to a IBM UDB DB2 data source and query it?

You will need the following to install the drivers and connect to the IBM UDB DB2 data source:

1. Hostname of the DB2 server.
2. Port number where the DB2 instance is listening. You can ask for the SVCENAME Database manager parameter for the particular instance. Sometimes you might have to read the /etc/services file to get this SVCENAME.
3. DB2 driver or client packages. You can download the specific client from the following location. We recommend that you install any of the Runtime clients.

http://www-01.ibm.com/support/docview.wss?uid=swg27016878

Once you install the IBM DB2 runtime client, you will have to catalog a database in the ODBC Data sources. In some cases you will have to catalog a database in both the 32 bit and the 64 bit ODBC data sources. Go to Control Panel > Adminstrative Tools > ODBC Data sources.

Then go to System DSNs tab and click Add.

Figure 1 : Here you can select among the installed drivers, you should pick ‘IBM DB2 ODBC DRIVER – DB2COPY1’. This could either be 32 bit or 64 bit. Based on my experience you will have less problems with 32 bit driver.

Figure 2 : In the next window Enter the DB name that you want to connect to and click Add.

Figure 3 : Here you can go to the TCP/IP settings and enter the DB name, alias, hostname and port number. If you go to the tab for ‘Data source’ you can save the user name and password. But I wouldn’t recommend that. Then click OK and save the data source.

Now we can use this data source to create a linked server in SQL Server.  The following command should help you create the linked server in SQL Server management studio.

EXEC master.dbo.sp_addlinkedserver @server = N'TESTSERVER', @srvproduct=N'IBM OLE DB Provider for DB2 - DB2COPY1', @provider=N'IBMDADB2.DB2COPY1', @datasrc=N'SAMPLE',@provstr=N'Initial Catalog=db2inst1;Data Source=SAMPLE;HostCCSID=28591;Network Address=192.168.1.150;Network Port=50500;'

Following are some of the other options that you can specify in the linked server.

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TESTSERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'db2inst1',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'collation compatible', @optvalue=N'false'
GO\
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTSERVER', @optname=N'remote proc transaction promotion', @optvalue=N'true'

Please note that in the above commands I have restricted the password, make sure you provide a strong password.

You can SELECT the data from the IBM DB2 source using the OPENQUERY statement like the following

SELECT from OPENQUERY(TESTSERVER, 'select * from syscat.tables')

Some things to remember:

1. There is 64 bit version of IMPORT/EXPORT Wizard. So if you have created a 64 bit Data source then use that. The one that opens up when you right click on the database and go to Tasks > Import Data is a 32 bit IMPORT/EXPORT Wizard.
2. BIDS and other SSIS data tools can also be 32 bit. So, if you are going to use that, then create a 32 bit data source using a 32 bit runtime client.
3. I recommend ising the IBM DB2 run time client over the Microsoft OLEDB DB2 driver.