查询处理器

项目
07/21/2023

SQL Server DB2 的分布式查询

SQL Server 中的分布式查询提供对多个数据源的分布式并发访问。可以使用分布式查询处理器 (DQP) 创建将 SQL Server 中的表与 DB2 中的表连接的各种查询、主机文件系统、Oracle，或可由 OLE DB 访问接口访问的任何其他数据源。您可以使用 DQP 在 DB2 表中创建 SQL Server 视图，以便开发人员可以直接写入到 SQL Server 并在其应用程序中集成基于 Windows 和基于主机的数据。

下图显示了使用 Host Integration Server (HIS) 访问数据的 DQP 体系结构。

SQL Server分布式查询

若要从 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），然后从“提供程序”列表中选择“DB2 的 Microsoft OLE DB 提供程序”。在“产品名称”字段中输入 HIS。将使用数据访问工具和数据源向导定义的有效连接字符串粘贴到“提供程序字符串”字段中。在 “位置” 字段中输入 DB2 目录。
在“选择页面”窗格中，单击“ 安全性 ”，然后选择“ 使用此安全上下文创建”。在 “远程登录 ”中输入有效的 DB2 用户名，在“ 使用密码”中输入密码。
在“选择页面”窗格中，单击“ 服务器选项”，单击“ RPC 输出 ”，然后选择“ True”。单击 “确定” 。
若要在远程服务器上显示对象，请展开 “链接服务器 ”文件夹，展开定义的链接服务器，然后展开“ 目录”、“ 表和视图”。
若要创建查询，请右键单击表，然后选择“ 将表脚本编写为”。选择“SELECT”，然后选择“新建查询编辑器窗口”。
在“查询”菜单中，单击“ 执行” (F5) 。你将在“结果”窗格中看到数据行。

还可以更改链接服务器定义，或将其用作模板来创建其他链接服务器定义。
在对象资源管理器中，右键单击之前定义的链接服务器。选择“将链接服务器编写为脚本”，选择“删除并创建到”，然后单击“新建查询编辑器窗口”。
编辑 Transact-SQL 语句，然后单击“查询” 菜单中的“执行 (F5) ”。
右键单击重新定义的或新的链接服务器，然后单击“ 测试连接”。

另请参阅

SQL Server

通过