查詢處理器

發行項
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，然後從 [提供者] 清單中選取[Microsoft OLE DB Provider for DB2]。在 [產品名稱]欄位中輸入HIS。將您使用 [資料存取工具和資料來源精靈] 定義的有效連接字串貼到[提供者字串] 欄位中。在 [ 位置] 欄位中輸入 DB2 目錄。
在 [選取頁面] 窗格中，按一下 [ 安全性 ]，然後選取 [使用此安全性內容進行]。在 [遠端登入 ] 中輸入有效的 DB2 使用者名稱，並在 [ 使用密碼] 中輸入密碼。
在 [選取頁面] 窗格中，按一下 [ 伺服器選項]，按一下 [ RPC Out ]，然後選取 [True]。按一下 [確定]。
若要在遠端伺服器上顯示物件，請展開 [連結的伺服器 ] 資料夾，展開您定義的連結伺服器、 [目錄]、[ 資料表 和檢視]。
若要建立查詢，請以滑鼠右鍵按一下資料表，然後選取 [ 腳本資料表為]。選擇[選取] 並選取 [新增查詢編輯器視窗]。
從 [查詢] 功能表中，按一下 [ 執行 (F5) ]。您會在 [結果] 窗格中看到資料列。

您也可以改變連結的伺服器定義，或使用它作為建立其他連結伺服器定義的範本。
在物件總管中，以滑鼠右鍵按一下您先前定義的連結伺服器。選取 [編寫連結的伺服器腳本]，選擇[DROP] 和 [建立] 以按一下 [新增查詢編輯器視窗]。
編輯 Transact-SQL 語句，然後從 [查詢] 功能表按一下 [ 執行 (F5) ]。
以滑鼠右鍵按一下重新定義的或新的連結伺服器，然後按一下 [ 測試連線]。

另請參閱

SQL Server

共用方式為