共用方式為

查詢處理器

適用於 DB2 的 SQL Server 分散式查詢

SQL Server 中的分散式查詢提供多個數據源的分散式並行存取。 分散式查詢處理器 （DQP） 可讓您建立異質查詢，將 SQL Server 中的數據表與 DB2、主機檔系統、Oracle 或任何其他數據源的數據表聯結在一起，供 OLE DB 提供者存取。 您可以使用 DQP 在 DB2 資料表上建立 SQL Server 檢視，讓開發人員可以直接寫入 SQL Server，並在其應用程式中整合 Windows 型和主機型數據。

下圖顯示使用主機整合伺服器 （HIS） 存取資料的 DQP 架構。

SQL Server 分散式查詢

若要從 OLE DB 數據來源存取資料，SQL Server 需要下列資訊：

  1. OLE DB 提供者的名稱

  2. 以 OLE DB 初始化字串形式的連接資訊

  3. 數據表名稱或 SQL 查詢字串

  4. 授權認證

    您可以使用下列三種方法之一來參考異質資料來源：

  5. 臨時名稱

  6. 連結的伺服器名稱

  7. 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 定義連結的伺服器：

  1. 在 [物件總管] 中，展開 [ 伺服器物件]，按兩下 [鏈接的伺服器 ] 資料夾，然後按兩下 [ 新增連結的伺服器]。

  2. 在 [新增鏈接的伺服器] 對話框中，輸入鏈接的伺服器名稱，例如DB2EXAMPLE，然後從 [提供者] 列表中選取 [Microsoft OLE DB Provider for DB2]。 在 [產品名稱] 欄位中輸入 HIS。 將您使用 [資料存取工具和數據源精靈] 定義的有效連接字串貼到 [提供者字串 ] 字段中。 在 [ 位置 ] 欄位中輸入 DB2 目錄。

  3. 在 [選取頁面] 窗格中，點擊 [安全性]，然後選取 [利用此安全性上下文進行]。 在 [遠端登錄 ] 中輸入有效的 DB2 用戶名稱，並在 [ 使用密碼] 中輸入密碼。

  4. 在 [選取頁面] 窗格中，按兩下 [ 伺服器選項]，按兩下 [RPC Out ]，然後選取 [True]。 按一下 [確定]。

  5. 若要在遠端伺服器上顯示物件，請展開 [鏈接的伺服器] 資料夾、您定義的連結伺服器、展開 [目錄]、[資料表和檢視]。

  6. 若要建立查詢，請以滑鼠右鍵按一下資料表，然後選取 [ 將資料表作為腳本]。 請選擇 [SELECT to] 並選取 [新增查詢編輯器視窗]。

  7. 從 [查詢] 功能表中，按一下 [執行 (F5)]。 您會在 [結果] 窗格中看到資料列。

    您也可以改變連結的伺服器定義，或使用它作為建立其他連結伺服器定義的範本。

  8. 在 [物件總管] 中，右鍵點擊您先前定義的連結伺服器。 選取 [腳本鏈接的伺服器] 作為 ，選擇 [DROP] 和 [建立至]，然後按兩下 [ 新增查詢編輯器視窗]。

  9. 編輯 Transact-SQL 語句，然後在 [查詢] 功能表中，按下 [執行 (F5)]。

  10. 以滑鼠右鍵點選重新定義或新的連結伺服器，然後點選測試連線。

另請參閱

SQL Server

  • Last updated on