How to add an IBM DB2 server to SQL Server's L

Question

From .Net, I'm able to connect to the DB2 database:

1. First I include a reference to "IBM.Data.DB2.iSeries"
   

2. Then I create a new IBM.Data.DB2.iSeries.iDB2Connection. The connection string is

   DataSource=ChaDb2Server;UserID=MyUsername;Password=MyPassword;
   

3. Then I create an IBM.Data.DB2.iSeries.iDB2Command, and so on.

Now I'm trying to get my SQL Server 2005 to access the same data directly. In SQL Server Management Studio, I right-click on Linked Servers, and select "New Linked Server..."

Linked Server:   ChaDb2Server
Provider:        IBM OLE DB Provider for DB2
Product Name:    ???
Data Source:     ChaDb2Server
Provider String: DataSource=ChaDb2Server;UserID=MyUsername;Password=MyPassword;
Location:        ???

I can leave Location blank, but Product Name can't be empty and I don't know what to fill in here.

On the Security tab, I select "Be made using this security context" and I repeat the UserID and password.

The linked server gets created, but when I try to expand Catalogs / default / Tables, I get an error message:

OLE DB provider 'IBMDADB2.DB2COPY1' reported an error. Authentication failed. (Microsoft SQL Server, Error: 7399)

I have no idea where the name IBMDADB2.DB2COPY1 came from.

Also, when I try to select data:

Select * from ChaDB2Server.ChaDb2Server.Information_Schema.Tables

I get a similar error:

Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'IBMDADB2.DB2COPY1' reported an error. Authentication failed.

Obviously there's something missing in the way that I link the servers. Anybody know how to do this?

Answer 1

I don't know why, but I've never had any luck getting linked servers to work when set up through the GUI. However, I have had success doing it with sp_addlinkedserver. See here for IBM's instructions on how to set this up: https://www-304.ibm.com/support/docview.wss?uid=swg21394344

Answer 2

The way i setup DB2 LUW server as a linked server in SQL is the following :

1- I installed the appropriate DB2 LUW client on mySQL Server

--> This will installed the missing driver for DB2 (IBMADB2.DB2COPY1)

2- I create the linked server as follow :

replace the <...text...>  with your values

/****** Object:  LinkedServer [<DB2_DB_Name>]    Script Date: 09/08/2014 09:46:02 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'<DB2_DB_Name>', @srvproduct=N'IBMADB2.DB2COPY1', @provider=N'IBMDADB2.DB2COPY1', @provstr=N'Database=<DB2_DB_Name>;Hostname=<DB2 Server running the DB2 database>;Port=5900'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'<DB2_DB_Name>',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Answer 3

I haven't had luck using the IBM provider. I hear it's tricky. However, I have successfully used the Microsoft OLE DB Provider for ODBC Drivers.

My configuration points to a DSN and works well. The only 3 fields I have filled out is the provider (Microsoft OLE DB Provider for ODBC Drivers), product name (value='not used') and Data Source (name of the DSN on the SQL server).

If you don't mind using the ODBC provider, it will work for you.