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).
Für den Zugriff auf Daten aus einer OLE DB-Datenquelle benötigt SQL Server die folgenden Informationen:
Der Name des OLE DB-Anbieters
Verbindungsinformationen in Form einer OLE DB-Initialisierungszeichenfolge
Der Tabellenname oder die SQL-Abfragezeichenfolge
Autorisierungsanmeldeinformationen
Sie können auf heterogene Datenquellen mit einer von drei Methoden verweisen:
Ad-hoc-Name
Verbindungsservernamen
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:
Erweitern Sie im Objekt-Explorer Serverobjekte, klicken Sie auf den Ordner Verbindungsserver, und klicken Sie dann mit der rechten Maustaste auf Neuer Verbindungsserver.
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.
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.
Klicken Sie im Bereich Seite auswählen auf Serveroptionen, klicken Sie auf RPC Out , und wählen Sie True aus. Klicken Sie auf OK.
Erweitern Sie zum Anzeigen von Objekten auf dem Remoteserver den Ordner Verbindungsserver , den von Ihnen definierten Verbindungsserver, und erweitern Sie Kataloge, Tabellen und Sichten.
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.
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.
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.
Bearbeiten Sie die Transact-SQL-Anweisungen, und klicken Sie im Menü Abfrage auf Ausführen (F5 ).
Klicken Sie mit der rechten Maustaste auf den neu definierten oder neuen Verbindungsserver, und klicken Sie dann auf Verbindung testen.