소개

Linux, UNIX 및 Windows용 IBM DB2 데이터베이스 정보 센터(참고자료의 링크 참조)에 설명된 대로 작성된 글로벌 임시 테이블은 DB2 9.7에 도입된 사용자 정의 임시 테이블의 새 유형이다. 이 릴리스 이전에는 DGTT(Declared Global Temporary Table)가 유일한 유형의 사용자 정의 임시 테이블이었다. DGTT와 CGTT의 주요 차이점은 CGTT의 정의가 DB2 카탈로그에 지속적으로 저장된다는 것이다. DGTT는 사용자 세션 동안에만 작성되고 존재한다. DGTT와는 달리 CGTT는 세션 중에 작성된 후 세션 종료 이후까지도 지속된다. 작성된 임시 테이블의 컨텐츠가 각 세션에 대해 개인용인 경우에도 정의는 모든 동시 세션에서 공유된다.

애플리케이션 세션에서는 CGTT와 DGTT를 사용하여 동시에 실행 중인 애플리케이션을 간섭하지 않고 반복된 참조 또는 조작을 위해 중간 결과 세트를 저장할 수 있다. CGTT 정의를 지속적으로 저장하면 다음과 같은 조작상 차이가 발생한다.

• 애플리케이션 세션이 작성된 임시 테이블을 정의한 후에는 동시에 실행 중인 세션에서 해당 테이블을 다시 정의하지 않아도 된다.
• SQL 함수, 트리거, 색인 및 보기에서 작성된 임시 테이블을 참조할 수 있다.

또한 모든 연결은 설치 스크립트를 사용하여 작성된 임시 테이블을 초기화하지 않아도 언제든지 작성된 임시 테이블을 참조할 수 있다. 연결은 해당 연결이 삽입한 행에만 액세스할 수 있다.

CGTT는 두 가지 주요 동기에 의해 개발되었다. 첫 번째 동기는 CGTT가 SQL 프로그래머에게 익숙한 일반 테이블과 비슷하게 동작할 뿐만 아니라 성능 향상을 기대할 수도 있다는 것이다. CGTT 테이블은 미리 정의할 수 있으며 사용자는 이러한 테이블을 일반 테이블과 매우 비슷하게 사용할 수 있다. 각 세션에 대한 데이터가 로컬 데이터이고(따라서 행 잠금이 필요하지 않고) 로그 옵션이 없을 경우 CGTT를 사용하면 성능이 향상될 수 있다.

CGTT 오퍼링의 두 번째 동기는 비DB2 임시 테이블을 DB2 임시 테이블로 변환하는 기능이 지원된다는 것이다. 따라서 변환 작업에 소요되는 비용을 줄일 수 있기 때문에 DB2로 쉽게 마이그레이션할 수 있다.

다른 DB2 제품군의 제품(예: DB2 for z/OS®)과 SQL 표준에서 작성된 임시 테이블을 지원한다.

다음 섹션에서는 CGTT에 대한 이해를 돕기 위해 CGTT의 사용 방법과 그 시기에 대한 기술적인 내용을 다룬 후 CGTT 구문을 설명하고 사용 방법을 보여 주는 작업 예제를 살펴본 다음 DGTT를 CGTT로 마이그레이션하는 베스트 프랙티스를 설명한다.

전제 조건

이 기사는 DB2 데이터베이스 개발자와 DB2 데이터베이스 관리자를 위한 것이며 DB2 및 스크립팅에 대한 기본 지식이 필요하다.

시스템 요구 사항

Linux®, UNIX® 및 Windows®용 DB2 9.7 Workgroup Server Edition 또는 Linux, UNIX 및 Windows용 DB2 9.7 Enterprise Server Edition

위로

기술적인 내용

이 섹션에서는 고려할 필요가 있는 작성된 임시 테이블의 동작에 대해 설명한다.

CGTT에는 로그 기능을 해제할 수 있는 옵션이 있다. not logged on commit delete rows 옵션을 설정하여 로그 기능을 해제하면 실제로 성능이 향상되는 효과를 얻을 수 있다. Not logged가 기본 동작이다. 이는 또한 커미트 또는 롤백이 테이블을 자르게 된다는 것을 의미한다.

모든 변경 사항을 로그하려면 logged on commit preserve rows를 사용한다. 그러면 커미트 명령문이 세션 데이터를 이전 커미트 또는 롤백 명령문으로 복원한다. 이 옵션은 일반 테이블과 같은 동작을 원하는 경우에 사용해야 한다.

CGTT는 일반 테이블보다 훨씬 더 효율적이다. 잠금 작업이 필요하지 않기 때문에(모든 데이터가 로컬 데이터임) CGTT가 일반 테이블보다 높은 성능을 제공한다. Not logged를 지정하면 성능이 훨씬 더 향상된다. 테이블을 자르려면 not logged 옵션을 사용한다.

색인이 CGTT 테이블에 작성되어 있을 경우 쿼리 컴파일러는 해당 색인을 고려하고 특정 세션의 runstat는 로컬 데이터만을 고려한다. 따라서 각 세션에서 다양한 액세스 계획을 사용할 수 있다.

아래 목록에서 언급되지 않는 한 CGTT는 일반 테이블과 동일하게 지원된다. 다음은 CGTT의 주요 특징이다.

• CGTT는 가져오기/내보내기를 지원하지만 로드를 지원하지 않는다.
• 권한 부여 및 권한 취소가 허용된다.
• 한 세션에 추가된 색인을 다른 세션에서 볼 수 있다. 하지만 세션 데이터가 새 색인과 충돌할 경우에는(고유 색인을 사용하는 행이 중복되어 서로 충돌할 경우) 데이터가 정정될 때까지 해당 세션에 대해 색인이 구체화되지 않는다. 이러한 혼란을 방지하려면 준비 단계에서 모든 색인을 정의하는 것이 바람직하다.
• CGTT에서는 고유 제한조건 또는 1차 키를 지원하지 않지만 고유 색인을 작성할 수 있다.
• CGTT에서는 제한조건을 지원하지 않는다. LIKE 절을 사용하여 CGTT 테이블을 작성하면(일반 테이블과 같은 CGTT 작성) 해당 명령문이 성공적으로 수행된다. 하지만 CGTT는 원래 테이블의 제한조건을 상속하지 않는다.
• 테이블 압축이 기본적으로 사용된다(옵션을 구입한 경우). 하지만 자주 액세스하는 데이터를 압축하면 최적의 성능을 얻는 데 지장을 줄 수 있다. CGTT 테이블의 압축을 비활성화할 수 있다.

CGTT에 대한 트리거를 작성할 수 있다. 또는 CGTT를 트리거의 본문에서 사용할 수도 있다. CGTT의 컨텐츠는 세션의 로컬 데이터이다. 다음과 같은 경우에는 DGTT 대신 CGTT를 사용해야 한다.

• 자주 작성되는 테이블인 경우
• 색인이 필요한 경우
• 보기에서 사용하려는 경우

CGTT는 다음과 같은 기능을 지원하지 않는다.

• rename, alter, reorg, refresh, nickname, comments 또는 lock 명령문
• RI
• 유형이 지정된 테이블, 범위 파티션 또는 MDC 테이블
• 구체화된 보기의 기본 테이블
• 보안 테이블의 기본 테이블
• XML 열

DB2에는 Oracle 구현에 비해 여러 장점이 있다. Oracle 구문은 DB2에서 작동하며 대부분의 경우 마이그레이션이 필요하지 않다. DB2에서는 다음과 같은 작업을 수행할 수 있다.

• 데이터에 대한 해시 파티션을 수행할 수 있다.
• 조건을 롤백할 수 있다. (이를 위해 CGTT에서는 유연성이 강화되었다.)
• 테이블 스페이스를 지정할 수 있다. 하지만 사용자 임시 테이블 스페이스여야 한다.
• 로그 여부를 선택할 수 있다.

CGTT는 매우 다양한 방법으로 다양한 상황에서 사용할 수 있다. 이제 CGTT 기능을 효과적으로 사용할 수 있는 몇 가지 예제를 살펴보자.

• 온라인 쇼핑 카트. 각 온라인 사용자에게 쇼핑 카트에 대한 CGTT가 있고, 이 CGTT는 사용자의 세션 동안 프로그래밍 방식으로 작성 및 제거되고 상품을 구매할 때 실제 테이블에 복사된다.
• 강의 등록. 각 학생에게 전체 과정 스케줄에 대한 선택 항목이 제공되며, 각 학생은 선택한 항목을 CGTT에 저장한다. 학생이 선택 사항을 승인하면 선택 사항이 모든 과정 선택 사항으로 구성된 실제 테이블로 옮겨진다.
• 사내 복리 후생 혜택 등록. 사원이 차기 회계연도에 받을 복리 후생 혜택을 선택한다(의료, 치과, 401k 등). 사원이 자신의 선택 사항을 확인하면 선택 사항이 CGTT에서 실제 테이블로 옮겨진다.

CGTT는 일반 테이블에 비해 관리하기가 쉽다. 왜냐하면 일반 테이블은 재구성이 필요하기 때문이며 게다가, 변경 사항을 로그하지 않으면 많은 트랜잭션을 수행하는 애플리케이션의 경우 로그 경합 문제가 줄어들 수 있기 때문이다. 또한 색인 및 로깅 옵션을 사용할 수 있기 때문에 DGTT로 쉽게 되돌릴 수도 있다.

위로

DB2 CGTT를 사용하는 방법

이 섹션의 코드 listing은 CGTT가 DB2에서 작동하는 방법을 보여 준다. 이러한 listing은 모두 DB2 API와 CLP, CLPPlus, Optim Database Administrator 및 Optim Development Studio를 비롯한 여러 DB2 도구에서 실행할 수 있으며 사용자의 고유 DB2 데이터베이스에서도 실행할 수 있다. 스크립트에 있는 dbname을 사용 중인 데이터베이스에 적합한 이름으로 변경하고 user1 및 user2 사용자를 작성한 후 테스트를 실행해야 한다. 이 섹션에서는 커미트 이후라도 다른 세션에서 데이터에 액세스할 수 없다는 것을 보여 준다.

Listing 1에서는 CGTT를 작성하는 SQL 구문을 보여 준다.

Create GLOBAL TEMPORARY TABLE <table_name> (
<column_name> <column_datatype>,
<column_name> <column_datatype>,
…  )
ON COMMIT [PRESERVE|DELETE] ROWS
[NOT LOGGED|LOGGED] ON ROLLBACK [PRESERVE|DELETE] ROWS
DISTRIBUTE BY HASH ( col1,..)
IN <tspace-name>;

Listing 2에서는 데이터베이스에 연결하고, 글로벌 임시 테이블을 삭제/작성하고, 별명을 작성하고, 모든 권한을 공용으로 부여하고, 데이터를 테이블을 삽입한 다음 테이블의 행 수를 선택하는 방법을 보여 준다.

다시 한번 말하지만 모든 listing에서 dbname을 사용자의 데이터베이스 이름으로 바꾸고 user1을 실제 사용자 이름으로 변경해야 한다.

|--------10--------20--------30--------40--------50--------60--------70--------80--------|
CONNECT TO dbname USER user1;

DROP TABLE user1.gtt_test;

CREATE GLOBAL TEMPORARY TABLE user1.gtt_test
   ( col1   number(5),
     Col2   date default sysdate)
ON COMMIT PRESERVE ROWS
NOT LOGGED ON ROLLBACK DELETE ROWS;

DROP PUBLIC ALIAS gtt_test;

CREATE PUBLIC ALIAS gtt_test FOR user1.gtt_test;

GRANT ALL ON gtt_test TO PUBLIC;

INSERT INTO gtt_test ( col1 ) select rownum from syscat.tables where rownum < 100;

COMMIT;

SELECT COUNT(*) FROM gtt_test;

Listing 2에서는 테이블 이름을 규정할 필요 없이 gtt_test로 참조할 수 있도록 새로 작성한 글로벌 임시 테이블에 대한 공용 별명을 작성한다. 또한 액세스 권한을 공용으로 부여하고, 데이터를 삽입한 다음 테이블에서 선택한다.

Listing 2를 실행하면 Listing 3과 같은 결과가 표시된다. Listing 3에서는 select 명령문의 결과를 보여 준다. select count(*) 명령문의 결과로 99가 표시된다.

>db2 -tvf gtt_1.sql


SELECT COUNT(*) FROM gtt_test

1

-----------

         99


  1 record(s) selected.

Listing 4의 코드를 다른 세션에서 실행해 보면 해당 데이터가 서로 다른 세션 간에 공유되지 않는다는 것을 알 수 있다. Listing 4에서는 다른 사용자로 데이터베이스에 연결한 후 작성된 글로벌 임시 테이블 gtt_test에 대해 selects count(*)를 실행한다. 이 테이블에는 별명이 있으므로 테이블의 소유자를 지정하지 않아도 된다.

|--------10--------20--------30--------40--------50--------60--------70--------80--------|
CONNECT TO dbname USER user2;

SELECT count(*) FROM gtt_test;

Listing 4를 실행하면 Listing 5와 같이 select count(*) 명령문의 결과가 0으로 표시된다.

db2 "select count(*) from db2inst1.gtt_test";

1

-----------

          0


  1 record(s) selected.

Listing 6은 Listing 2를 실행할 때 설정했던 첫 번째 세션에서 실행해야 한다. 이 코드를 실행하면 테이블의 처음 네 개의 행에 있는 모든 데이터가 선택된다.

|--------10--------20--------30--------40--------50--------60--------70--------80--------|
SELECT * FROM gtt_test where rownum < 5;

user1 세션에서 Listing 6을 실행한다. 이 코드는 작성된 글로벌 임시 테이블의 기본값이 작동하고 있고 모든 행에 날짜가 설정되어 있음을 보여 준다. 결과적으로 Listing 7과 같이 네 개의 행이 표시된다.

$ db2 "SELECT * FROM gtt_test where rownum < 5";

COL1    COL2

------- -------------------

     1. 2009-07-10-12.34.41

     2. 2009-07-10-12.34.41

     3. 2009-07-10-12.34.41

     4. 2009-07-10-12.34.41


  4 record(s) selected

다음 단계에서도 다시 한번 user1 세션에서 Listing 8을 실행한다. 이 listing은 작성된 글로벌 임시 테이블을 자르는 작업이 올바르게 작동하는 모습을 보여 준다.

|--------10--------20--------30--------40--------50--------60--------70--------80--------|
SELECT COUNT(*) from gtt_test;

TRUNCATE TABLE gtt_test;

SELECT * FROM gtt_test where rownum < 5;

이 코드는 테이블의 모든 내용을 선택하고 자른 후 다시 한번 테이블의 모든 내용을 선택하여 데이터가 삭제되었음을 보여 준다. 직접 실행한 결과가 Listing 9와 같은지 확인한다.

db2inst1@mclaren-laptop:/home/mclaren/sql$ db2 -tvf gtt_4.sql

SELECT COUNT(*) from gtt_test

1
-----------
         99



  1 record(s) selected.



TRUNCATE TABLE gtt_test

DB20000I  The SQL command completed successfully.



SELECT * FROM gtt_test where rownum < 5


COL1    COL2

------- -------------------

  0 record(s) selected.

위 결과에서 볼 수 있듯이 데이터가 올바르게 잘라졌다.

위로

DGTT(Declared Global Temporary Table)를 CGTT(Created Global Temporary Table)로 변환하기

DB2는 DGTT(Declared Global Temporary Table)와 CGTT(Create Global Temporary Table)를 지원한다. 두 임시 테이블 모두 세션별 데이터를 지원하는 데 사용된다. 일부 프로그래밍 모델에서는 DGTT를 CGTT로 변환할 경우 각 사용자 세션을 시작할 때 임시 테이블을 선언하지 않아도 된다. CGTT는 지속적이므로 시스템 설정 동안 모든 세션에서 작성 및 공유할 수 있다.

CGTT에 비해 DGTT에는 다음과 같은 제한 사항이 있다.

• DGTT는 세션에서 선언된다. 테이블 정의가 해당 세션에만 지속적이다.
• DGTT는 시스템 카탈로그에 있지 않다. 따라서 DGTT를 참조하는 모든 명령문은 사용하기 전에 컴파일해야 한다. CGTT를 사용하면 기본적으로 테이블 설정 프로세스가 세션 외부에서 수행된다.
• DGTT에는 고정된 스키마 세션이 있는 반면 CGTT는 일반 테이블과 같이 스키마 규칙을 따른다.

Listing 9에서는 선언된 임시 테이블의 예를 보여 준다. 이후 listing에서 이 DGTT를 CGTT로 변환할 것이다.

declare global temporary table dgttbase1
           ( B1_c1	integer,
             B1_C2	varchar(20),
	     B1_c3      varchar(20)
           )  on commit preserve rows;
insert into SESSION.dgttbase1 values (1,'R2_c1 is 1', 'inserted row');
insert into SESSION.dgttbase1 values (2,'R2_c1 is 2', 'inserted row');
connect reset;

connect to dbname;
insert into SESSION.dgttbase1 values (3,'R2_c1 is 3', 'inserted row');
select * from SESSION.dgttbase1;
connect reset;

SESSION.dgttbase1 테이블은 두 번째 세션에서 보이지 않는다. 두 번째 세션에서 dgttbase1을 사용하려면 해당 세션에서 이 테이블을 다시 작성해야 한다. Listing 11에서는 명령의 출력을 보여 준다. 세션이 종료된 후에는 DGTT가 더 이상 존재하지 않는다.

declare global temporary table dgttbase1 ( B1_c1      integer,
                                           B1_C2      varchar(20),
                                           B1_c3      varchar(20)
                                         )  on commit preserve rows
DB20000I  The SQL command completed successfully.

insert into SESSION.dgttbase1 values (1,'R2_c1 is 1', 'inserted row')
DB20000I  The SQL command completed successfully.

insert into SESSION.dgttbase1 values (2,'R2_c1 is 2', 'inserted row')
DB20000I  The SQL command completed successfully.

onnect reset
DB20000I  The SQL command completed successfully.

connect to dbname

insert into SESSION.dgttbase1 values (3,'R2_c1 is 3', 'inserted row')
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "SESSION.DGTTBASE1" is an undefined name.  SQLSTATE=42704

select * from SESSION.dgttbase1
SQL0204N  "SESSION.DGTTBASE1" is an undefined name.  SQLSTATE=42704

connect reset
DB20000I  The SQL command completed successfully.

Listing 12에서는 DGTT 테이블을 CGTT로 변환하는 방법을 보여 준다. CGTT 구문은 DGTT 구문과 매우 유사하다. DGTT와는 달리 두 번째 세션 동안 cgttbase1의 정의가 여전히 유효하다.

Connect to <dbname>
create global temporary table cgttbase1
           ( B1_c1	integer,
             B1_C2	varchar(20),
             B1_c3      varchar(20)
           )  on commit preserve rows;
connect reset;

insert into cgttbase1 values (1,'R2_c1 is 1', 'inserted row');
insert into cgttbase1 values (2,'R2_c1 is 2', 'inserted row');

Connect reset;
Connect to <dbname>
insert into cgttbase1 values (3,'R2_c1 is 3', 'inserted row');
insert into cgttbase1 values (4,'R2_c1 is 4', 'inserted row');
select * from cgttbase1;
connect reset;

작성된 CGTT 테이블 cgttbase1은 이후 모든 세션에 지속적으로 유지된다.

Connect to dbname
create global temporary table cgttbase1 ( B1_c1     integer,
                                          B1_C2     varchar(20),
                                          B1_c3     varchar(20)
                                        )  on commit preserve rows
DB20000I  The SQL command completed successfully.
connect reset


connect to dbname
insert into cgttbase1 values (1,'R2_c1 is 1', 'inserted row')
DB20000I  The SQL command completed successfully.

insert into cgttbase1 values (2,'R2_c1 is 2', 'inserted row')
DB20000I  The SQL command completed successfully.
Connect reset

Connect to dbname
insert into cgttbase1 values (3,'R2_c1 is 3', 'inserted row')
DB20000I  The SQL command completed successfully.
insert into cgttbase1 values (4,'R2_c1 is 4', 'inserted row')
DB20000I  The SQL command completed successfully.

select * from cgttbase1

B1_C1       B1_C2                B1_C3
----------- -------------------- --------------------
          3 R2_c1 is 3           inserted row
          4 R2_c1 is 4           inserted row

  2 record(s) selected.
connect reset

위로

결론

CGTT(Created Global Temporary Table)은 다음과 같은 새 기능을 제공하는 DB2 9.7의 새 기능이다.

• 색인 지원
• 트리거 지원
• 보기에서 사용할 수 있도록 지원
• 지속적 정의
• 로깅 옵션
• 해시 파티션 옵션
• 롤백 유연성

CGTT는 지속적인 글로벌 정의를 제공하므로 시작 시에 테이블 정의를 선언하지 않아도 모든 사용자 세션에서 동일한 테이블 정의에 액세스할 수 있다. 일부 기존 DB2 애플리케이션의 경우 DGTT를 CGTT로 변환하여 프로그래밍 모델을 단순화할 수 있다. 예를 들어, 보기 및 트리거 지원을 사용할 수 있으면 이러한 옵션을 사용하여 애플리케이션을 좀 더 단순하게 작성할 수 있다. CGTT에는 유지보수 작업이 많이 필요하지 않기 때문에 일반 테이블보다 쉽게 관리할 수 있다. 색인의 CGTT 지원을 사용하면 좀 더 쉽게 성능을 조정할 수 있다. 그리고 새로운 로깅 커미트 옵션이 지원되므로 CGTT는 일반 및 DGTT 테이블에 비해 한층 더 향상된 유연성과 성능을 제공한다.

또한 CGTT는 애플리케이션을 다른 데이터베이스에서 DB2로 마이그레이션할 때 유용하다. 예를 들어, Oracle 임시 테이블은 DB2 CGTT로 쉽게 채택할 수 있다.

CGTT에는 여러 가지 장점이 있다. 이제 자신의 애플리케이션에서 CGTT를 충분히 활용할 수 있을 것으로 기대한다.

참고자료

교육

• developerWorks의 DB2 for Linux, UNIX and Windows 영역에서 DB2 9.7 기술 향상에 도움이 되는 여러 가지 리소스를 볼 수 있다.
  
  
• DB2 9.7 for Linux, UNIX and Windows Information Center에서 DB2 제품군 및 기능과 관련 WebSphere Information Integration 제품 및 기능을 사용하는 방법에 대한 자세한 설명을 볼 수 있다.
  
  
• developerWorks Information Management 영역: Information Management에 대한 추가 정보를 제공한다. 기술 자료, 사용법 기사, 교육, 다운로드, 제품 정보 등을 찾아볼 수 있다.
  
  
• developerWorks 기술 행사 및 웹 캐스트를 통해 최신 정보를 얻을 수 있다.
  
  

제품 및 기술 얻기

• IBM DB2 for Linux, UNIX and Windows에서 무료 시험판 버전의 DB2 for Linux, UNIX and Windows를 다운로드할 수 있다.
  
  
• IBM DB2 Express-C에서 DB2 Express-C를 무료로 다운로드할 수 있다.
  
  
• developerWorks에서 직접 다운로드할 수 있는 IBM 시험판 소프트웨어를 사용하여 후속 개발 프로젝트를 구현해 보자.
  
  

토론

• developerWorks 포럼 & 블로그와 My developerWorks community에 참여하자. 이러한 커뮤니티에서는 사용자의 개인 프로파일과 사용자 정의 홈 페이지를 통해 관심을 가지고 있는 developerWorks의 여러 주제를 추적할 수 있으며 다른 developerWorks 사용자들과 의견을 나눌 수도 있다.
  
  

필자소개

아티클 순위

의견

위로

목차

• 소개
• 기술적인 내용
• DB2 CGTT를 사용하는 방법
• DGTT(Declared Global Temporary Table)를 CGTT(Created Global Temporary Table)로 변환하기
• 결론
• 참고자료
• 필자소개
• 의견