이 글은 Naver cafe SQLROAD카페(MSSQL전문가로가는지름길)에 올렸던 글에 좀더 살을 붙인 글입니다.

 MS SQL Server를 운영하는 중에 이기종 DBMS에서 data를 가져올 일이 꽤 많죠. DW 구축을 위해 도입한 ETL Tool이 있다면 이런 일이 다른 팀에 생색내며 해줄 수 있는 좋은 건수가 될텐데요. 대부분의 경우 이런 경우 PHP 같은 스크립트 언어를 이용해서 data를 넘기도록 만들거나, 해당 DB에 Tool로 접속하여 엑셀 파일 형태로 data를 뽑아낸뒤 다시 업로드하는 방식을 이용하는걸로 알고 있습니다. 조금 번거롭죠...
이건 SQL Server를 운영하는 DBA만의 고민은 아닐겁니다. 이번엔 SQL Server를 운영하다가 PostgreSQL의 Data를 가져올때 어떻게 하나... 하는 얘기를 해볼까합니다. Oracle, Tibero와 data를 공유하는 것에 대해서는 이전에 올린 글들을 찾아보시면 될것 같습니다. 이번에는 PostgreSQL입니다.

 제가 올렸던 SQL Server 관련 글들을 보시면 아시겠지만, 저는 OLEDB, ODBC 드라이버를 이용해서 SSIS 혹은 Openquery를 쓰는 방식을 얘기할 생각입니다.

1. ODBC, OLEDB driver 설치하기.
 PostgreSQL 홈페이지(www.postgresql.org)에서 다운로드 메뉴로 들어가면 "Driver and interface"라는 항목이 있습니다. (바로가기)
 OLEDB driver가 몇가지 있지만 저는 PostgreSQL OLE DB Provider project에서 제공하는 PostgreSQL OLE DB Provider for Windows를 이용하였습니다. 이유는 오픈소스라서입니다. PostgreSQL Native OLEDB Provider (PGNP)라는게 있는데 최근까지 업데이트도 되고 SQL Server 200의 DTS와 2005/2008의 SSIS 그리고 복제 등에서 사용할 수 있다고 소개되어있네요. 문제는 Trial 버전이라는겁니다. 아직 Manual과 홈페지이를 다 확인하지는 못했지만, 제약이 있을것 같네요.

 

2. Linked server(연결된 서버)를 이용한 Openquery 사용하기
 Openquery는

3. SSIS로 data 가져오기
 설치한 OLEDB 드라이버를 통해서 PostrgeSQL에 접속할 수 있습니다.
아래의 그림은 SQL Server 2008 버전의 SQL Server Management Studio에서 SSIS를 사용할 때 데이터 원본 선택 화면입니다.

SQL Server 가져오기 및 내보내기 마법사


위에서 PostgreSQL OLEDB driver를 선택하면 아래와 같은 화면이 보입니다.


위에서 한 뒤 연결 속성을 클릭하면 아래와 같이 창이 뜹니다. 여기서 PostgreSQL 정보를 입력하면 해당 DB에 접속이 가능합니다.

 여기서부터는 PGNP Provider를 설치하고 SSIS를 사용할때의 화면입니다. 뭐... 별로 다른점은 없어 보입니다.
화면이 달라 보인다면 그건 위의 화면은 Windows 2003 Test server에서 캡쳐한 화면이고, 아래는 Windows 7 Test PC에서 캠쳐한 화면이라는 겁니다.


SSIS 사용에 대해서는 다음에 따로 정리하도록 하겠습니다.
그럼 이만...





저작자표시 동일조건

'IT 기술 > Database' 카테고리의 다른 글

한살이라도 어릴때 오라클 튜닝 능력 향상을 위해 "AWR을 이용한 고성능 데이타베이스 튜닝" 수강 중  (0) 2011.04.25
SQL Server 2008 R2 Upgrade seminar  (0) 2011.04.22
DB 복제, Data 동기화 솔루션에 대한 고민  (0) 2010.11.16
SQL Server 2005에서 중복 값 제거하기  (0) 2010.09.07
MS SQL Server 2005 서버 튜닝 (1) - tempdb 및 file 관련 설정  (0) 2010.08.13
 Oracle과 MS-SQL 사이의 Data 공유를 위해서 Openquery를 이용하고 있습니다. 지금까지 잘 이용하고 있었는데, 문제가 발생했습니다. Oracle DB를 Tibero로 바꾼다는거죠. 그래서 Tibero에서도 해당 기능을 사용할 수 있는지 점검해 봤습니다.
 Tibero to MSSQL Gateway라는게 존재하는데, 이는 설정을 따로 요청해야 하고, 기존의 MS-SQL측에서 생성된 프로시져를 쓸 수가 없다는 단점이 있어서 일단 Openquery를 이용하는 방법을 시도해봤습니다.

 MS-SQL에서 Linked server를 생성하는 방법에는 Tibero ODBC driver를 이용해서 Data 원본(DSN)을 만든 뒤에 이를 이용해서 Linked server를 생성하는 법과, Tibero oledb driver(2가지를 지원하더군요.)를 이용해서 바로 Linked server를 생성하는 방법이 있습니다.

 1. select query 실행
  ODBC, OLEDB 두가지 방법으로 모두 이상없이 잘 됩니다.

2. update query 실행
 ODBC, OLEDB 두가지 방법으로 모두 오류가 발생합니다.

결론.
 Openquery를 이용할 때 Tibero로는 select query만 실행 가능하다는 문제가 있습니다.

'IT 기술 > Database' 카테고리의 다른 글

SQL Unplugged "괴물 이야기" 참석 후기  (0) 2010.06.14
국산 DBMS. TmaxData Tibero TAC의 좋은점과 아쉬운점.  (0) 2010.05.07
DBA의 고민 (1) 고성능, 고가용성 시스템 구축. 안정성과 성능의 두 마리 토끼를 잡아야 하는데... ㅜㅜ  (0) 2010.02.18
[DBA로 살기] DBA가 일을 할때.  (0) 2010.02.03
Tibero4 migration 모험기 (3) Index rebuild 기능  (1) 2010.02.01
 MS-SQL에서 Oracle에 있는 Data에 접근하기 위한 방법으로 Openquery라는걸 소개한 적이 있죠.
기본적으로 제공하는 기능이라 좋긴 하지만 동적으로 쿼리를 만들어서 결과값을 받아올수 없다는 얘기를 한 적이 있습니다. 그렇게 되면 WHERE절 조건을 정확하게 줄 수 없으니 오라클 DB에 부하를 많이 주게 되겠죠.
저도 이렇게 알고 서비스 중인 MS-SQL과 Oracle 사이에 Data 동기화 프로시져를 만들었었습니다.
그런데....

 웬걸...

 회사에서 사용중인 MS-SQL 2005에서 혹시나 하는 마음에 문자열 변수에 커서를 정의하는 문장까지 포함해서 동적으로 쿼리를 만들어 주고, 커서를 열었더니... 결과 값이 정상적으로 나오네요. 앗싸~ 가오리~

아래와 같은 방식으로 처리하니까 동적으로 쿼리문장을 만들어서 실행시키고 결과값을 커서에 받아서 사용할 수 있습니다.


DECLARE @QUERY_STRING VARCHAR(1000)
DECLARE @CODE        VARCHAR(30)
SET @CODE='SUPERCODE'
DECLARE @USERNAME VARCHAR(50)

SET @QUERY_STRING = 'DECLARE ORA_CUR CURSOR FOR
                     SELECT USERNAME
                         FROM OPENQUERY(ORA_DB, ''SELECT USERNAME
                                FROM USER_INFO
                               WHERE CODE='''''+@CODE+''''' AND USER_FLAG=''''N'''' '') '

EXEC (@QUERY_STRING)
OPEN ORA_CUR
FETCH NEXT FROM ORA_CUR INTO @USERNAME

WHILE @@FETCH_STATUS = 0
    BEGIN
    INSERT INTO dbo.USER_INFO(USERNAME)
    VALUES(@USERNAME)

    FETCH NEXT FROM ORA_CUR INTO @USERNAME
  END;

CLOSE ORA_CUR

DEALLOCATE ORA_CUR

'IT 기술 > Database' 카테고리의 다른 글

Tibero4 migration 모험기 (3) Index rebuild 기능  (1) 2010.02.01
Tibero4 migration 모험기 (2) tbAdmin에 대해서  (0) 2010.01.28
[이기종 DB간 Data 공유] MS-SQL에서 Oracle에 있는 Data 가져와서 동기화 맞추는 기능 구현  (0) 2010.01.07
Tibero4 migration 모험기 (1) 사용자 정의 함수 사용시 경험한 묘한 버그  (0) 2009.11.23
Orange for Tibero 2009를 실행시키기 위한 준비.  (0) 2009.09.22
 새로 도입하는 시스템이 MS-SQL을 사용한다고 해서 기존에 사용하던 Oracle에서 일부 Data는 초기에 한번, 일부 Data는 주기적으로 동기화를 맞춰주는 작업을 해야합니다. 초기에 한번 이전해 주는 작업은 MS-SQL의 SSIS라는 기능을 이용해서 넘겨주기로 했고, 기능 테스트도 이미 했죠. 문제는 주기적으로 Data의 동기화를 맞춰주는 작업인데요. DB 복제 솔루션을 이용하거나 ETL을 이용하면 쉽게 해결되겠지만, 예산이 없어서 보류하고요. 다른 방법을 찾아보기로 결정했었죠. 그래서 알아본게 다음의 두가지입니다.

1. Oracle Gateway를 이용한 이기종 DBMS간 Data 공유
2. MS-SQL의 Open Query를 이용한 이기종 DBMS간 Data 공유


 Oracle DB에 원천 Data가 있으니 변경된 내용이 생길때 바로바로 적용하기 위해서는 Oracle Gateway를 이용하는게 좋을것 같긴 한데요. 문제는 이건 제가 한번도 해보질 않아서 프로젝트 일정에 맞출수가 없을 것 같더군요. 그래서 MS-SQL에서 구현하기로 했습니다. Open query는 예전에도 써본적이 있거든요.
자꾸 이러면 실력이 늘지 않을텐데 큰일입니다. ^^;  자~ 이제 MS-SQL의 Open query를 이용해서 주기적으로 Data 동기화를 맞춰주는 기능을 구현해보도록 하겠습니다.

 제일 먼저 할 일은 MS-SQL 서버에 Oracle client를 설치하는 일입니다. 설치가 완료되면 Data를 가져올 Oracle server의 정보를 tnsnames.ora에 설정해주면 됩니다. 이제 Linked server를 설정해줄 차례입니다.

 Open Query를 사용하기 위해서 우선 Oracle과 Link를 걸어야 합니다. SQL Server Management Studio에서 [연결된 서버]라는 항목에 추가를 해주면 됩니다. 이때 [Oracle provider for OLEDB]를 선택해서 사용하시는게 좋습니다. MS에서 제공하는 OLEDB provider를 사용할 수도 있는데, 이건 뭔가 문제가 있어보이더군요. Open query에서는 테스트해보지 않았지만, SSIS에서 Oracle의 Data를 가져올때 데이터형을 좀 잘못 가져오는것 같더군요. tnsnames.ora에 설정된 이름과 사용할 Oracle의 계정 정보 등을 입력해서 linked server를 생성하시면 됩니다.

 이제 Open query에 대해서 얘기해볼까합니다. Open query를 이용하면 MS-SQL에서 Oracle DB에 insert, select, update, delete를 실행 할 수 있습니다.

USE TESTDB
GO

SELECT * FROM OPENQUERY(LINK_NAME, `SELECT * FROM 테이블이름`);


UPDATE OPENQUERY(LINK_NAME, `SELECT A FROM 테이블이름 WHERE 조건1`)
SET A='AA' WHERE 조건2;

DELETE OPENQUERY(LINK_NAME, `SELECT A FROM 테이블이름 WHERE 조건1`)
 위의 query문을 보시면 update문을 실행시키는데 select문을 실행시키고 set을 해주는 부분이 보일겁니다. 그리고 조건이 2개 붙은 부분이 보일겁니다. [조건2]가 붙는 이유는 [조건1]에 ms-sql의 프로시져에서 사용하는 변수를 적용할 수 없기 때문입니다. 변수로 처리하지 않고 문자열로 넣어서 처리할 수 있는 부분은 [조건1]에 다 넣어주고, 변수로 받아서 넣어줘야할 내용은 [조건2]에 넣어주면 됩니다. 아래에 프로시져 코드를 보시면 바로 이해하실 겁니다.

이제 배치작업을 위해서 해당 기능을 프로시져로 만듭니다. 제가 작성한 간단한 샘플 코드를 보여드리겠습니다.

USE [testdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [tdb].[user_sync]
AS

    DECLARE ora_cursor CURSOR FOR
    SELECT TRG_FLAG, USER_ID, USER_NAME, TEL, CEL, EMAIL, ZIP_CD, ADDR1, ADDR2
       FROM OPENQUERY(VOCA2, 'SELECT B.TRG_FLAG, B.USER_ID, A.USER_NAME, A.TEL, A.CEL, 
                                                               A.EMAIL, A.ZIP_CD, A.ADDR1, A.ADDR2
                                                     FROM USER_TEST A, USER_TRG B
                                                    WHERE B.USER_ID = A.USER_ID(+)  AND B.MIG_DATE IS NULL');

    OPEN ora_cursor

    DECLARE @TRG_FLAG        VARCHAR(1)
    DECLARE @USER_ID            VARCHAR(30)
    DECLARE @USER_NAME            VARCHAR(50)
    DECLARE @TEL            VARCHAR(50)
    DECLARE @CEL            VARCHAR(50)
    DECLARE @EMAIL        VARCHAR(100)
    DECLARE @ZIP_CD            VARCHAR(7)
    DECLARE @ADDR1        VARCHAR(100)
    DECLARE @ADDR2        VARCHAR(100)

    FETCH NEXT FROM ora_cursor INTO @TRG_FLAG, @ USER_ID , @USER_NAME, @USER_TEL, @USER_CEL, @USER_EMAIL, @ZIP_CD, @USER_ADDR1, @USER_ADDR2

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@TRG_FLAG = 'I')
            BEGIN
                INSERT INTO USER_TEST  ( USER_ID , USER_NAME, USER_TEL, USER_CEL, USER_EMAIL, ZIP_CD, USER_ADDR1, USER_ADDR2)
                VALUES(@USER_ID, @USER_NAME, @USER_TEL, @USER_CEL, @USER_EMAIL, @ZIP_CD, @USER_ADDR1, @USER_ADDR2);

                UPDATE OPENQUERY(VOCA2, 'SELECT MIG_FLAG, MIG_DATE, USER_ID FROM  USER_TRG  WHERE TRG_FLAG=''I'' AND MIG_FLAG=''N''')
                    SET MIG_FLAG='Y', MIG_DATE=GETDATE()
                    WHERE USER_ID=@USER_ID;
            END;
        ELSE IF (@TRG_FLAG = 'U')
            BEGIN
                UPDATE  USER_TEST    SET USER_NAME=@USER_NAME, USER_TEL=@USER_TEL, USER_CEL=@USER_CEL, USER_EMAIL=@USER_EMAIL, ZIP_CD=@ZIP_CD,
                                           USER_ADDR1=@USER_ADDR1, USER_ADDR2=@USER_ADDR2
                WHERE USER_ID=@USER_ID;

                UPDATE OPENQUERY(VOCA2, 'SELECT MIG_FLAG, MIG_DATE, USER_ID FROM  USER_TRG   WHERE TRG_FLAG=''U'' AND MIG_FLAG=''N''')
                    SET MIG_FLAG='Y', MIG_DATE=GETDATE()
                    WHERE USER_ID=@USER_ID;
            END;
        ELSE IF (@TRG_FLAG = 'D')
            BEGIN
                DELETE FROM  USER_TEST    WHERE USER_ID=@USER_ID;

                UPDATE OPENQUERY(VOCA2, 'SELECT MIG_FLAG, MIG_DATE, USER_ID FROM  USER_TRG   WHERE TRG_FLAG=''D'' AND MIG_FLAG=''N''')
                    SET MIG_FLAG='Y', MIG_DATE=GETDATE()
                    WHERE USER_ID=@USER_ID;
            END;

        INSERT INTO dbo.USER_TEST_LOG(REG_DATE, USER_ID, TRG_FLAG) VALUES(GETDATE(), @USER_ID, @TRG_FLAG);
        
    FETCH NEXT FROM ora_cursor INTO @TRG_FLAG, @USER_ID, @USER_NAME, @USER_TEL, @USER_CEL, @USER_EMAIL, @ZIP_CD, @USER_ADDR1, @USER_ADDR2 
    END;

    CLOSE ora_cursor                                        -- 커서 클로즈

    DEALLOCATE ora_cursor                              -- 커서 메모리 반환
GO




자 이제 배치작업으로 주기적으로 실행시키는 일만 남았네요.


'IT 기술 > Database' 카테고리의 다른 글

Tibero4 migration 모험기 (2) tbAdmin에 대해서  (0) 2010.01.28
[이기종 DB간 Data 공유] MS-SQL의 Openquery에 동적으로 쿼리 만들어서 결과값 받아오기  (0) 2010.01.28
Tibero4 migration 모험기 (1) 사용자 정의 함수 사용시 경험한 묘한 버그  (0) 2009.11.23
Orange for Tibero 2009를 실행시키기 위한 준비.  (0) 2009.09.22
IBM이 DB2 사용자 기반을 넓히려고 노력중인가 봅니다.  (0) 2009.08.10

+ Recent posts

티스토리툴바