> 자료실 > News Letter
 
Download
News Letter
Site Link
XpertMon 뉴스레터 60호 - DB2 9.7 새로운 기능
2010/02/10 14:49 15047

DB2 9.7 새로운 기능 자율 트랜잭션(Autonomous Transactions)

 

1. 소개

 DB2 V9.7에 새로이 추가된 자율 트랜잭션에 대해 알아 보는 시간을 가져 보도록 하겠습니다.

자율 트랜잭션은 DB 관리자 나 개발자라면 충분히 매력적이라고 느껴질 수 있습니다.

 예제를 통한 자율 트랜잭션 동작을 알아 볼 수 있도록 하였으며, 실습을 위해선 DB2 V9.7 LUW 제품이 필요합니다.

 

2. 자율 트랜잭션에 대한 소개

 자율 트랜잭션은 호출자의 언커미트된 변경 결과에 영향을 받지 않는 자신 만의 COMMIT, ROLLBACK 범위를 가지고 있습니다. 덧붙여 말하면, 요청된 세션의 COMMIT, ROLLBACK이 아닌 자율 트랜잭션 내의 요청에 의해 확정을 짓게 됩니다.

 

 요청 중인 세션에 대해서는 요청된 세션의 제어권이 넘어가는 시점까지 유지가 되지만, 자율 트랜잭션 상에서는 실행 중인 세션에 대해 동시적인 사용이 가능합니다.

 

3. 자율 트랜잭션 만들기

 DB2 안에서의 자율 트랜잭션은 자율 프로시저에 의해 제공됩니다. CREATE PROCEDURE 문의 AUTONOMOUS 키워드를 통해 자율 프로시저를 생성할 수 있습니다. [ 1. 참고]

1. CREATE PROCEDURE 작성 예

CREATE OR REPLACE your_procedure_name

LANGUAGE SQL

AUTONOMOUS

BEGIN

                do autonomous work ;

END

 

 자율 프로시저는 트랜잭션의 독립된 요청에 의해 자신만의 세션에서 수행이 됩니다. 정상 자율 프로시저의 경운 묵시적으로 comit이 수행되며, 비 정상인 경우 rollback이 수행 됩니다. 이는 트랜잭션의 수행과는 무관합니다.

 

4. 실 사용 예

 [시나리오] B 은행에서는 테이블에 포함된 고객 정보가 처리 되는 것에 대해 로그가 남기를 원하고 있습니다. 그래서 B 은행 어플리케이션 개발자는 고객 데이터를 조회하는 인터페이스에 대해 스토어드 프로시저로 제공하였습니다. 이 스토어드 프로시저에는 테이블에 대한 정보를 조회하는 사용자ID, 쿼리에 사용된 고객 번호, 조회 요청된 시간이 로깅 되도록 만들어졌습니다.

 

 

Ø     실습 시 유의 사항

l     데이터베이스에 연결이 되어 있어야 함.

l     Auto commit off 상태로

l     모든 문장은 파일로 만들어 수행하도록 하면 문장의 종결 문자는 ‘%’를 사용

l     명령은 ‘DB2 CLP(Command Line Processor) 상에서 발행하며, 다음과 같은 CLP 명령문을 이용합니다.

db2 +c –td% -f <파일명>

 

4.1. 테이블 작성

 필요 테이블에 대한 정의를 합니다. 한 개의 테이블에는 고객에 대한 정보를 가지고 있고, 다른 테이블에는 고객 정보 조회 로그를 저장합니다. [ 2. 참고]

2. 테이블 DDL

   DROP TABLE customerSensitiveInfo %

   CREATE TABLE customerSensitiveInfo (

           customerAccountNumber    integer,

           amountOverdue             integer) %

 

   DROP TABLE log_table %

   CREATE TABLE log_table (

           queryingEmployeeID        varchar(100),

           customerAccNumber        integer,

           when                      timestamp) %

 

   COMMIT %

 

다음으로 고객 정보에 대한 접근 시 log_table에 기록하는 프로시저를 생성합니다. [ 3. 참고]

3. LOG 테이블 데이터 기록 프로시저

CREATE OP REPLACE PROCEDURE log_query

(in queryingEmployee varchar(100), in accNumber integer, in when timestamp)

LANGUAGE SQL

BEGIN

             Insert into log_table values (queryingEmployee, accNumber, when) ;

END %

 

COMMIT %

 

B 은행 어플리케이션 개발자는 get_AmountOverdue 프로시저를 통해 customerSeneitiveInfo 테이블로 부터 데이터를 조회해 오면서 이 조회 기록을 남기기 위해 log_query 프로시저를 실행하도록 하였습니다. [ 4. 참고]

4. get_AmountOverdue 프로시저

   CREATE OR REPLACE PROCEDURE get_AmountOverdue

   (in accountNumber integer, out overdue integer)

   LANGUAGE SQL

   BEGIN

         DECLARE due integer ;

         DECLARE currentTime timestamp ;

 

         SET currentTime = CURRENT TIMESTAMP ;

 

         CALL log_query(CURRENT USER, accountNumber, currentTime) ;

 

         SELECT amountOverdue INTO due FROM customerSensitiveInfo

                WHERE customerAccountNumber=accountNumber ;

 

         SET overdue=due;

   END %

 

   COMMIT %

 

Get_AmountOverdue 인터페이스가 만들어 졌다면 customerSensitiveInfo 테이블에 가상의 고객 정보를 입력하도록 하겠습니다. [ 5. 참고]

5. 고객 데이터 입력

   INSERT INTO customerSensitiveInfo VALUES (123435, 10000), (12346, 20000) %

   COMMIT %

 

입력된 고객 데이터가 잘 들어 갔는지 조회해 보겠습니다. [그림 1. 참고]

그림 1 . CustomerSensitiveInfo



 

이젠 get_AmountOverdue 프로시저를 통해 ‘12345’ 고객에 대한 정보를 조회 해 보도록 하겠습니다. 조회 후 바로 ROLLBACK 명령이 발행 되었을 때 어떤 결과가 나오는지 조의 깊게 보시기 바랍니다. [그림 2. 참고]

그림 2. 고객 정보 조회 및 로그 기록 확인



 

고객 정보 조회 시 ROLLBACK이 일어난 경우 log_table에 조회 기록이 남지 않은 것을 확인 하셨을 겁니다. 이젠 log_query 프로시저에 AUTONOMOUS 키워드를 사용하여 자율 프로시저로 재 생성 해 보겠습니다. [ 6. 참고]

6. 자율 프로시저로 만들어진 log_query 프로시저

CREATE OP REPLACE PROCEDURE log_query

(in queryingEmployee varchar(100), in accNumber integer, in when timestamp)

LANGUAGE SQL

AUTONOMOUS

BEGIN

             Insert into log_table values (queryingEmployee, accNumber, when) ;

END %

 

COMMIT %


고객 정보를 다시 한번 조회 해 보도록 하겠습니다. [그림 3. 참고]

그림 3. AUTONOMOUS 사용 시 log_table 기록 반영




고객 정보를 읽고 rollback 하는 트랜잭션이 발행 되었음에도 log_table에는 commit 이 되어 있는 것을 확인 할 수 있습니다. 이 같은 기능을 이용한다면 커밋 되지 않는 기록에 대해서라도 데이터 접근에 대한 기록을 남길 수가 있습니다.

목록 글쓰기 수정