Procesador de consultas
SQL Server consultas distribuidas para DB2
Las consultas distribuidas en SQL Server proporcionan acceso simultáneo distribuido a varios orígenes de datos. El procesador de consultas distribuidas (DQP) permite crear consultas heterogéneas que unen las tablas de SQL Server con las tablas de DB2, de los sistemas de archivos host, de Oracle o de cualquier otro origen de datos al que puede acceder un proveedor OLE DB. Puede usar DQP para crear vistas de SQL Server sobre tablas DB2 de modo que los programadores puedan escribir directamente en SQL Server e integrar a sus aplicaciones datos basados en Windows y datos basados en host.
En el diagrama siguiente se muestra la arquitectura DQP para acceder a datos con Host Integration Server (HIS).
Para acceder a los datos desde un origen de datos OLE DB, SQL Server requiere la siguiente información:
Nombre del proveedor OLE DB
Información de conexión en forma de cadena de inicialización de OLE DB
Nombre de tabla o cadena de consulta SQL
Credenciales de autorización
Puede hacer referencia a orígenes de datos heterogéneos mediante uno de los tres métodos:
Nombre ad hoc
Nombres de servidores vinculados
consultas de Pass-Through
Consultas de nombres ad hoc
Los nombres ad hoc se utilizan para consultas poco frecuentes de orígenes de datos OLE DB que no están definidos como servidores vinculados. En SQL Server, las funciones OPENROWSET y OPENDATASOURCE proporcionan información de conexión para acceder a datos de orígenes de datos OLE DB. De manera predeterminada, no se admiten nombres ad hoc. La opción proveedor DisallowAdhocAccess debe establecerse en 0 y la opción de configuración avanzada Consultas distribuidas ad hoc debe estar habilitada.
El fragmento de código siguiente muestra la sintaxis para habilitar consultas de nombre 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
El fragmento de código siguiente muestra la sintaxis para crear consultas 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]
Las funciones OPENROWSET y OPENDATASOURCE solo se deben utilizar para hacer referencia a orígenes de datos OLE DB a los que rara vez se obtiene acceso. Para los orígenes de datos cuyo acceso es más frecuente, defina un servidor vinculado. Ni OPENDATASOURCE ni OPENROWSET proporcionan toda la funcionalidad de definiciones de servidor vinculado. Por ejemplo, OPENROWSET y OPENDATASOURCE son macros y no admiten el suministro de variables transact-SQL como argumentos. Las consultas de nombres ad hoc no incluyen la administración de seguridad ni la capacidad de consultar información del catálogo. Cada vez que se llama a estas funciones, se debe proporcionar toda la información de conexión, incluidas las contraseñas.
Definir un servidor vinculado
Puede crear un nombre de servidor vinculado que defina una conexión a DB2 mediante instrucciones Transact-SQL o a través de la interfaz de usuario de SQL Server Management Studio.
Transact-SQL
En el fragmento de código siguiente se muestra la sintaxis de Transact-SQL para quitar, crear y especificar credenciales de autenticación para una definición de nombre de servidor vinculado.
-- 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
Consultas de paso a través
SQL Server envía consultas de paso a través como cadenas de consulta no interpretadas a un origen de datos OLE DB. La consulta debe seguir una sintaxis que el origen de datos OLE DB acepte. Una instrucción Transact-SQL usa los resultados de una consulta de paso a través como si fuera una referencia de tabla normal. Los argumentos de OPENROWSET y OPENDATASOURCE no admiten variables. Los argumentos se deben especificar como literales de cadena. Si las variables se deben pasar como argumentos, se puede crear dinámicamente una cadena de consulta que contenga las variables y ejecutarla mediante la instrucción EXECUTE.
El fragmento de código siguiente muestra la sintaxis que crea consultas de paso a través.
-- 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
También puede usar SQL Server Management Studio para definir instrucciones SELECT, INSERT, UPDATE y DELETE que usan consultas de servidor vinculado con nombre de cuatro partes. Estas consultas proporcionan una sintaxis común de Transact-SQL que se puede usar en orígenes de datos heterogéneos.
Siga estos pasos para definir un servidor vinculado desde el SQL Server Management Studio:
En el Explorador de objetos, expanda Objetos de servidor, haga clic en la carpeta Servidores vinculados y, a continuación, haga clic con el botón derecho en Nuevo servidor vinculado.
En el cuadro de diálogo Nuevo servidor vinculado, escriba un nombre de servidor vinculado, como DB2EXAMPLE, y seleccione Proveedor OLE DB de Microsoft para DB2 en la lista Proveedor. Escriba HIS en el campo Nombre del producto . Pegue un cadena de conexión válido que haya definido mediante la Herramienta de acceso a datos y el Asistente para orígenes de datos en el campo Cadena del proveedor. Escriba el catálogo DB2 en el campo Ubicación .
En el panel Seleccionar una página, haga clic en Seguridad y seleccione Realizar con este contexto de seguridad. Escriba un nombre de usuario de DB2 válido en Inicio de sesión remoto y una contraseña en Con contraseña.
En el panel Seleccionar una página, haga clic en Opciones del servidor, haga clic en RPC Fuera y seleccione True. Haga clic en OK.
Para mostrar objetos en el servidor remoto, expanda la carpeta Servidor vinculado , expanda el servidor vinculado que definió, expanda Catálogos, Tablas y vistas.
Para crear una consulta, haga clic con el botón derecho en una tabla y seleccione Crear script como. Elija SELECCIONAR para y seleccione Nueva ventana de Editor de Power Query.
En el menú Consulta, haga clic en Ejecutar (F5). Verá las filas de datos en el panel Resultados.
También puede modificar la definición del servidor vinculado o usarla como plantilla para crear otras definiciones de servidor vinculado.
En el Explorador de objetos, haga clic con el botón derecho en el servidor vinculado que definió anteriormente. Seleccione Script Linked Server (Servidor vinculado de script) como, elija DROP (COLOCAR) y CREATE (CREAR) y haga clic en New Editor de Power Query Window (Nueva ventana de Editor de Power Query).
Edite las instrucciones Transact-SQL y haga clic en Ejecutar (F5) en el menú Consulta.
Haga clic con el botón derecho en el servidor vinculado nuevo o definido y, a continuación, haga clic en Probar conexión.