쿼리 프로세서

아티클
07/21/2023

DB2에 대한 분산 쿼리 SQL Server

SQL Server 분산 쿼리는 여러 데이터 원본에 대한 분산 동시 액세스를 제공합니다. DQP(분산 쿼리 프로세서)를 사용하여 DB2, 호스트 파일 시스템, Oracle 또는 OLE DB 공급자가 액세스할 수 있는 기타 데이터 원본의 테이블과 SQL Server의 테이블을 결합하는 유형이 다른 쿼리를 만들 수 있습니다. 개발자가 SQL Server에 직접 쓰고 Windows 기반 및 호스트 기반 데이터를 해당 응용 프로그램에 통합할 수 있도록 DQP를 사용하여 DB2 테이블을 통한 SQL Server 뷰를 만들 수 있습니다.

다음 다이어그램에서는 HIS(Host Integration Server)를 사용하여 데이터에 액세스하기 위한 DQP 아키텍처를 보여 줍니다.

SQL Server 분산 쿼리

OLE DB 데이터 원본에서 데이터에 액세스하려면 SQL Server 다음 정보가 필요합니다.

OLE DB 공급자의 이름
OLE DB 초기화 문자열 형식의 연결 정보
테이블 이름 또는 SQL 쿼리 문자열
권한 부여 자격 증명

다음 세 가지 방법 중 하나를 사용하여 다른 유형의 데이터 원본을 참조할 수 있습니다.
임시 이름
연결된 서버 이름
Pass-Through 쿼리

임시 이름 쿼리

임시 이름은 연결된 서버로 정의되지 않은 OLE DB 데이터 원본에 대해 자주 사용되지 않는 쿼리에 사용할 수 있습니다. SQL Server OPENROWSET 및 OPENDATASOURCE 함수는 OLE DB 데이터 원본에서 데이터에 액세스하기 위한 연결 정보를 제공합니다. 기본적으로 임시 이름은 지원되지 않습니다. DisallowAdhocAccess 공급자 옵션은 0으로 설정해야 하며 임시 분산 쿼리 고급 구성 옵션을 사용하도록 설정해야 합니다.

다음 코드 조각은 임시 이름 쿼리를 사용하도록 설정하기 위한 구문을 표시합니다.

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

다음 코드 조각은 임시 쿼리를 만들기 위한 구문을 표시합니다.

-- 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과 OPENDATASOURCE는 자주 사용되지 않는 OLE DB 데이터 원본을 참조하기 위해서만 사용해야 합니다. 여러 번 액세스될 모든 데이터 원본에 대해서는 연결된 서버를 정의해야 합니다. OPENDATASOURCE나 OPENROWSET 모두 연결된 서버 정의의 모든 기능을 제공하지는 않습니다. 예를 들어 OPENROWSET 및 OPENDATASOURCE는 매크로이며 Transact-SQL 변수를 인수로 제공하는 것을 지원하지 않습니다. 임시 이름 쿼리에는 보안 관리 또는 카탈로그 정보를 쿼리하는 기능이 포함되지 않습니다. 이러한 함수가 호출될 때마다 암호를 포함한 모든 연결 정보를 제공해야 합니다.

연결된 서버 정의

Transact-SQL 문을 사용하거나 SQL Server Management Studio 사용자 인터페이스를 통해 DB2에 대한 연결을 정의하는 연결된 서버 이름을 만들 수 있습니다.

Transact-SQL

다음 코드 조각에서는 연결된 서버 이름 정의에 대한 인증 자격 증명을 삭제, 생성 및 지정하기 위한 Transact-SQL 구문을 보여 줍니다.

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

쿼리 통과

SQL Server 통과 쿼리를 해석되지 않은 쿼리 문자열로 OLE DB 데이터 원본으로 보냅니다. 이 쿼리는 OLE DB 데이터 원본이 받아들일 수 있는 구문으로 작성되어야 합니다. Transact-SQL 문은 통과 쿼리의 결과를 일반 테이블 참조인 것처럼 사용합니다. OPENROWSET 및 OPENDATASOURCE의 인수는 변수를 지원하지 않습니다. 인수는 문자열 리터럴로 지정되어야 합니다. 변수를 인수로 전달해야 하는 경우 해당 변수가 포함된 쿼리 문자열을 동적으로 생성하고 EXECUTE 문을 사용하여 실행할 수 있습니다.

다음 코드 조각은 쿼리 통과를 만드는 구문을 표시합니다.

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

SQL Server Management Studio 사용하여 연결된 서버 쿼리라는 네 부분으로 구성된 SELECT, INSERT, UPDATE 및 DELETE 문을 정의할 수도 있습니다. 이러한 쿼리는 다른 유형의 데이터 원본에 사용할 수 있는 일반적인 Transact-SQL 구문을 제공합니다.

다음 단계에 따라 SQL Server Management Studio 연결된 서버를 정의합니다.

개체 탐색기 서버 개체를 확장하고 연결된 서버 폴더를 클릭한 다음 새 연결된 서버를 마우스 오른쪽 단추로 클릭합니다.
새 연결된 서버 대화 상자에서 DB2EXAMPLE 같은 연결된 서버 이름을 입력하고 공급자 목록에서 Microsoft OLE DB Provider for DB2 선택합니다. 제품 이름 필드에 HIS를 입력합니다. 데이터 액세스 도구 및 데이터 원본 마법사를 사용하여 정의한 유효한 연결 문자열 공급자 문자열 필드에 붙여넣습니다. 위치 필드에 DB2 카탈로그를 입력합니다.
페이지 선택 창에서 보안을 클릭하고 이 보안 컨텍스트를 사용하여 만들기를 선택합니다. 원격 로그인에 유효한 DB2 사용자 이름과 암호 포함에 암호를 입력합니다.
페이지 선택 창에서 서버 옵션을 클릭하고 RPC Out 을 클릭한 다음 True를 선택합니다. 확인을 클릭합니다.
원격 서버에 개체를 표시하려면 연결된 서버 폴더를 확장하고 정의한 연결된 서버를 확장하고 카탈로그, 테이블 및 뷰를 확장합니다.
쿼리를 만들려면 테이블을 마우스 오른쪽 단추로 클릭하고 테이블 스크립팅을 선택합니다. 선택 대상을 선택하고 새 쿼리 편집기 창을 선택합니다.
쿼리 메뉴에서 실행(F5)을 클릭합니다. 결과 창에 데이터 행이 표시됩니다.

연결된 서버 정의를 변경하거나 다른 연결된 서버 정의를 만들기 위한 템플릿으로 사용할 수도 있습니다.
개체 탐색기 이전에 정의한 연결된 서버를 마우스 오른쪽 단추로 클릭합니다. 연결된 서버 스크립팅을 선택하고 DROP 및 CREATE를 선택한 다음 새 쿼리 편집기 창을 클릭합니다.
Transact-SQL 문을 편집하고 쿼리 메뉴에서 실행(F5) 을 클릭합니다.
다시 정의되거나 새 연결된 서버를 마우스 오른쪽 단추로 클릭한 다음 연결 테스트를 클릭합니다.

참고 항목

SQL Server

다음을 통해 공유