![닫기 [x]](http://dw1.s81c.com/i/c.gif){kind=small}
이 기사에서는 DB2 9.7의 새 기능을 사용하여 데이터베이스 스키마에 대한 온라인 변경 작업을 수행하는 방법에 대해 설명한다. 온라인 변경 작업이란 변경 프로세스를 진행하는 동안에도 변경 중인 오브젝트가 읽기 및 쓰기 액세스가 가능한 상태로 유지된다는 것을 의미한다.
다음은 새 기능 중 일부 기능에 대한 설명이다.
- ALTER TABLE 명령문을 사용하여 온라인 상태에서 열의 이름을 바꿀 수 있다.
- OR REPLACE가 여러 CREATE 명령문에서 옵션으로 추가되었다.
- 오류가 허용되는 CREATE 지원이 보기 및 인라인 SQL 함수에 대해 추가되었다.
- ALTER COLUMN SET DATA TYPE 지원이 확장되었다.
- ADMIN_MOVE_TABLE 루틴을 사용하여 테이블을 온라인 상태에서 변경 및 이동할 수 있다.
이 기사의 대상 독자는 DB2 데이터베이스 관리자이므로 테이블 공간, 테이블, 열 등에 대한 기본 개념을 알고 있어야 한다.
이 기사의 예제를 사용하려면 DB2 9.7 for Linux, UNIX, and Windows가 있어야 한다. 참고자료 섹션에서 DB2 9.7 for Linux, UNIX, and Windows의 무료 시험판을 다운로드할 수 있는 링크에 대한 정보를 볼 수 있다.
DB2의 새로운 온라인 스키마 변경 기능을 보여 주는 예제를 사용하려면 먼저 필수 인프라로 사용할 샘플 데이터베이스를 작성해야 한다. 예제에서는 DB2 SAMPLE 데이터베이스를 사용한다. DB2 SAMPLE 데이터베이스를 아직 작성하지 않았으면 DB2 Information Center의 "The SAMPLE database" 기사를 참조하여 데이터베이스를 작성한다(참고자료 섹션에서 링크 정보 참조).
SAMPLE 데이터베이스를 작성한 후에는 다음 단계를 수행하여 예제에서 사용되는 필수 테이블과 데이터를 작성한다.
- 다음 명령을 사용하여 고객 정보를 시뮬레이션하는 시스템 카탈로그 기반 테이블을 작성한다.
Listing 1. CUSTOMER_INFO 테이블 작성하기CREATE TABLE CUSTOMER_INFO( customer_id INTEGER NOT NULL, first_name VARCHAR(128) NOT NULL, last_name VARCHAR(128), address_street VARCHAR(128), address_city VARCHAR(128), address_state VARCHAR(25), address_country VARCHAR(30), age VARCHAR(2), customer_type VARCHAR(10), CONSTRAINT customer_id_pk PRIMARY KEY (customer_id) ) IN USERSPACE1;
- 다음 명령을 사용하여 시스템 카탈로그의 더미 정보를 사용하는 데이터로 CUSTOMER_INFO 테이블을 채운다.
Listing 2. CUSTOMER_INFO 테이블 채우기INSERT INTO customer_info SELECT ROW_NUMBER() OVER () as customer_id , RTRIM(a.tabschema) as first_name, RTRIM(a.tabname) as last_name, CAST(a.colno AS VARCHAR(3)) || ' ' || RTRIM(a.colname) as address_street, RTRIM(a.tabname) as address_city, RTRIM(a.TYPENAME) as address_state, RTRIM(a.TABSCHEMA) as address_country, CAST(CAST(sysfun.rand()*50 AS INTEGER)+1 AS VARCHAR(2)) as age, CASE WHEN MOD(CAST(sysfun.rand()*50 AS INTEGER),3)=0 THEN 'New' ELSE 'Existing' END AS type FROM syscat.columns a ORDER BY sysfun.rand();
- 이 마지막 단계에서는 CUSTOMER_INFO 테이블을 기반으로 하는 보기와 함수를 작성한다.
오브젝트의 작성 순서에 주의를 기울여야 한다. 먼저 EXISTING_CUSTOMERS 보기를 작성한 후 이 보기에서 사용되는 FULL_NAME 함수를 작성한다. 이 순서가 중요한 이유는 순서를 지키지 않았을 경우 기본적으로 CREATE OR REPLACE VIEW 명령문을 실행할 때 SQL0440N 오류가 발생하면서 명령문이 실패하기 때문이다.
하지만 버전 9.7부터는 종속 오브젝트 누락과 같은 특정 유형의 오류가 있더라도 오브젝트가 작성되도록 DB2를 구성할 수 있다. 이 기능을 사용하면 CREATE 명령을 순서에 상관 없이 실행해도 되기 때문에 데이터베이스 오브젝트 작성, 설계 및 수정 작업을 쉽게 수행할 수 있다. 또한 새 보기 및 프로시저가 의존하고 있는 오브젝트를 작성하지 않은 채로 해당 보기 및 프로시저의 구문을 검증할 수도 있다.
이 기능을 사용하려면 다음 명령을 사용하여 AUTO_REVAL 동적 데이터베이스 구성 매개변수의 값을 DEFERRED_FORCE로 변경한다.
Listing 3. AUTO_REVAL 구성 매개변수 설정하기db2 update db cfg using AUTO_REVAL DEFERRED_FORCE
이 매개변수를 변경한 후 EXISTING_CUSTOMER 보기를 작성하면 SQL20480W 경고가 발생하면서 작성된 보기가 처음에는 유효하지 않은 것으로 표시된다. 하지만 나중에 이 보기를 사용할 때 보기가 의존하는 함수가 있으면 보기의 유효성이 자동으로 다시 검증된다.
다음 명령을 사용하여 새 보기와 함수를 작성한다. 이러한 명령에서는 함수, 프로시저, 보기, 모듈, 별칭, 트리거, 변수 및 별명에 사용할 수 있는 새 CREATE OR REPLACE 구문을 사용한다. 이름으로 알 수 있듯이 이 구문은 오브젝트를 작성하며 해당 오브젝트가 이미 있는 경우에는 오브젝트를 대체한다. 즉, 기존 오브젝트에 대해 이 구문은 DROP과 CREATE를 하나의 명령으로 결합하여 실행하는 동시에 오브젝트에 부여된 기존 권한을 그대로 유지한다.
Listing 4. 함수 및 보기 작성하기CREATE OR REPLACE VIEW existing_customers AS SELECT full_name(customer_id) AS full_name, address_city, address_state FROM customer_info WHERE customer_type='Existing'; CREATE OR REPLACE function full_name(p_customer_id INTEGER) RETURNS VARCHAR(100) return SELECT first_name || ', ' || last_name FROM customer_info WHERE customer_id=p_customer_id;
테이블 정의에 대한 온라인 수정과 관련하여 DB2 9.7에서는 다음과 같은 두 가지 사항이 개선되었다.
- 첫째, 테이블에 대한 워크로드가 실행 중인 동안 사용자를 중단시키지 않고서도 열 이름을 온라인 상태에서 바꿀 수 있다.
- 둘째, DB2 9.7에서는 기존 테이블의 열 데이터 유형을 변경하는 데 필요한 지원이 강화되었다.
다음 예제에서는 테이블을 완전히 액세스 가능한 상태로 유지하면서 ALTER TABLE 명령을 사용하여 열의 이름을 바꾸는 방법을 보여 준다.
Listing 5. 열 이름 바꾸기 예제
ALTER TABLE customer_info RENAME COLUMN age TO customer_age ; |
ALTER TABLE 명령문의 ALTER COLUMN SET DATA TYPE 옵션은 모든 호환 유형을 지원하도록 확장되었다. 예를 들어, INTEGER 데이터 유형으로 정의된 열을 VARCHAR 데이터 유형도 허용하도록 변경하거나 TIMESTAMP 데이터 유형을 DATE 데이터 유형으로 변경할 수 있다. 전체 호환 데이터 유형 목록을 보려면 DB2 Information Center의 "Casting between data types" 기사를 참조한다(참고자료 섹션에서 링크 정보 참조).
ALTER TABLE 작업을 ALTER COLUMN SET DATA TYPE 옵션과 함께 수행하는 동안 DB2는 전체 유효성 검증을 수행하여 열 데이터가 새 데이터 유형과 호환되는지 그리고 잘림, 오버플로우 및 기타 유형의 오류가 없는지 확인한다. 열 기본값도 새 데이터 유형을 따르는지 확인하기 위해 검증된다. 열 유형과 데이터 내용이 호환되면 데이터 유형이 성공적으로 변경되지만 그렇지 않은 경우에는 ALTER 명령이 오류를 리턴한다.
다음 예제에서는 customer_age 열의 데이터 유형을 VARCHAR(2)에서 SMALLINT로 변경하는 방법을 보여 준다.
Listing 6. 열 유형 변경하기
ALTER TABLE customer_info ALTER COLUMN customer_age SET DATA TYPE SMALLINT; |
대부분의 경우 ALTER SET DATA TYPE은 물리적 행 형식을 변경하므로 테이블 재구성이 필요하다. 다음과 같이 ADMIN_REVALIDATE_DB_OBJECTS 루틴을 사용하여 테이블 재구성이 필요한지 여부를 자동으로 결정할 수 있다.
Listing 7. 테이블 재검증
CALL ADMIN_REVALIDATE_DB_OBJECTS('TABLE', 'DB2INST1', 'CUSTOMER_INFO'); |
열 데이터 유형이 변경되는 동안 데이터베이스에 대한 쓰기 액세스가 가능하도록 해야 하는 경우에는 다음 섹션에서 설명하는 ADMIN_MOVE_TABLE 루틴을 사용할 수 있다.
DB2 9.7의 새 ADMIN_MOVE_TABLE 루틴을 사용하면 사용자가 테이블에 액세스할 수 있는 상태를 유지한 채로 자동으로 테이블 공간 간에 테이블을 이동하거나 열 데이터 유형 정의를 변경할 수 있다.
내부적으로 ADMIN_MOVE_TABLE은 소스 테이블의 사본을 작성한다. 이 데이터는 INSERT FROM CURSOR(기본값) 또는 LOAD(올바른 옵션이 설정된 경우)를 사용하여 복사된다. 테이블이 이동하는 동안 ADMIN_MOVE_TABLE 루틴은 소스 테이블의 모든 변경 사항을 추적하는 단계 테이블을 작성한다. 변경 사항은 소스 테이블에서 작성된 트리거를 통해 추적된다. 이동 작업이 완료될 때 ADMIN_MOVE_TABLE 루틴은 소스 테이블을 배타적 모드로 잠근 후 발생한 갱신 사항을 확인한 후 소스 테이블을 대상 테이블로 대체한다. 소스 테이블은 ADMIN_MOVE_TABLE 입력 매개변수에 따라 삭제 또는 유지된다.
ADMIN_MOVE_TABLE 루틴을 사용하면 테이블의 테이블 공간 위치뿐만 아니라 다차원 클러스터링, 파티셔닝 키, 범위 파티셔닝 및 열 데이터 유형도 변경할 수 있다. ADMIN_MOVE_TABLE은 소스 테이블에 정의된 트리거와 보기를 대상 테이블로 이동한다. 하지만 상위 또는 하위 외부 키의 복사가 아직까지 지원되지 않고 있으므로 소스 테이블이 RI 관계에서 상위 또는 하위 항목이었다면 테이블을 이동한 후에 외부 키 정의를 캡처하여 다시 작성해야 한다.
다음 예제에서는 SQL 명령문이 외부 키 정보를 캡처하고 DROP 명령문을 생성한다.
Listing 8. 외부 키에 대한 CREATE 및 DROP 명령
SELECT 'ALTER TABLE ' || RTRIM(a.tabschema)||'.'||RTRIM(a.tabname) ||
' ADD CONSTRAINT '||a.constname||
' FOREIGN KEY (' || fk_colnames || ') REFERENCES ' ||
RTRIM(reftabschema)||'.'||RTRIM(reftabname) ||
' ('||pk_colnames||') ON DELETE ' ||
CASE deleterule WHEN 'A' THEN 'NO ACTION' WHEN 'C' THEN 'CASCADE' WHEN 'N' THEN
'SET NULL' WHEN 'R' THEN 'RESTRICT' END || ' ON UPDATE ' ||
CASE updaterule WHEN 'A' THEN 'NO ACTION' WHEN 'R' THEN 'RESTRICT' END ||
CASE enforced WHEN 'Y' THEN ' ENFORCED ' WHEN 'N' THEN ' NOT ENFORCED ' END ||
' QUERY OPTIMIZATION '||
CASE enablequeryopt WHEN 'Y' THEN ' ENABLE ' WHEN 'N' THEN ' DISABLE 'END ||
';'
FROM syscat.references a, syscat.tabconst b
WHERE a.constname=b.constname;
SELECT 'ALTER TABLE ' || RTRIM(tabschema)||'.'||RTRIM(tabname) ||
' DROP FOREIGN KEY ' || constname || ';'
FROM syscat.references
WHERE (tabschema='DB2INST1' AND tabname='CUSTOMER_INFO') OR
(reftabschema='DB2INST1' AND reftabname='CUSTOMER_INFO'); |
다음 예제에서는 CUSTOMER_INFO 테이블을 새 테이블 공간으로 이동하는 방법을 보여 준다.
Listing 9. ADMIN_MOVE_TABLE 루틴으로 테이블 이동하기
CREATE TABLESPACE new_ts;
CALL SYSPROC.ADMIN_MOVE_TABLE
('DB2INST1', 'CUSTOMER_INFO',
'NEW_TS', 'NEW_TS', 'NEW_TS',
'', '', '', '', '', 'MOVE');
Result set 1
--------------
KEY VALUE
-------------------------------- ---------------------------
AUTHID DB2INST1
CLEANUP_END 2009-06-11-13.24.45.438000
CLEANUP_START 2009-06-11-13.24.45.407000
COPY_END 2009-06-11-13.24.44.641000
COPY_OPTS OVER_INDEX,ARRAY_INSERT
COPY_START 2009-06-11-13.24.43.829000
COPY_TOTAL_ROWS 6745
INDEX_CREATION_TOTAL_TIME 1
INDEXNAME CUSTOMER_ID_PK
INDEXSCHEMA DB2INST1
INIT_END 2009-06-11-13.24.43.797000
INIT_START 2009-06-11-13.24.43.250000
REPLAY_END 2009-06-11-13.24.45.250000
REPLAY_START 2009-06-11-13.24.44.641000
REPLAY_TOTAL_ROWS 0
REPLAY_TOTAL_TIME 0
STATUS COMPLETE
SWAP_END 2009-06-11-13.24.45.391000
SWAP_RETRIES 0
SWAP_START 2009-06-11-13.24.45.329000
VERSION 09.07.0000
21 record(s) selected. |
다음 예제는 조금 복잡하다. 이 예제에서도 동일한 CUSTOMER_INFO 테이블을 이동하지만 기본 옵션인 INSERT FROM CURSOR 옵션 대신 LOAD 옵션을 사용한다.
LOAD 옵션이 INSERT FROM CURSOR 옵션보다 빠르게 실행되는 이유는 다음과 같다.
- LOAD는 대상의 변경 사항을 레코드 단위로 기록하지 않는다.
- LOAD는 높은 수준의 병렬 CPU 및 I/O 처리 성능을 제공한다.
- LOAD는 대상 테이블에 대한 대용량 블록 쓰기를 수행한다. 이는 추가 I/O 효율성을 제공한다.
이 작업은 복구 가능한 작업이 아니므로 데이터베이스를 테이블 이동 완료 시간과 후속 백업 시간 사이의 특정 시점으로 복구해야 하는 경우에는 테이블이 유실될 수 있다. 데이터 유실을 방지하기 위해 KEEP 옵션을 사용할 수 있다. KEEP 옵션을 사용하면 ADMIN_MOVE_TABLE 루틴이 원본을 유지한다. 또한 LOAD를 복사 옵션으로 사용할 경우에는 FORCE 옵션을 사용해야 한다. FORCE 옵션 요구 사항은 LOAD가 복구 불가능한 작업이므로 복구를 원할 경우에는 백업을 작성해야 한다는 점을 상기시켜 주는 알림 기능을 수행한다.
Listing 10. LOAD 옵션으로 테이블 이동하기
CALL SYSPROC.ADMIN_MOVE_TABLE
('DB2INST1', 'CUSTOMER_INFO',
'NEW_TS', 'NEW_TS', 'NEW_TS',
'', '', '', '',
'KEEP, COPY_USE_LOAD, FORCE', 'MOVE')
Result set 1
--------------
KEY VALUE
-------------------------------- ----------------------------
AUTHID DB2INST1
CLEANUP_END 2009-06-11-13.36.43.360000
CLEANUP_START 2009-06-11-13.36.43.297000
COPY_END 2009-06-11-13.36.42.704000
COPY_OPTS OVER_INDEX,LOAD,WITH_INDEXES
COPY_START 2009-06-11-13.36.40.563000
COPY_TOTAL_ROWS 6745
INDEX_CREATION_TOTAL_TIME 0
INDEXNAME CUSTOMER_ID_PK
INDEXSCHEMA DB2INST1
INIT_END 2009-06-11-13.36.40.266000
INIT_START 2009-06-11-13.36.39.172000
ORIGINAL CUSTOMER_INFOAC61b#o
REPLAY_END 2009-06-11-13.36.43.125000
REPLAY_START 2009-06-11-13.36.42.704000
REPLAY_TOTAL_ROWS 0
REPLAY_TOTAL_TIME 0
STATUS COMPLETE
SWAP_END 2009-06-11-13.36.43.250000
SWAP_RETRIES 0
SWAP_START 2009-06-11-13.36.43.125000
VERSION 09.07.0000
22 record(s) selected. |
Listing 3의 예제는 테이블을 훨씬 빠르게 이동하고 원본 테이블을 CUSTOMER_INFOAC61b#o라는 이름으로 저장한다.
다음 예제에서는 테이블 데이터뿐만 아니라 인덱스 및 LOB에 대한 테이블 공간을 변경하는 방법을 보여 준다. 그리고 테이블의 특성을 변경하는 방법도 보여 준다. CUSTOMER_INFO 테이블은 인덱스와 LOB를 USERSPACE1 테이블 공간에 유지하며 테이블 데이터는 NEW_TS 테이블 공간으로 이동한다. 이 예제에서는 CUSTOMER_TYPE 열을 클러스터링 키로 사용하는 MDC(Multi Dimensional Clustering)를 사용하도록 테이블을 변환한 후 CUSTOMER_AGE 열의 데이터 유형을 VARCHAR(2)로 변경한다.
Listing 11. ADMIN_MOVE_TABLE 루틴으로 테이블 특성 변경하기
CALL SYSPROC.ADMIN_MOVE_TABLE(
'DB2INST1', 'CUSTOMER_INFO',
'NEW_TS', 'USERSPACE1', 'USERSPACE1',
'CUSTOMER_TYPE', '', '',
'customer_id INTEGER NOT NULL,
first_name VARCHAR(128) NOT NULL,
last_name VARCHAR(128),
address_street VARCHAR(128),
address_city VARCHAR(128),
address_state VARCHAR(25),
address_country VARCHAR(30),
customer_age VARCHAR(2),
customer_type VARCHAR(10)',
'', 'MOVE');
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------
AUTHID MAKSYMP
CLEANUP_END 2009-06-11-14.30.04.438000
CLEANUP_START 2009-06-11-14.30.04.422000
COPY_END 2009-06-11-14.30.03.750000
COPY_OPTS OVER_INDEX,ARRAY_INSERT
COPY_START 2009-06-11-14.30.03.219000
COPY_TOTAL_ROWS 6745
INDEX_CREATION_TOTAL_TIME 1
INDEXNAME CUSTOMER_ID_PK
INDEXSCHEMA DB2INST1
INIT_END 2009-06-11-14.30.03.125000
INIT_START 2009-06-11-14.30.02.250000
PAR_COLDEF customer_id INTEGER NOT NULL,
first_name VARCHAR(128) NOT NULL,
last_name VARCHAR(128),
address_street VARCHAR(128),
address_ci
REPLAY_END 2009-06-11-14.30.04.344000
REPLAY_START 2009-06-11-14.30.03.750000
REPLAY_TOTAL_ROWS 0
REPLAY_TOTAL_TIME 0
STATUS COMPLETE
SWAP_END 2009-06-11-14.30.04.407000
SWAP_RETRIES 0
SWAP_START 2009-06-11-14.30.04.360000
VERSION 09.07.0000
|
이 기사에서 살펴본 내용은 다음과 같습니다.
- CREATE OR REPLACE 구문을 사용하는 방법
- 오류가 허용되는 특정 데이터베이스 오브젝트를 작성하는 방법
- 열 이름을 바꾸고 열 데이터 유형을 변경하는 방법
- ADMIN_MOVE_TABLE 루틴을 사용하여 테이블을 이동하고 테이블 특성을 수정하는 방법
DB2 9.7의 새 기능인 온라인 스키마 변경 기능을 통해 DBA 및 애플리케이션 개발자는 계획된 중단 시간과 관련된 데이터베이스 중단 시간을 상당히 줄일 수 있다. 이러한 기능은 스키마 오브젝트에 대한 관리 작업에도 도움이 되기 때문에 전반적인 생산성도 향상된다.
교육
- developerWorks의
DB2 for Linux, UNIX and Windows 영역에서 DB2 9.7 관련 기술을 향상시키는 데 도움이 되는 참고자료를 다운로드할 수 있다.
- DB2 for Linux, UNIX, and Windows Information Center의 "The
SAMPLE database" 기사에서는 본 기사의 예제에 사용된 SAMPLE 데이터베이스를 작성하는 방법에 대한 지침을 볼 수 있다.
- DB2 for Linux, UNIX, and Windows Information Center의 "Casting
between data types" 기사에서는 ALTER TABLE 명령문의 ALTER COLUMN SET DATA TYPE 옵션에서 지원되는 호환 데이터 유형의 전체 목록을 볼 수 있다.
- DB2
for Linux, UNIX, and Windows Information Center에서 DB2를 사용하는 방법에 대한 추가 정보를 볼 수 있다.
- 기술 서점에서
다양한 기술 주제와 관련된 서적을 살펴보자.
제품 및 기술 얻기
- DB2
9.7 for Linux, UNIX, and Windows의 무료 시험판을 다운로드할 수 있다.
토론
Maksym Petrenko는 IBM Toronto Lab의 DB2 Beta Enablement Team 소속으로 애플리케이션을 우수한 기능의 최신 DB2 코드베이스로 이동하려는 초기 채택자를 지원하는 업무를 맡고 있다. 2001년부터 개발자, 기술 지원 분석가 및 랩 서비스 컨설턴트로서 DB2 관련 업무를 수행하고 있다. 고객에게 Windows, Linux 및 UNIX 플랫폼에서 DB2 데이터베이스와 관련된 설치, 구성, 애플리케이션 개발 및 성능 문제에 대한 지원을 제공하는 업무도 수행했다. 공인 DB2 Advanced Database Administrator 및 DB2 Application Developer이다.
태그
태그를 더 많이 보거나 적게 보기 위해 슬라이더 막대를 사용하십시오.
인기 태그는 특정 컨텐츠 존(예를 들어, 자바, 리눅스, WebSphere)의 최고 인기 태그를 보여줍니다.
내 태그는 특정 컨텐츠 존(예를 들어, 자바, 리눅스, WebSphere)의 귀하의 태그를 보여줍니다.
인기 기사 태그 |
내 기사 태그태그 목록으로 건너뛰기
인기 기사 태그 |
내 기사 태그