Query Processor
SQL Server query distribuite per DB2
Le query distribuite in SQL Server forniscono l'accesso simultaneo a più origini dati. Distributed Query Processor (DQP) consente di creare query eterogenee in grado di creare un join tra le tabelle di SQL Server e quelle di DB2, dei sistemi file host, di Oracle o di qualsiasi altra origine dati accessibile da parte di un provider OLE DB. È possibile utilizzare DQP per creare viste di SQL Server su tabelle DB2, in modo da consentire agli sviluppatori di scrivere direttamente in SQL Server e integrare nelle applicazioni sia i dati basati su Windows sia quelli basati su host.
Il diagramma seguente illustra l'architettura DQP per l'accesso ai dati con Host Integration Server (HIS).
Per accedere ai dati da un'origine dati OLE DB, SQL Server richiede le informazioni seguenti:
Nome del provider OLE DB
Informazioni di connessione sotto forma di stringa di inizializzazione OLE DB
Nome tabella o stringa di query SQL
Credenziali di autorizzazione
È possibile fare riferimento a origini dati eterogenee usando uno dei tre metodi seguenti:
Nome ad hoc
Nomi dei server collegati
query Pass-Through
Query nome ad hoc
I nomi ad hoc vengono utilizzati per query eseguite raramente su origini dei dati OLE DB non definite come server collegati. In SQL Server le funzioni OPENROWSET e OPENDATASOURCE forniscono informazioni di connessione per l'accesso ai dati da origini dati OLE DB. Per impostazione predefinita, i nomi ad hoc non sono supportati. L'opzione provider DisallowAdhocAccess deve essere impostata su 0 e l'opzione di configurazione avanzata query distribuite ad Hoc deve essere abilitata.
Il frammento di codice seguente visualizza la sintassi per abilitare le query sui nomi ad hoc.
-- 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
Il frammento di codice seguente visualizza la sintassi per la creazione di query ad hoc.
-- 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]
È consigliabile utilizzare le funzioni OPENROWSET e OPENDATASOURCE solo per fare riferimento a origini dei dati OLE DB a cui si accede raramente. Per le origini dei dati a cui è necessario accedere con maggiore frequenza, è possibile definire un server collegato. OPENDATASOURCE e OPENROWSET non dispongono di tutte le funzionalità offerte dalle definizioni dei server collegati, Ad esempio, OPENROWSET e OPENDATASOURCE sono macro e non supportano la fornitura di variabili Transact-SQL come argomenti. Le query sui nomi ad hoc non includono la gestione della sicurezza o la possibilità di eseguire query sulle informazioni del catalogo. Ogni volta che queste funzioni vengono chiamate, tutte le informazioni di connessione, incluse le password, devono essere fornite.
Definire un server collegato
È possibile creare un nome del server collegato che definisce una connessione a DB2 usando istruzioni Transact-SQL o tramite l'interfaccia utente SQL Server Management Studio.
Transact-SQL
Il frammento di codice seguente illustra la sintassi Transact-SQL per l'eliminazione, la creazione e la specifica delle credenziali di autenticazione per una definizione del nome del server collegato.
-- 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
Query pass-through
SQL Server invia query pass-through come stringhe di query non interpretate a un'origine dati OLE DB. Le query devono essere formulate con una sintassi supportata dall'origine dei dati OLE DB. Un'istruzione Transact-SQL usa i risultati di una query pass-through come se fosse un riferimento di tabella regolare. Gli argomenti di OPENROWSET e OPENDATASOURCE non supportano le variabili. e devono essere specificati come stringhe letterali. Se è necessario passare variabili come argomenti, è possibile costruire dinamicamente una stringa di query che contiene le variabili ed eseguirla tramite l'istruzione EXECUTE.
Il frammento di codice seguente visualizza la sintassi che crea query pass through.
-- 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
È anche possibile usare SQL Server Management Studio per definire istruzioni SELECT, INSERT, UPDATE e DELETE che usano query del server collegato denominate quattro parti. Queste query forniscono una sintassi Transact-SQL comune che può essere usata su origini dati eterogenee.
Seguire questa procedura per definire un server collegato dalla SQL Server Management Studio:
Nella Esplora oggetti espandere Oggetti server, fare clic sulla cartella Server collegati e quindi fare clic con il pulsante destro del mouse su Nuovo server collegato.
Nella finestra di dialogo Nuovo server collegato immettere un nome del server collegato, ad esempio DB2EXAMPLE e selezionare provider Microsoft OLE DB per DB2 dall'elenco Provider. Immettere HIS nel campo Nome prodotto . Incollare un stringa di connessione valido definito usando lo strumento di accesso ai dati e la procedura guidata origine dati nel campo Stringa provider. Immettere il catalogo DB2 nel campo Percorso .
Nel riquadro Seleziona una pagina fare clic su Sicurezza e selezionare Crea usando questo contesto di sicurezza. Immettere un nome utente DB2 valido nell'account di accesso remoto e una password in Con password.
Nel riquadro Selezionare una pagina fare clic su Opzioni server, fare clic su Rpc Out e selezionare True. Fare clic su OK.
Per visualizzare gli oggetti nel server remoto, espandere la cartella Server collegato , espandere il server collegato definito, espandere Cataloghi, tabelle e viste.
Per creare una query, fare clic con il pulsante destro del mouse su una tabella e selezionare Tabella script come. Scegliere SELECT per e selezionare Nuova finestra Editor di query.
Dal menu Query fare clic su Esegui (F5). Verranno visualizzate le righe di dati nel riquadro Risultati.
È anche possibile modificare la definizione del server collegato o usarla come modello per la creazione di altre definizioni del server collegato.
Nella Esplora oggetti fare clic con il pulsante destro del mouse sul server collegato definito in precedenza. Selezionare Script Linked Server come, scegliere DROP e CREATE per e fare clic su Nuova finestra Editor di query.
Modificare le istruzioni Transact-SQL e fare clic su Esegui (F5) dal menu Query.
Fare clic con il pulsante destro del mouse sul nuovo server collegato o sul nuovo server collegato e quindi scegliere Test connessione.