如何将 IBM DB2 服务器添加到 SQL Server 的链接服务器

2022-01-14 00:00:00 db2 sql-server linked-server

通过 .Net，我可以连接到 DB2 数据库:

  1. 首先我引用IBM.Data.DB2.iSeries"

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

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

  3. 然后我创建一个 IBM.Data.DB2.iSeries.iDB2Command，等等.

现在我正试图让我的 SQL Server 2005 直接访问相同的数据.在 SQL Server Management Studio 中，我右键单击 Linked Servers，然后选择New Linked Server..."

链接服务器:ChaDb2Server提供者:IBM OLE DB Provider for DB2产品名称:    ???数据来源:ChaDb2Server提供者字符串:DataSource=ChaDb2Server;UserID=MyUsername;Password=MyPassword;地点:        ???

我可以将 Location 留空，但 Product Name 不能为空，我不知道在此处填写什么.

在安全"选项卡上，选择使用此安全上下文"并重复用户 ID 和密码.

已创建链接服务器，但是当我尝试展开目录/默认/表时，我收到一条错误消息:

OLE DB 提供程序 'IBMDADB2.DB2COPY1' 报告错误.身份验证失败.(Microsoft SQL Server，错误:7399)

我不知道 IBMDADB2.DB2COPY1 这个名字是从哪里来的.

另外，当我尝试选择数据时:

从 ChaDB2Server.ChaDb2Server.Information_Schema.Tables 中选择 *

我收到类似的错误:

Msg 7399, Level 16, State 1, Line 1OLE DB 提供程序IBMDADB2.DB2COPY1"报告了一个错误.身份验证失败.

显然，我链接服务器的方式缺少一些东西.有人知道怎么做吗?

解决方案

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

1- 我在 mySQL Server

上安装了适当的 DB2 LUW 客户端

-->这将安装缺少的 DB2 驱动程序 (IBMADB2.DB2COPY1)

2- 我创建的链接服务器如下:

替换 <...text...>用你的价值观/****** 对象:LinkedServer [] 脚本日期: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>;主机名=<运行 DB2 数据库的 DB2 服务器>;端口=5900'/* 出于安全原因，链接服务器远程登录密码更改为 ######## */EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'<DB2_DB_Name>',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'collat​​ion compatible', @optvalue=N'false'去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'数据访问', @optvalue=N'true'去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'dist', @optvalue=N'false'去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'pub', @optvalue=N'false'去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'rpc', @optvalue=N'false'去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'rpc out', @optvalue=N'false'去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'sub', @optvalue=N'false'去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'连接超时', @optvalue=N'0'去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'collat​​ion name', @optvalue=null去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'惰性模式验证', @optvalue=N'false'去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'查询超时', @optvalue=N'0'去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'使用远程排序规则', @optvalue=N'true'去EXEC master.dbo.sp_serveroption @server=N'', @optname=N'remote proc transaction Promotion', @optvalue=N'true'去

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?

解决方案

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

相关文章