Processeur de requêtes
SQL Server Requêtes distribuées pour DB2
Les requêtes distribuées dans SQL Server fournissent un accès simultané distribué à plusieurs sources de données. DQP (Distributed Query Processor) vous permet de créer des requêtes hétérogènes qui joignent les tables dans SQL Server aux tables dans DB2, les systèmes de fichiers hôtes, Oracle ou toute autre source de données accessible par un fournisseur OLE DB. Vous pouvez utiliser DQP pour créer des vues SQL Server sur des tables DB2 afin que les développeurs puissent écrire directement dans SQL Server et intégrer les données Windows et de l'hôte dans leurs applications.
Le diagramme suivant montre l’architecture DQP pour accéder aux données avec Host Integration Server (HIS).
Pour accéder aux données à partir d’une source de données OLE DB, SQL Server nécessite les informations suivantes :
Nom du fournisseur OLE DB
Informations de connexion sous la forme d’une chaîne d’initialisation OLE DB
Nom de la table ou chaîne de requête SQL
Informations d’identification d’autorisation
Vous pouvez référencer des sources de données hétérogènes à l’aide de l’une des trois méthodes suivantes :
Nom ad hoc
Noms de serveurs liés
requêtes Pass-Through
Requêtes de nom ad hoc
Les noms appropriés sont utilisés pour les requêtes peu fréquentes impliquant des sources de données OLE DB qui ne sont pas définies en tant que serveurs liés. Dans SQL Server, les fonctions OPENROWSET et OPENDATASOURCE fournissent des informations de connexion pour accéder aux données à partir de sources de données OLE DB. Par défaut, les noms appropriés ne sont pas pris en charge. L’option de fournisseur DisallowAdhocAccess doit être définie sur 0 et l’option de configuration avancée des requêtes distribuées ad hoc doit être activée.
Le fragment de code suivant affiche la syntaxe permettant d’activer les requêtes de nom 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
Le fragment de code suivant affiche la syntaxe de création de requêtes 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]
OPENROWSET et OPENDATASOURCE doivent être utilisés uniquement pour faire référence à des sources de données OLE DB faisant l'objet d'accès peu fréquents. Pour les sources de données faisant l'objet d'accès plus fréquents, définissez un serveur lié. OPENDATASOURCE et OPENROWSET ne fournissent pas toutes les fonctionnalités des définitions de serveur lié, Par exemple, OPENROWSET et OPENDATASOURCE sont des macros et ne prennent pas en charge la fourniture de variables Transact-SQL en tant qu’arguments. Les requêtes de nom ad hoc n’incluent pas la gestion de la sécurité ou la possibilité d’interroger des informations de catalogue. Chaque fois que ces fonctions sont appelées, toutes les informations de connexion, y compris les mots de passe, doivent être fournies.
Définir un serveur lié
Vous pouvez créer un nom de serveur lié qui définit une connexion à DB2 à l’aide d’instructions Transact-SQL ou via l’interface utilisateur SQL Server Management Studio.
Transact-SQL
Le fragment de code suivant illustre la syntaxe Transact-SQL pour supprimer, créer et spécifier des informations d’identification d’authentification pour une définition de nom de serveur lié.
-- 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
Requêtes directes
SQL Server envoie des requêtes directes sous forme de chaînes de requête non interprétées à une source de données OLE DB. La syntaxe de ces requêtes doit être compatible avec la source de données OLE DB. Une instruction Transact-SQL utilise les résultats d’une requête directe comme s’il s’agissait d’une référence de table normale. Les arguments de OPENROWSET et OPENDATASOURCE ne prennent pas en charge les variables. Les arguments doivent être spécifiés sous la forme de littéraux de chaîne. Si des variables doivent être transmises en guise d'arguments, une chaîne de requête contenant celles-ci peut être construite dynamiquement et exécutée à l'aide de l'instruction EXECUTE.
Le fragment de code suivant affiche la syntaxe qui crée des requêtes 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
Vous pouvez également utiliser SQL Server Management Studio pour définir des instructions SELECT, INSERT, UPDATE et DELETE qui utilisent des requêtes de serveur lié nommées en quatre parties. Ces requêtes fournissent une syntaxe Transact-SQL commune qui peut être utilisée sur des sources de données hétérogènes.
Procédez comme suit pour définir un serveur lié à partir du SQL Server Management Studio :
Dans le Explorateur d'objets, développez Objets serveur, cliquez sur le dossier Serveurs liés, puis cliquez avec le bouton droit sur Nouveau serveur lié.
Dans la boîte de dialogue Nouveau serveur lié, entrez un nom de serveur lié tel que DB2EXAMPLE, puis sélectionnez Fournisseur OLE DB Microsoft pour DB2 dans la liste Fournisseur. Entrez HIS dans le champ Nom du produit . Collez un chaîne de connexion valide que vous avez défini à l’aide de l’Outil d’accès aux données et de l’Assistant Source de données dans le champ Chaîne du fournisseur. Entrez le catalogue DB2 dans le champ Emplacement .
Dans le volet Sélectionner une page, cliquez sur Sécurité , puis sélectionnez Être effectué à l’aide de ce contexte de sécurité. Entrez un nom d’utilisateur DB2 valide dans Connexion à distance et un mot de passe dans Avec mot de passe.
Dans le volet Sélectionner une page, cliquez sur Options du serveur, cliquez sur RPC Out et sélectionnez True. Cliquez sur OK.
Pour afficher des objets sur le serveur distant, développez le dossier Serveur lié , développez le serveur lié que vous avez défini, développez Catalogues, tables et vues.
Pour créer une requête, cliquez avec le bouton droit sur une table et sélectionnez Scripter la table en tant que. Choisissez SÉLECTIONNER dans, puis sélectionnez Nouvelle fenêtre Éditeur de requête.
Dans le menu Requête, cliquez sur Exécuter (F5). Vous verrez les lignes de données dans le volet Résultats.
Vous pouvez également modifier la définition du serveur lié ou l’utiliser comme modèle pour créer d’autres définitions de serveur lié.
Dans le Explorateur d'objets, cliquez avec le bouton droit sur le serveur lié que vous avez défini précédemment. Sélectionnez Script Linked Server as, choisissez DROP et CREATE to, puis cliquez sur New Éditeur de requête Window.
Modifiez les instructions Transact-SQL et cliquez sur Exécuter (F5) dans le menu Requête.
Cliquez avec le bouton droit sur le serveur lié re-défini ou nouveau, puis cliquez sur Tester la connexion.