Freigeben über


Abfrageprozessor

SQL Server verteilte Abfragen für DB2

Verteilte Abfragen in SQL Server bieten verteilten gleichzeitigen Zugriff auf mehrere Datenquellen. Der Prozessor für verteilte Abfragen ermöglicht das Erstellen heterogener Abfragen, die Tabellen in SQL Server mit Tabellen in DB2, Hostdateisystemen, Oracle oder anderen Datenquellen verknüpfen, auf die ein OLE DB-Anbieter zugreifen kann. Sie können den Prozessor für verteilte Abfragen zum Erstellen von SQL Server-Sichten für DB2-Tabellen verwenden, damit Entwickler direkt in SQL Server schreiben und auf Windows basierende und hostbasierte Daten in ihre Anwendungen integrieren können.

Das folgende Diagramm zeigt die DQP-Architektur für den Zugriff auf Daten mit Host Integration Server (HIS).

SQL Server verteilte Abfragen

Für den Zugriff auf Daten aus einer OLE DB-Datenquelle benötigt SQL Server die folgenden Informationen:

  1. Der Name des OLE DB-Anbieters

  2. Verbindungsinformationen in Form einer OLE DB-Initialisierungszeichenfolge

  3. Der Tabellenname oder die SQL-Abfragezeichenfolge

  4. Autorisierungsanmeldeinformationen

    Sie können auf heterogene Datenquellen mit einer von drei Methoden verweisen:

  5. Ad-hoc-Name

  6. Verbindungsservernamen

  7. Pass-Through Abfragen

Ad-hoc-Namensabfragen

Ein Ad-hoc-Name wird für seltene Abfragen von OLE DB-Datenquellen verwendet, die nicht als Verbindungsserver definiert sind. In SQL Server stellen die Funktionen OPENROWSET und OPENDATASOURCE Verbindungsinformationen für den Zugriff auf Daten aus OLE DB-Datenquellen bereit. Standardmäßig werden Ad-hoc-Namen nicht unterstützt. Die Anbieteroption DisallowAdhocAccess muss auf 0 festgelegt werden, und die erweiterte Konfigurationsoption ad hoc Distributed Queries muss aktiviert sein.

Das folgende Codefragment zeigt die Syntax zum Aktivieren von Ad-hoc-Namensabfragen an.

-- 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  
  

Das folgende Codefragment zeigt die Syntax zum Erstellen von Ad-hoc-Abfragen an.

-- 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 und OPENDATASOURCE sollten nur für Verweise auf OLE DB-Datenquellen verwendet werden, auf die selten zugegriffen wird. Definieren Sie einen Verbindungsserver für Datenquellen, auf die mehr als nur wenige Male zugegriffen wird. Weder OPENDATASOURCE noch OPENROWSET stellen den gesamten Funktionsumfang von Verbindungsserverdefinitionen bereit. OPENROWSET und OPENDATASOURCE sind z. B. Makros und unterstützen die Bereitstellung von Transact-SQL-Variablen als Argumente nicht. Ad-hoc-Namensabfragen umfassen weder die Sicherheitsverwaltung noch die Möglichkeit, Kataloginformationen abzufragen. Jedes Mal, wenn diese Funktionen aufgerufen werden, müssen alle Verbindungsinformationen, einschließlich Kennwörter, bereitgestellt werden.

Definieren eines Verbindungsservers

Sie können einen Verbindungsservernamen erstellen, der eine Verbindung mit DB2 mithilfe von Transact-SQL-Anweisungen oder über die benutzeroberfläche SQL Server Management Studio definiert.

Transact-SQL

Das folgende Codefragment veranschaulicht die Transact-SQL-Syntax zum Löschen, Erstellen und Angeben von Authentifizierungsanmeldeinformationen für eine Verbindungsservernamendefinition.

-- 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  
  

Übergeben von Abfragen

SQL Server sendet Passthrough-Abfragen als nicht interpretierte Abfragezeichenfolgen an eine OLE DB-Datenquelle. Die Abfrage muss in einer von der OLE DB-Datenquelle unterstützten Syntax vorliegen. Eine Transact-SQL-Anweisung verwendet die Ergebnisse aus einer Passthrough-Abfrage, als ob es sich um einen regulären Tabellenverweis handeln würde. Die Argumente von OPENROWSET und OPENDATASOURCE unterstützen keine Variablen. Die Argumente müssen als Zeichenfolgenliterale angegeben werden. Wenn Variablen als Argumente übergeben werden müssen, kann dynamisch eine Abfragezeichenfolge, die diese Variablen enthält, erstellt und mithilfe der EXECUTE-Anweisung ausgeführt werden.

Das folgende Codefragment zeigt die Syntax an, die Passthrough-Abfragen erstellt.

-- 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

Sie können auch SQL Server Management Studio verwenden, um SELECT-, INSERT-, UPDATE- und DELETE-Anweisungen zu definieren, die vierteilige benannte Verbindungsserverabfragen verwenden. Diese Abfragen stellen eine allgemeine Transact-SQL-Syntax bereit, die für heterogene Datenquellen verwendet werden kann.

Führen Sie die folgenden Schritte aus, um einen Verbindungsserver aus dem SQL Server Management Studio zu definieren:

  1. Erweitern Sie im Objekt-Explorer Serverobjekte, klicken Sie auf den Ordner Verbindungsserver, und klicken Sie dann mit der rechten Maustaste auf Neuer Verbindungsserver.

  2. Geben Sie im Dialogfeld Neuer Verbindungsserver einen Namen des Verbindungsservers ein, z. B. DB2EXAMPLE, und wählen Sie in der Liste AnbieterMicrosoft OLE DB-Anbieter für DB2 aus. Geben Sie his in das Feld Produktname ein. Fügen Sie eine gültige Verbindungszeichenfolge, die Sie mit dem Datenzugriffstool und dem Datenquellen-Assistenten definiert haben, in das Feld Anbieterzeichenfolge ein. Geben Sie den DB2-Katalog in das Feld Speicherort ein.

  3. Klicken Sie im Bereich Seite auswählen auf Sicherheit , und wählen Sie Mit diesem Sicherheitskontext erstellt werden aus. Geben Sie unter Remoteanmeldung einen gültigen DB2-Benutzernamen und unter Mit Kennwort ein Kennwort ein.

  4. Klicken Sie im Bereich Seite auswählen auf Serveroptionen, klicken Sie auf RPC Out , und wählen Sie True aus. Klicken Sie auf OK.

  5. Erweitern Sie zum Anzeigen von Objekten auf dem Remoteserver den Ordner Verbindungsserver , den von Ihnen definierten Verbindungsserver, und erweitern Sie Kataloge, Tabellen und Sichten.

  6. Klicken Sie zum Erstellen einer Abfrage mit der rechten Maustaste auf eine Tabelle, und wählen Sie Skripttabelle als aus. Wählen Sie SELECT to und anschließend New Abfrage-Editor Window (Neues Abfrage-Editor Fenster) aus.

  7. Klicken Sie im Menü Abfrage auf Ausführen (F5). Die Datenzeilen werden im Bereich Ergebnisse angezeigt.

    Sie können auch die Definition des Verbindungsservers ändern oder als Vorlage zum Erstellen anderer Verbindungsserverdefinitionen verwenden.

  8. Klicken Sie im Objekt-Explorer mit der rechten Maustaste auf den Verbindungsserver, den Sie zuvor definiert haben. Wählen Sie Verbindungsserver skriptieren als aus, wählen Sie DROP und CREATE to aus, und klicken Sie auf Neu Abfrage-Editor Fenster.

  9. Bearbeiten Sie die Transact-SQL-Anweisungen, und klicken Sie im Menü Abfrage auf Ausführen (F5 ).

  10. Klicken Sie mit der rechten Maustaste auf den neu definierten oder neuen Verbindungsserver, und klicken Sie dann auf Verbindung testen.

Weitere Informationen

SQL Server