如何将IBM DB2服务器添加到SQL Server的链接服务器 [英] How to add an IBM DB2 server to SQL Server's Linked Server

问题描述

从.Net，我能够连接到DB2数据库:

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

1. 首先，我包括对"IBM.Data.DB2.iSeries"的引用.
   

2. 然后创建一个新的IBM.Data.DB2.iSeries.iDB2Connection.连接字符串是

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;

然后我创建一个IBM.Data.DB2.iSeries.iDB2Command，依此类推.

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

现在，我正在尝试使我的SQL Server 2005直接访问相同的数据.在SQL Server Management Studio中，我右键单击链接服务器"，然后选择新建链接服务器..."

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.

在安全性"选项卡上，选择使用此安全性上下文创建"，然后重复用户ID和密码.

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

已创建链接服务器，但是当我尝试扩展Catalogs/default/Tables时，出现错误消息:

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)

我不知道IBMDADB2.DB2COPY1这个名字的来源.

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

我遇到类似的错误:

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?

推荐答案

我将DB2 LUW服务器设置为SQL中的链接服务器的方式如下:

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

1-我在mySQL服务器上安装了相应的DB2 LUW客户端

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

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

2-我按如下方式创建链接服务器:

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

这篇关于如何将IBM DB2服务器添加到SQL Server的链接服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！