适用于 DB2 的 SQL Server 分布式查询
SQL Server 中的分布式查询提供对多个数据源的分布式并发访问。 使用分布式查询处理器（DQP）可以创建异构查询，将 SQL Server 中的表与 DB2、主机文件系统、Oracle 或任何其他由 OLE DB 提供程序访问的数据源中的表连接起来。 可以使用 DQP 基于 DB2 表创建 SQL Server 视图，以便开发人员可以直接写入 SQL Server，并在其应用程序中集成基于 Windows 的数据和基于主机的数据。
下图显示了使用 Host Integration Server （HIS） 访问数据的 DQP 体系结构。
若要从 OLE DB 数据源访问数据，SQL Server 需要以下信息：
OLE DB 提供程序的名称
OLE DB 初始化字符串形式的连接信息
表名或 SQL 查询字符串
授权凭据
可以使用以下三种方法之一引用异类数据源：
临时名称
链接服务器名称
Pass-Through 查询
临时名称查询
临时名称用于针对没有定义为链接服务器的 OLE DB 数据源进行偶尔查询。 在 SQL Server 中， OPENROWSET 和 OPENDATASOURCE 函数提供用于从 OLE DB 数据源访问数据的连接信息。 默认情况下，不支持临时名称。 DisallowAdhocAccess 提供程序选项必须设置为 0，并且必须启用即席分布式查询高级配置选项。
以下代码片段展示了启用临时名称查询的语法。
-- Example of enabling Ad Hoc Name Query
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
以下代码片段显示用于创建即席查询的语法。
谨慎
此示例或指南引用敏感信息，例如连接字符串或用户名和密码。 切勿在代码中硬编码这些值，并确保使用最安全的身份验证来保护机密数据。 有关详细信息，请参阅以下文档：
-- Example of OPENROWSET Ad Hoc Name Query
SELECT * FROM OPENROWSET (
'DB2OLEDB',
'Provider=DB2OLEDB;User ID=HISDEMO;Password=HISDEMO;Initial Catalog=DSN1D037;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=SYS1;Network Port=446;Package Collection=MSPKG;Default Schema=NWIND;Units of Work=RUW;Default Qualifier=NWIND;DBMS Platform=DB2/z/OS;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Datetime As Date=False;AutoCommit=True;Authentication=Server;Persist Security Info=True;Cache Authentication=False;Connection Pooling=False;Derive Parameters=False;',
'SELECT * FROM NWIND.AREAS'
);
GO
-- Example of OPENDATASOURCE Ad Hoc Name Query
SELECT *
FROM OPENDATASOURCE(
'DB2OLEDB',
'Provider=DB2OLEDB;User ID=PLARSEN;Password=PLARSEN;Initial Catalog=DSN1D037;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=sys1;Network Port=446;Package Collection=MSPKG;Default Schema=NWIND;Units of Work=RUW;Default Qualifier=NWIND;DBMS Platform=DB2/z/OS;Defer Prepare=False;Persist Security Info=True;Connection Pooling=False;Derive Parameters=False'
).[DSN1D037].[NWIND].[AREAS]
OPENROWSET 和 OPENDATASOURCE 应仅用于引用不经常访问的 OLE DB 数据源。 对于将多次访问的任何数据源，请定义链接服务器。 OPENDATASOURCE 和 OPENROWSET 都不提供链接服务器定义的所有功能。 例如，OPENROWSET 和 OPENDATASOURCE 是宏，不支持将 Transact-SQL 变量作为参数提供。 即席名称查询不包括安全管理功能或查询目录信息的能力。 每次调用这些函数时，都必须提供所有连接信息，包括密码。
定义链接服务器
可以使用 Transact-SQL 语句或通过 SQL Server Management Studio 用户界面创建定义与 DB2 的连接的链接服务器名称。
Transact-SQL
以下代码片段演示用于删除、创建和指定链接服务器名称定义的身份验证凭据的 Transact-SQL 语法。
-- Example of dropping linked server by name
EXEC sp_dropserver
@server = 'DB2EXAMPLE',
@droplogins = 'droplogins';
GO
-- Example of adding linked server by name
EXEC sp_addlinkedserver
@server = 'DB2EXAMPLE',
@srvproduct = 'x''HIS',
@provider = 'DB2OLEDB',
@catalog = 'DSN1',
@provstr = ‘Provider=DB2OLEDB;User ID=HISDEMO;Password=HISDEMO;Initial Catalog=DSN1D037;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=SYS1;Network Port=446;Package Collection=MSPKG;Default Schema=NWIND;Units of Work=RUW;Default Qualifier=NWIND;DBMS Platform=DB2/z/OS;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Datetime As Date=False;AutoCommit=True;Authentication=Server;Persist Security Info=True;Cache Authentication=False;Connection Pooling=False;Derive Parameters=False;';
GO
-- Example of adding linked server login
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'DB2EXAMPLE',
@rmtuser = 'HISDEMO',
@rmtpassword = 'HISDEMO';
GO
-- Example of enabling pass-through queries
EXEC sp_serveroption
@server = 'DB2EXAMPLE',
@optname = 'RPC OUT',
@optvalue = 'TRUE' ;
GO
-- Example of listing linked servers and options
EXEC sp_linkedservers;
GO
EXEC sp_helpserver;
GO
-- Example of listing DB2 tables with restriction on schema name
-- List DB2 columns with restrictions on table name
EXEC sp_columns_ex
@table_server = 'DB2EXAMPLE',
@table_catalog = 'DSN1D037',
@table_schema = 'NWIND',
@table_name = 'ORDERS';
GO
-- Example of listing DB2 columns with restriction on schema and table names
EXEC sp_columns_ex
@table_server = 'DB2EXAMPLE',
@table_catalog = 'DSN1D037',
@table_schema = 'NWIND',
@table_name = 'ORDERS';
GO
-- Example of linked server query (SELECT)
SELECT * FROM DB2EXAMPLE.DSN1D037.NWIND.AREAS
GO
-- Example of linked server query (INSERT)
INSERT INTO DB2EXAMPLE.DSN1D037.NWIND.AREAS VALUES (99999, 'Everywhere', 999)
GO
-- Example of linked server query (SELECT with WHERE clause)
SELECT * FROM DB2EXAMPLE.DSN1D037.NWIND.AREAS WHERE REGIONID > 104
GO
-- Example of linked server query (UPDATE)
UPDATE DB2EXAMPLE.DSN1D037.NWIND.AREAS SET AREADESC = 'Nowhere' WHERE AREAID = 99999
GO
-- Example of linked server query (SELECT with WHERE clause)
SELECT * FROM DB2EXAMPLE.DSN1D037.NWIND.AREAS WHERE REGIONID > 104
GO
-- Example of linked server query (DELETE)
DELETE FROM DB2EXAMPLE.DSN1D037.NWIND.AREAS WHERE AREAID = 99999
GO
-- Example of linked server query (SELECT with WHERE clause)
SELECT * FROM DB2EXAMPLE.DSN1D037.NWIND.AREAS WHERE REGIONID > 104
GO
-- Example of linked server query (in a SQL Server VIEW)
DROP VIEW QP_CustomerOrders
GO
CREATE VIEW QP_CustomerOrders
AS
SELECT * FROM DB2EXAMPLE.DSN1D037.NWIND.ORDERS
GO
SELECT * FROM QP_CustomerOrders
GO
-- Create SQL Server Stored Procedure to wrap Linked Server Query (SELECT with parameter)
DROP PROCEDURE QP_SP_SelectAreaByAREAID
GO
CREATE PROCEDURE QP_SP_SelectAreaByAREAID
@MyArea integer
AS
SELECT * FROM DB2EXAMPLE.DSN1D037.NWIND.AREAS WHERE AREAID = @MyArea
GO
SP_HELP QP_SP_SelectAreaByAREAID
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[QP_SP_SelectAreaByAREAID]
@MyArea = 1581
SELECT 'Return Value' = @return_value
GO
-- Four-part linked server query (INSERT with DUW transaction)
--(Note: Requires updated Provider String (provstr) argument (Units of Work=DUW)
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
INSERT INTO DB2EXAMPLE.DSN1D037.NWIND.AREAS VALUES ('99999', 'Everywhere', 999)
COMMIT TRAN
SET XACT_ABORT OFF
-- Test table (SELECT)
SELECT * FROM DB2EXAMPLE.DSN1D037.NWIND.AREAS
-- Four-part linked server query (UPDATE with DUW transaction)
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
UPDATE DB2EXAMPLE.DSN1D037.NWIND.AREAS SET AREADESC = 'Nowhere' WHERE AREAID = 99999
COMMIT TRAN
SET XACT_ABORT OFF
-- Test table (SELECT)
SELECT * FROM DB2EXAMPLE.DSN1D037.NWIND.AREAS
-- Four-part linked server query (DELETE with DUW transaction)
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
DELETE FROM DB2EXAMPLE.DSN1D037.NWIND.AREAS WHERE AREAID = 99999
COMMIT TRAN
SET XACT_ABORT OFF
-- Test table (SELECT)
SELECT * FROM DB2EXAMPLE.DSN1D037.NWIND.AREAS
传递查询
SQL Server 将传递查询作为未解释的查询字符串发送到 OLE DB 数据源。 查询必须采用 OLE DB 数据源将接受的语法。 Transact-SQL 语句使用传递查询的结果，就像它是常规表引用一样。 OPENROWSET 和 OPENDATASOURCE 的参数不支持变量。 参数必须指定为字符串文本。 如果变量必须作为参数传入，则可以使用 EXECUTE 语句动态构造并执行包含变量的查询字符串。
以下代码片段显示创建传递查询的语法。
-- Example of a pass through query (SELECT with parameters)
DECLARE @AMOUNT DECIMAL(9,2);
SET @AMOUNT = 99.99;
EXECUTE ('SELECT * FROM NWIND.ORDERS WHERE AMOUNT = ?', @AMOUNT, 'Select') AT DB2EXAMPLE;
GO
-- Example of pass through query to execute DDL statement (DROP PROCEDURE)
EXECUTE ('DROP PROCEDURE NWIND.CUSTORD', 'Drop') AT DB2EXAMPLE;
GO
-- Example of pass through query to execute DDL statement (CREATE PROCEDURE)
EXECUTE ('CREATE PROCEDURE NWIND.CUSTORD (IN CUSTID INT) RESULT SETS 1 LANGUAGE SQL P1: BEGIN DECLARE CURSOR1 CURSOR WITH RETURN FOR SELECT * FROM NWIND.ORDERS WHERE CUSTID = CUSTORD.CUSTID ORDER BY ORDID ASC; OPEN CURSOR1; END P1', 'CreateProc') AT DB2EXAMPLE;
GO
-- Example of pass through query to execute CALL statement (with parameters)
DECLARE @CUSTID INT;
SET @CUSTID = 10001;
EXEC ( 'CALL NWIND.CUSTORD(?)', @CUSTID) AT DB2EXAMPLE;
GO
SQL Server Management Studio
还可以使用 SQL Server Management Studio 定义使用由四部分命名的链接服务器查询的 SELECT、INSERT、UPDATE 和 DELETE 语句。 这些查询提供可用于异类数据源的常见 Transact-SQL 语法。
按照以下步骤从 SQL Server Management Studio 定义链接服务器：
在对象资源管理器中，展开 “服务器对象”，单击 “链接服务器” 文件夹，然后右键单击“ 新建链接服务器”。
在“新建链接服务器”对话框中，输入链接服务器名称（如DB2EXAMPLE），并从提供程序列表中选择Microsoft OLE DB Provider for DB2。 在“产品名称”字段中输入 HIS。 将使用数据访问工具和数据源向导定义的有效连接字符串粘贴到 “提供程序”字符串 字段中。 在 “位置” 字段中输入 DB2 目录。
在“选择页面”窗格中，单击“安全性”，然后选择“使用此安全上下文进行”。 在 远程登录 中输入有效的 DB2 用户名， 并在“使用密码”中输入密码。
在“选择页面”窗格中，单击“ 服务器选项”，单击 “RPC Out ”，然后选择 “True”。 单击 “确定” 。
若要在远程服务器上显示对象，请展开 链接服务器 文件夹，展开定义的链接服务器，展开 目录、 表 和 视图。
若要创建查询，请右键单击表并选择将表生成脚本为。 选择SELECT 以打开并选择新建查询编辑器窗口。
在“查询”菜单中，单击“执行”（F5）。 你将在“结果”窗格中看到数据行。
还可以更改链接服务器定义，或将其用作创建其他链接服务器定义的模板。
在对象资源管理器中，右键单击之前定义的链接服务器。 选择 脚本链接服务器，选择 DROP 和 CREATE 到，然后单击 新建查询编辑器窗口。
编辑 Transact-SQL 语句，然后单击“查询”菜单中的“执行”（F5）。
右键单击重新定义的或新的链接服务器，然后单击“ 测试连接”。