IBM에서는 V9.5부터 DB2_COMPATIBILITY_VECTOR 라는 레지스트리 변수를 통하여 ORACLE의 특수 기능을 동일하게 사용하도록 지원하기 시작하였다.

ORACLE에서 DB2로 object들을 변환 작업할 때, 도움이 많이 되지만 DBMS가 다른 만큼 동일하게 작용하지 않는 부분이 존재한다. 개인적으로는 “오라클 호환성” 기능은 ORACLE 적인 SQL 등을 내부적으로 DB2 기능으로 mapping 혹은 변환시켜주는 기능이지 “Oracle SQL”을 직접 수행하는 기능은 아니라고 생각하고 있다.

보통은 DB2 V9.7에서 오라클 호환성을 사용하기 위해서는 레지스트리 변수를 DB2_COMPATIBILITY_VECTOR=ORA 로 설정을 한 후 DB를 생성하도록 안내를 한다. 면밀하게 살펴보지는 않았으나 ORACLE의 시스템 관리 뷰들의 생성 유무 차이가 있다.

일반적인 기능은 DB 생성 후에도 “오라클 호환성 변수”를 적용해도 (db2 restart는 필요함) 되는 것으로 알려져 있는데, 다음과 같이 “미묘한 차이”가 발생을 한다.

1. 오라클 호환성을 적용한 후 DB 생성한 경우

테이블 생성
> db2 "create table t1(c1 date, c2 timestamp)"

테이블 DDL 생성
> db2look -d test -e -z "INST97" -t "T1"

결과 확인

CREATE TABLE "INST97 "."T1" (

"C1" TIMESTAMP(0) ,

"C2" TIMESTAMP )

IN "USERSPACE1" ;

테이블 삭제

db2 drop table t1

 

오라클 호환성 제거

db2set DB2_COMPATIBILITY_VECTOR=

db2stop

db2start

 

테이블 생성

db2 "create table t1(c1 date, c2 timestamp)"

 

테이블 DDL문 생성

db2look -d test -e -z "INST97" -t "T1"

 

결과 확인

CREATE TABLE "INST97 "."T1" (

"C1" TIMESTAMP(0) ,

"C2" TIMESTAMP )

IN "USERSPACE1" ;

db2 "select * from t1"

C1                           C2

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

2011-10-26-16.47.38   2011-10-26-16.47.38.548792

  테스트 결과에서 보듯이, date 값이 timestamp(0) 으로 변환됨을 알 수 있다.

반면, 오라클 호환성을 적용하지 않고 DB를 생성한 경우 date 컬럼 속성은 변경되지 않았다.

 

2. 오라클 호환성을 적용하지 않고 DB 생성 후 오라클 호환성 적용한 경우

오라클 호환성 적용
db2set DB2_COMPATIBILITY_VECTOR=ORA

db2stop
db2start

테이블 생성
db2 "create table t1(c1 date, c2 timestamp)"

테이블 DDL 생성
db2look -d test -e -z "INST97" -t "T1"

CREATE TABLE "INST97 "."T1" (

"C1" DATE ,

"C2" TIMESTAMP )

IN "IBMDB2SAMPLEREL" ;


데이터 값 조회
db2 "select * from t1"

C1              C2

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

2011-10-26   2011-10-26-16.57.49.963273

오라클 호환성을 적용하지 않고 DB 생성한 후에는 오라클 호환성 적용과 상관없이 date 형변환은 발생하지 않았다.

만일, 오라클 호환성을 DB를 생성한 상태에서 date 컬럼의 형 변환 막을 방법은 없을 것 같다. 다만 Application에서 값을 가공 처리할 수는 있을 것 같다.

개인적으로는 오라클 호환성을 적용한 경우, SQL 해석기를 DB2로 할 것인지, Oracle로 할 것인지 사용자가 선택할 수 있는 장치가 마련되어야 하지 않은가 하는 생각이 든다.

IBM 기술지원센터에 문의를 해 보지 않아서 bug 여부는 확인하지 않았다.

'Note' 카테고리의 다른 글

[관리] Application에서 실행 중인 SQL 확인  (0) 2012.03.12
[관리] Windows 작업스케줄러를 이용한 DB2 백업 자동화  (0) 2012.02.22
[관리] DB2 설정정보 백업방법과 그 활용  (0) 2012.01.09
[SQL] 집계함수 listagg()  (0) 2012.01.05
[관리] DB2가 사용하는 메모리 확인은 어떻게?  (0) 2011.12.26

DB2에서 백업은 데이터베이스 범위에서 이뤄진다. DB보다 큰 범위인 인스턴스 범위에서의 백업은 db2 get dbm cfg 나 db2set –all 등을 통해서 백업받는게 보통인데, 버전이 올라갈 수록 DB 윗단의 백업으로서 사용할만 해지는 것 같다.(추출되어지는 정보가 많아짐)

1. db2rspgn

DB2 식 용어로는 “응답 설치” 방법으로 일컬어지면서 등장하는 “응답파일 생성 도구”이다. “응답 파일 설치는” 쉽게 말해 “묻지마 설치”라고 표현할 수 있을 것 같다. 위 명령어를 사용하면 DB2 설치에서 OS 계정 생성, DB2 설정, DB 생성까지 일괄 실행할 수 있는 파일이 생성된다.

(많진지 오래되서 기억이 가물해졌지만) oracle도 인스턴스 및 DB 생성 시 “설정 파일”을 이용해서 생성하는 법이 있었던 것 같은데 그것과 비슷하다고 하지 않을까 싶다.

설치에 필요한 모든 정보들 파일에 담아 놓고, “어떻게 설정해 놓느냐”에 따라 한번의 실행으로 더이상 손댈 필요없이 설치 작업이 진행된다.

이런 설치는 보통 HADR이나 DPF구성 시, 여러 머신에 동일하게 설치할 때 사용하면 좋지 않을까 싶다.

명령어

db2rspgn –d 생성경로 –i 인스턴스명

위 명령어를 수행하면 2개의 파일이 생성된다. (rsp 파일, ins 파일)

DB 생성 및 설정작업 후 응답 파일을 생성하면 이와 관련한 설정값도 다 추출되서 응답 파일이 저장된다.

 

설치 명령어

설치이미지/db2setup –r 응답파일경로/응답파일.rsp

PoC나 BMT 작업으로 여러 대 설치를 해야될 일이 있어서 활용을 해 보았는데, 약간 불편한 사항이 있어서 계속 써지지는 않았다.

추출 후, rsp 파일에서 “계정 비밀번호 설정” 과 라이센스 조항 “승인” 설정을 해 주어야 한다.

이외 별도의 파일시스템(디렉토리 및 파티션)에 사용자 계정이나 DB를 생성한 경우, 그 접근할 수 있도록 사전에 소유권 변경작업이 필요하다.

 

2. db2cfexp

db2repgn의 작업은 db2cfexp 도구와 겹치는 부분이 존재한다.

configuration 을 export하는 도구로서 수행 시 db2set 과 dbm cfg 변수값들이 추출되어 저장된다.

추출 옵션이 3가지 (backup, maintain, template) 존재하지만 backup을 덮어놓고 쓰는 편이다.

 

명령어

db2cfexp 백업파일명 backup

백업파일명은 임의적이고, 실행 위치에 파일이 생성된다.(인스턴스 계정으로 수행)

관리 용으로 설정(profile)작업을 백업받는 용도로 사용할 수도 있지만, db2 fixpack 적용 시 백업 후,인스턴스 업데이트 후 설정 값을 다시 적용하고자 할 때 사용할 수 있겠다.

 

적용

db2cfimp 백업파일명

'Note' 카테고리의 다른 글

[관리] Windows 작업스케줄러를 이용한 DB2 백업 자동화  (0) 2012.02.22
[SQL] Oracle 호환성에 따른 date 값의 차이점  (0) 2012.02.22
[SQL] 집계함수 listagg()  (0) 2012.01.05
[관리] DB2가 사용하는 메모리 확인은 어떻게?  (0) 2011.12.26
[관리] 데이터베이스 크기 확인  (0) 2011.12.26

위 함수는 V9.7 FixPack4에서 새롭게 소개된 함수이다. 특정 컬럼 기준으로 그룹의 문자열 값 세트를 하나의 문자열로 집계하도록 하는 기능을 한다.

고객사쪽에서 문의가 와서 고민하다 위 함수가 생각이 나서 소개를 해 주었는데, 나름 의미가 있을 듯 해서 블로그에도 남겨 본다.

저장된 데이터

C1          C2          C3
----------- ----------- ---
          1           1 A
          1           1 B
          1           1 C
          1           2 D
          1           3 E

 

원하는 결과값

C1          C2          C3_SUM
----------- ----------- ------------
          1           1 A,B,C
          1           2 D
          1           3 E

 

LISTAPP() 함수를 쓰면 다음과 같이 간단한 SQL로 구현된다.

select c1,
         c2,
         substr(listagg(c3,',') within group(order by c3),1,12) as c3_sum
from t1
group by c1, c2

결과값

C1          C2          C3_SUM
----------- ----------- ------------
          1           1 A,B,C
          1           2 D
          1           3 E

 

만일 V9.7 FixPack4 미만이라 사용할 수 없는 경우라면?

본 SQL 경우에는 3개의 문자를 붙이는 경우가 최대라고 가정하고 구현된 SQL이다. Stored Procedure 나 사용자 함수(UDF)를 만들어서 구현한다면 조금은 간단한 SQL 되지 않을까 싶다.

select a.c1, a.c2, a.c5||','|| b.c3 c3
from t1 b,
(select a.c1, a.c2, a.c3, t1.c3 c4, a.c3 || ',' || t1.c3 c5
from t1,
    (select a.c1, a.c2, b.c3
           from (select c1,c2,count(c3) cnt from t1 group by c1, c2 having count(c3) > 1 ) a,
                (select c1,c2,c3 from t1)b
     where a.c1=b.c1 and a.c2=b.c2 fetch first 1 rows only) a
where a.c1 = t1.c1 and a.c2 = t1.c2 and a.c3 <> t1.c3  fetch first 1 rows only) a
where a.c1=b.c1 and a.c2=b.c2 and b.c3 <> a.c3 and b.c3 <> a.c4

union all

select c1, c2, max(c3) c3 from t1 group by c1,c2 having count(c3) = 1

결과값

C1          C2          C3
----------- ----------- -----------
          1           2 D
          1           3 E
          1           1 A,B,C

  3 record(s) selected.

 

테스트한 table 및 insert 문

db2 "create table t1 (c1 integer, c2 integer, c3 varchar(3))"
db2 "insert into t1 values (1,1,'A'),(1,1,'B'),(1,1,'C'),(1,2,'D'),(1,3,'E')"

'Note' 카테고리의 다른 글

[SQL] Oracle 호환성에 따른 date 값의 차이점  (0) 2012.02.22
[관리] DB2 설정정보 백업방법과 그 활용  (0) 2012.01.09
[관리] DB2가 사용하는 메모리 확인은 어떻게?  (0) 2011.12.26
[관리] 데이터베이스 크기 확인  (0) 2011.12.26
[SQL] Recursive 처리 (재귀 SQL)  (0) 2011.12.19

대부분의 DBMS는 메모리 영역을 3가지로 분류를 한다.

DB2 입장에서는 인스턴스 영역, 데이터베이스 영역, Application 영역 식으로 표현할 수 있겠지만, 다른 DBMS인 경우는 다르게 표현될 것이다.

DBM CFG 및 DB CFG의 메모리 설정 값은 총 값(인스턴스 메모리가 DB 공유메모리를 포함함)을  의미하지만, 모니터링을 통한 각 값은 분리되어 확인이 된다.

V8.x 에서는 db2mtrk를 통하여 확인을 할 수 있었지만, V9.x 부터는 뷰(snapdbm_memory_pool 등)를 통해서 확인할 수 있다.

인스턴스 사용 메모리 크기

명령어

db2 “db2mtrk -i –v”                 

결과

Tracking Memory on: 2011/12/26 at 15:35:25

Memory for instance

   Other Memory is of size 30212096 bytes
   FCMBP Heap is of size 15859712 bytes
   Database Monitor Heap is of size 327680 bytes
   Total: 46399488 bytes


명령어

db2 +p –tv << EOF

select pool_id, sum(pool_cur_size) instance_mem_tsize
from sysibmadm.snapdbm_memory_pool
group by grouping sets(pool_id,())
with ur;

EOF

결과

POOL_ID        INSTANCE_MEM_TSIZE 
-------------- --------------------
-                          21561344
FCMBP                        851968
MONITOR                      327680
OTHER                      20381696

 

데이터베이스 메모리 크기

명령어
db2 “db2mtrk -d –v”

결과

Memory for database: SAMPLE

   Backup/Restore/Util Heap is of size 65536 bytes
   Package Cache is of size 786432 bytes
   Other Memory is of size 196608 bytes
   Catalog Cache Heap is of size 393216 bytes
   Buffer Pool Heap (1) is of size 8650752 bytes
   Buffer Pool Heap (System 32k buffer pool) is of size 851968 bytes
   Buffer Pool Heap (System 16k buffer pool) is of size 589824 bytes
   Buffer Pool Heap (System 8k buffer pool) is of size 458752 bytes
   Buffer Pool Heap (System 4k buffer pool) is of size 393216 bytes
   Shared Sort Heap is of size 0 bytes
   Lock Manager Heap is of size 17629184 bytes
   Database Heap is of size 19791872 bytes
   Application Heap (13) is of size 65536 bytes
   Application Heap (12) is of size 65536 bytes
   Application Heap (11) is of size 196608 bytes
   Application Heap (10) is of size 65536 bytes
   Application Heap (9) is of size 65536 bytes
   Application Heap (8) is of size 65536 bytes
   Application Heap (7) is of size 65536 bytes
   Applications Shared Heap is of size 458752 bytes
   Total: 50855936 bytes


명령어
db2 +p –tv << EOF

select substr(db_name,1,8) dbname,
       pool_id,
       sum(pool_cur_size) db_mem_tsize
from sysibmadm.snapdb_memory_pool
group by grouping sets(db_name, pool_id)
with ur;
EOF

 

Application & Private 사용 메모리 크기

명령어
db2 “db2mtrk –a –p  –v”

결과

Application Memory for database: SAMPLE

   Applications Shared Heap is of size 524288 bytes
   Total: 524288 bytes

  Memory for application 13

   Application Heap is of size 65536 bytes
   Other Memory is of size 196608 bytes
   Total: 262144 bytes

  Memory for application 12

   ……………

Memory for agent 33

   Other Memory is of size 196608 bytes
   Total: 196608 bytes

Memory for agent 32

   Other Memory is of size 196608 bytes
   Total: 196608 bytes

    ………………


명령어
db2 +p –tv << EOF

select substr(db_name,1,8) dbname, pool_id, sum(pool_cur_size) app_mem_tsize
from sysibmadm.snapagent_memory_pool
group by grouping sets(db_name,pool_id)
with ur ;

EOF

'Note' 카테고리의 다른 글

[관리] DB2 설정정보 백업방법과 그 활용  (0) 2012.01.09
[SQL] 집계함수 listagg()  (0) 2012.01.05
[관리] 데이터베이스 크기 확인  (0) 2011.12.26
[SQL] Recursive 처리 (재귀 SQL)  (0) 2011.12.19
[관리] 백업, 복구 히스토리, 아카이브 로그 자동 관리  (0) 2011.12.12

DBMS마다 데이터베이스가 가지는 자원들은 차이가 난다.

ORACLE 경우는 인스턴스와 DB가 붙어있어서 자원 구분이 불분명하지만, DB2의 경우는 DB 하위에 독립적인 트랜잭션 로그, 메모리(버퍼풀), 테이블 공간등을 지정하고 관리를 한다.

Informix의 경우는 인스턴스 범위에서 트랜잭션 로그, 시스템(메타 테이블) 정보, DB 공간이 지정되고 관리가 된다. (어렴풋한 기억을 더듬어 보면 MS-SQL, Sybase 는 informix와 비슷하지 않을까 싶다.)

그 아키텍처가 제조사 별로 다르기 때문에 DB 크기 산정도 차이가 나겠지만, 그 기준이 되는 것은 “테이블 공간”들의 합일 것이다.

DB2에서는 프로시저를 실행함으로서 DB의 사용크기와 가용 크기를 확인할 수 있다. (단위는  byte)

명령어
        db2 “call sysproc.get_dbsize_info(?,?,?,-1)”

참고

    - 첫번째 출력 변수: 실행된 시간소 값

    - 두번째 출력 변수: 데이터베이스의 크기

      (산정방식: dbsize = sum (used_pages * page_size) for each table space (SMS & DMS))

    - 세번째 출력 변수: 데이터베이스 가용 크기

      (산정방식: dbcapacity = SUM (DMS usable_pages * page size) + SUM (SMS container size + file system free size per container))

    - 네번째 입력 변수: 수집 시간 간격 (기본 값: –1)

     -1인 경우 30분 단위 수집을 의미하며, 입력 값은 “분” 단위임

 

 

실행 결과

출력 매개변수 값
--------------------------
매개변수 이름  : SNAPSHOTTIMESTAMP
매개변수 값 : 2004-02-29-18.33.34.561000

매개변수 이름  : DATABASESIZE
매개변수 값 : 22302720

매개변수 이름   : DATABASECAPACITY
매개변수 값 : 4684859392

리턴 상태 = 0

 

간단한 방법은 db2top을 통하여 확인할 수 있다.(옵션: t)

 

화면 아래 가운데 DB Size가 명시되어 있다. (현 크기/ 가용 크기)

'Note' 카테고리의 다른 글

[SQL] 집계함수 listagg()  (0) 2012.01.05
[관리] DB2가 사용하는 메모리 확인은 어떻게?  (0) 2011.12.26
[SQL] Recursive 처리 (재귀 SQL)  (0) 2011.12.19
[관리] 백업, 복구 히스토리, 아카이브 로그 자동 관리  (0) 2011.12.12
[SQL] 익명 블록 (Anonymous Block)  (0) 2011.12.05

KDUG를 방문했다가 oracle “connect by” 변환에 대한 질문이 있어 테스트를 해 보았다.

댓글 달기가 좀 불편한 점이 있어 recursive 처리에 대한 자세한 내용을 하지 못해서, 이곳에 좀 더 자세히 올리면 좋겠다 하는 생각에 적어 본다.

V9.1까지는 오라클 SQL 호환성 기능이 지원되지 않아 recursive 처리가 쉽지가 않았다. 9.5부터 connect by 사용이 가능해져서 전보다 변환 작업이 편해 졌을 것이라 생각한다.

참고로 오라클 SQL 호환성 기능을 활용하여 ORACLE 변환 작업한 기회가 많지 않아 어느 정도 가능한지 말하기는 어렵다. (단, 2년 전 V9.7 Cobra로 PoC 수행시 문제가 발생해서 connect by를 쓰지 않고 Common table로 변환 처리했던 기억이 어렴풋이 난다. ^^; )

테스트는 sample 데이터베이스의 department 테이블로 한다.

1. department 테이블 정의

$ db2 describe table department

Column name                            schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
DEPTNO                                    SYSIBM    CHARACTER                    3          0 No
DEPTNAME                               SYSIBM    VARCHAR                       36          0 No
MGRNO                                    SYSIBM    CHARACTER                    6           0 Yes
ADMRDEPT                               SYSIBM    CHARACTER                    3           0 No
LOCATION                                SYSIBM     CHARACTER                   16          0 Yes

deptno 컬럼의 값과 admrdept 컬럼의 값이 동일하다. 이 2 컬럼을 통하여 “계층"(hierarchey) 처리가 가능하다.

 

2. department 데이터

$ db2 "select deptno, admrdept from department"

DEPTNO ADMRDEPT
------ --------
A00    A00
B01    A00
C01    A00
D01    A00
D11    D01
D21    D01
E01    A00
E11    E01
E21    E01
F22    E01
G22    E01
H22    E01
I22     E01
J22    E01

  14 record(s) selected..

 

3. 계층  recurcive 처리

   (1) deptno 컬럼과 admrdept 컬럼의 값이 동일한 값을 “최상위 계층”이라 정의하고 level에 0 값을 지정하여 parent 테이블에 저장한다. (아래 SQL의 붉은색 글씨)

    (2) “최상위 계층”의 값의 deptno값과 department 테이블의 admrdept 값이 일치하는 값을 찾아

두번째 level의 데이터를 찾아 parent 테이블에 저장한다. (연두색 글씨)

    (3) 두번째 level 값을 갖는 deptno 값을 통하여 department 테이블의 admrdept 값이 일치하는 값을 다시 찾아 세번째 level 데이터를 parent 테이블에 저장한다.

    (4) 이렇게 parent 테이블에 저장된 데이터를 통하여 새로운 level의 데이터를 찾은 후, 전체 데이터에 대해 완료하면 parent 테이블을 조회하여 값은 반환시킨다.

(a.deptno != p.fkey 에 대한 이유는 글을 보시는 분들에게 숙제로 남겨 본다.)

$ db2 +p -tv << EOF

> with parent (pkey, fkey, level) as
> (select deptno, admrdept, 0 from department
>  where deptno = admrdept
>    union all
>  select a.deptno, a.admrdept, p.level+1
>  from department a, parent p
>  where a.deptno != p.fkey and a.admrdept = p.pkey
> )
> select p.pkey,p.level from parent as p;
> EOF
with parent (pkey, fkey, level) as (select deptno, admrdept, 0 from department where deptno = admrdept union all select a.deptno, a.admrdept, p.level+1 from department a, parent p where a.deptno != p.fkey and a.admrdept = p.pkey ) select p.pkey,p.level from parent as p

PKEY        LEVEL
----          -----------
SQL0347W  The recursive common table expression "INST97.PARENT" may contain an
infinite loop.  SQLSTATE=01605

A00            0
B01            1
C01            1
D01            1
E01            1
D11            2
D21            2
E11            2
E21            2
F22            2
G22            2
H22            2
I22            2
J22            2

  14 record(s) selected with 1 warning messages printed.

 

4. recursive가 되는 테이블의 SELECT 절에 recursive 처리는 넣어 처리하려면?

이것은 KDUG에 문의된 내용이다. 될까? 안될까? 궁금해서 테스트를 해 보았는데 한 SQL에서 recursive를 2번 사용하는 것은 지원되지 않는 듯 하다. (SQL0104N 에러 발생)

그래서 SELECT 절에서 사용되는 recursive 처리는 UDF (User define Function)으로 빼서 처리를 했더니 이중 recursive 처리가 되었다.

with parent (pkey, fkey, level) as
(select deptno, admrdept, 0 from department
where deptno = admrdept
union all
  select a.deptno, a.admrdept, p.level+1
  from department a, parent p
  where a.deptno != p.fkey and a.admrdept = p.pkey
)

select p.pkey,p.level,
       ( with subpar (pkey,fkey, level) as
           ( select d.deptno, d.admrdept, p.level from department d where d.admrdept=p.pkey
              union all
              select a.deptno, a.admrdept, s.level+1 from department a, subpar s where a.deptno !=s.fkey and a.admrdept=s.pkey
            )
         select count(*) from subpar) as subcnt
from parent as p;

 

실행 결과

SQL0104N  An unexpected token "as" was found following "r (pkey,fkey, level)".

Expected tokens may include:  "JOIN".  SQLSTATE=42601

 

SELECT 절의 recursive의 함수 처리

db2 +p -td"@" -v << EOF
drop function reccnt() @
create function reccnt(p_key varchar(5), p_level integer)
returns integer
return with subpar (pkey,fkey, level) as
            ( select d.deptno, d.admrdept, p_level from department d where d.admrdept=p_key
              union all
              select a.deptno, a.admrdept, s.level+1 from department a, subpar s where a.deptno !=s.fkey and a.admrdept=s.pkey
            )
         select count(*) from subpar
@
EOF


SQL0347W  The recursive common table expression "INST97.SUBPAR" may contain an
infinite loop.  SQLSTATE=01605

 

Recursive SQL

db2 +p -tv << EOF
with parent (pkey, fkey, level) as
(select deptno, admrdept, 0 from department
where deptno = admrdept
union all
  select a.deptno, a.admrdept, p.level+1
  from department a, parent p
  where a.deptno != p.fkey and a.admrdept = p.pkey
)
select pkey,level, reccnt(pkey, level) as cnt from parent;
EOF

실행 결과

PKEY        LEVEL       CNT
----         ----------- -----------
SQL0347W  The recursive common table expression "INST97.SUBPAR" may contain an
infinite loop.  SQLSTATE=01605

A00            0          27
B01            1           0
C01            1           0
D01            1           2
E01            1           7
D11            2           0
D21            2           0
E11            2           0
E21            2           0
F22            2           0
G22            2           0
H22            2           0
I22             2           0
J22            2           0

  14 record(s) selected with 1 warning messages printed.

Recursive 처리 구현이 어느 정도는 가능하지만, 데이터가 많은 경우 성능 저하는 심하게 발생할 수 밖에 없다. Connect by를 이용한 처리에 비해 부하가 더 심할지, 비슷한지는 비교해 본 적이 없기에 알 수 없지만 “꺼려지는 SQL” 이다.

'Note' 카테고리의 다른 글

[관리] DB2가 사용하는 메모리 확인은 어떻게?  (0) 2011.12.26
[관리] 데이터베이스 크기 확인  (0) 2011.12.26
[관리] 백업, 복구 히스토리, 아카이브 로그 자동 관리  (0) 2011.12.12
[SQL] 익명 블록 (Anonymous Block)  (0) 2011.12.05
[SQL] 자율 트랜잭션(Autonomous Transaction)  (0) 2011.11.28

DB2에서 백업, 아카이브 로그, 복구 히스토리는 서로 관련이 있는 시스템?들이다.

백업의 유형에 따라 아카이브 로그를 사용할 수도 있고 사용 안 할 수도 있지만, 복구 히스토리는 백업, reorg, 테이블 공간 변경, 아카이브 로그 등등에 대한 실행 기록들을 보관을 한다.

따라서 이에 대해 별 생각없이 DB 관리를 하다 보면, 복구 히스토리 파일(db2rhist.asc)은 비대해 지고, (file system에 백업을 받아 놓는 경우) 백업 이미지로 인해 disk full이 되는 상황이 발생하게 된다.

앞으로 쓰고자 할 내용은 권장을 하는 내용은 아니고, “관리를 최소화 하고자 하는 경우” 이렇게 하면 이 부분들에 대한 관리 부담을 줄일 수 있는 방법이 존재한다는 것을 참고적으로 보이고자 한다.

V9.5에서 추가된 db 구성변수로 알고 있는데 AUTO_DEL_REC_OBJ 라는 변수가 있다.

기본 값은 OFF이지만, ON으로 변경을 하게 되면 NUM_DB_BACKUP 와 REC_HIS_RETENTN 값을 초과하는 백업 이미지, 복구 히스토리 기록, 관련 archive log 들을 자동으로 삭제를 한다.

따라서 나름 업무 특성을 잘 감안해서 이런  변수에 대한 값을 설정해 두면, disk full 이나 io 성능 저하 문제를 사전에 예방할 수 있다.

NUM_DB_BACKUP의 기본값은 12개 이고, 백업 이미지 보관 개수를 12개로 한다는 것을 의미한다. 즉 13개의 백업이미지가 생기게 되면 가장 오래된 백업 이미지가 AUTO_DEC_REC_OBJ 값에 의하여 자동 삭제가 된다.

REC_HIS_RETENTN  기본값은 366일 이고, 366일 이상 기록이 발생한 경우 가장 오래된 일자의 기록들이 AUTO_DEC_REC_OBJ 값에 의하여 삭제가 된다. 이때 관련된 archive log 들도 삭제가 된다.

 

온라인 백업을 위한 설정

$ db2 get db cfg for sample | grep -i logarchmeth1
     First log archive method                 (LOGARCHMETH1) = OFF

$ db2 update db cfg for sample using logarchmeth1 disk:/database/arclog
     DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

$ db2 backup db sample to /database/backup
   Backup successful. The timestamp for this backup image is : 20111212143512

 

백업, 복구 히스토리 파일 자동 삭제 설정

$ db2 update db cfg for sample using num_db_backups 3 rec_his_retentn 0 auto_del_rec_obj on
  DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

$ db2 get db cfg for sample | egrep -i 'num_db_backups|rec_his_*|auto_del_rec_obj'

Number of database backups to retain   (NUM_DB_BACKUPS) = 3
Recovery history retention (days)     (REC_HIS_RETENTN) = 0
Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = ON

 

아카이브 로그 및 백업 이미지 확인

$ ls -al /database/arclog/inst97/SAMPLE/NODE0000/C0000000

합계 8
drwxr-x--- 2 inst97 db2igrp 4096 12월 12 14:34 .
drwxr-x--- 3 inst97 db2igrp 4096 12월 12 14:34

$ ls -al /database/backup

-rw------- 1 12월 12 14:35 SAMPLE.0.inst97.NODE0000.CATN0000.20111212143512.001

 

온라인 백업 1회

$ db2 backup db sample online to /database/backup compress
Backup successful. The timestamp for this backup image is : 20111212144420

$ ls -al /database/arclog/inst97/SAMPLE/NODE0000/C0000000

합계 20
drwxr-x--- 2 inst97 db2igrp  4096 12월 12 14:44 .
drwxr-x--- 3 inst97 db2igrp  4096 12월 12 14:34 ..
-rw-r----- 1 inst97 db2igrp 12288 12월 12 14:44 S0000000.LOG

 

온라인 백업 2, 3회

$ db2 backup db sample online to /database/backup compress
Backup successful. The timestamp for this backup image is : 20111212144604

$ db2 backup db sample online to /database/backup compress
Backup successful. The timestamp for this backup image is : 20111212144718

$ ls -al /database/arclog/inst97/SAMPLE/NODE0000/C0000000

합계 44
drwxr-x--- 2 inst97 db2igrp  4096 12월 12 14:47 .
drwxr-x--- 3 inst97 db2igrp  4096 12월 12 14:34 ..
-rw-r----- 1 inst97 db2igrp 12288 12월 12 14:44 S0000000.LOG
-rw-r----- 1 inst97 db2igrp 12288 12월 12 14:46 S0000001.LOG
-rw-r----- 1 inst97 db2igrp 12288 12월 12 14:47 S0000002.LOG

$ ls -al /database/backup

-rw------- 1 12월 12 14:44 SAMPLE.0.inst97.NODE0000.CATN0000.20111212144420.001
-rw------- 1 12월 12 14:46 SAMPLE.0.inst97.NODE0000.CATN0000.20111212144604.001
-rw------- 1 12월 12 14:47 SAMPLE.0.inst97.NODE0000.CATN0000.20111212144718.001

최초의 백업인 (아카이브 로깅 설정에 따른) offline backup (시간소: 14시 35분) 이미지가 삭제되었다

 

온라인 백업 4회

$ db2 backup db sample online to /database/backup compress
Backup successful. The timestamp for this backup image is : 20111212145049

$ ls -al /database/arclog/inst97/SAMPLE/NODE0000/C0000000
합계 44
drwxr-x--- 2 inst97 db2igrp  4096 12월 12 14:50 .
drwxr-x--- 3 inst97 db2igrp  4096 12월 12 14:34 ..
-rw-r----- 1 inst97 db2igrp 12288 12월 12 14:46 S0000001.LOG
-rw-r----- 1 inst97 db2igrp 12288 12월 12 14:47 S0000002.LOG
-rw-r----- 1 inst97 db2igrp 12288 12월 12 14:50 S0000003.LOG

 

$ ls -al /database/backup

-rw------- 1 12월 12 14:46 SAMPLE.0.inst97.NODE0000.CATN0000.20111212144604.001
-rw------- 1 12월 12 14:47 SAMPLE.0.inst97.NODE0000.CATN0000.20111212144718.001
-rw------- 1 12월 12 14:50 SAMPLE.0.inst97.NODE0000.CATN0000.20111212145049.001

온라인 백업 1회 이미지 (시간소: 14시 44분) 와 0번 archive log가 삭제되었다

 

복구 히스토리 파일 조회

$ db2 list history all for sample

                    List History File for sample

Number of matching file entries = 6


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20111212144501      1    D  S0000001.LOG C0000000
----------------------------------------------------------------------------

  EID: 8 Location: /database/arclog/inst97/SAMPLE/NODE0000/C0000000/S0000001.LOG


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20111212144604001   N    D  S0000001.LOG S0000001.LOG
----------------------------------------------------------------------------
  Contains 5 tablespace(s):

----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLE ONLINE
Start Time: 20111212144604
   End Time: 20111212144611
     Status: A
----------------------------------------------------------------------------
  EID: 9 Location: /database/backup


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20111212144724      1    D  S0000002.LOG C0000000
----------------------------------------------------------------------------

  EID: 10 Location: /database/arclog/inst97/SAMPLE/NODE0000/C0000000/S0000002.LOG


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20111212144718001   N    D  S0000002.LOG S0000002.LOG
----------------------------------------------------------------------------
  Contains 5 tablespace(s):

   ----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLE ONLINE
Start Time: 20111212144718
   End Time: 20111212144725
     Status: A
----------------------------------------------------------------------------
  EID: 11 Location: /database/backup


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20111212145055      1    D  S0000003.LOG C0000000
----------------------------------------------------------------------------

EID: 12 Location: /database/arclog/inst97/SAMPLE/NODE0000/C0000000/S0000003.LOG


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20111212145049001   N    D  S0000003.LOG S0000003.LOG
----------------------------------------------------------------------------
  Contains 5 tablespace(s):

  ----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLE ONLINE
Start Time: 20111212145049
   End Time: 20111212145056
     Status: A
----------------------------------------------------------------------------
  EID: 13 Location: /database/backup

복구 히스토리 파일에는 6개의 기록이 남아있다.

온라인 백업이 발생될 때마다 트랜잭션 로그(active log) 의 백업(archive log)이 발생하기 때문에 총 6개의 기록 (log archiving 3회, online backup 3회) 남았으며,

이전에 발생된 백업 2개 ( 14시 35분, 14시 44분) 와 관련된 트랜잭션 로그 백업(log archive), 기록도 삭제되었다. 또한 물리적으로 0번 archive log도 삭제되었다.

 

백업을 위한 디스크 공간이 제약적이고, 온라인 백업을 위한 트랜잭션 로그 백업(archive log) 공간도 부족한 경우 위와 같이 설정함으로써 disk full 상황을 예방할 수 있다.

반면 실시간 복제를 하는 시스템 등의 경우, 경우에 따라서 archive log를 사용하는 경우가 발생하므로 업무 환경에 따라서 많은 주의를 기울이고 적용해야 될 것이다.

'Note' 카테고리의 다른 글

[관리] 데이터베이스 크기 확인  (0) 2011.12.26
[SQL] Recursive 처리 (재귀 SQL)  (0) 2011.12.19
[SQL] 익명 블록 (Anonymous Block)  (0) 2011.12.05
[SQL] 자율 트랜잭션(Autonomous Transaction)  (0) 2011.11.28
[SQL] Logon Trigger  (0) 2011.11.07

V9.7에서 소개된 개념으로 FixPack5에서 약간 기능(locking 처리)  개선되어진 것으로 알고 있다.

이 기능이 업무 단에 실질적으로 적용될 가능성은 없을 것 같지만, 개발자 측면에서는 편의성을 제공해 주는 기능이 아닐까 싶다.

routine 을 프로그래밍 하면서, 함수나 저장 프로시저(stored procedure)의 로직 검증을 하기 위해 매번 삭제하고 컴파일하여 db에 반영(deploy)하는 작업을 거쳐야 한다. 어떤 면에서는 불필요하게 반영(deploy)하는 것을 꼭 해야하나? 하는 의문이 들 것이다.

특히 운영 시스템에 routine들을 적용해야 하는 시점에서 검증을 하기 위해 컴파일해서 반영(deploy)하는 것도 때로는 부담이 되고 번거롭게 하는 과정일 수도 있을 것이다.

이렇게 db에 반영하지 않고 로직 compile하여 실행시키는 일련의 작업만 하도록 제공하는 것이 “익명 블록”이다. PLSQL이나 SQLPL 작업 시 로직 검증은 편하게 할 수 있는 방법이 되지 않을까 싶다.

이 테스트는 DB2_COMPATIBILITY_VECTOR=ORA 적용하고 수행되었다.

익명 블록 코드 예 - 3번 반복하면서 시간소 및 반복 회수를 도출하는 로직

db2 +p -td@ -v <<EOF
connect to sample @
set serveroutput on @

declare
    ret_val integer ;
    ret_time timestamp;
   
begin
      for k in 1..3 loop
          select k, current timestamp into ret_val, ret_time from dual;
          dbms_output.put_line('count number: ' || ret_val);
          dbms_output.put_line('loop time: ' || ret_time) ;
      end loop ;
end;
@
set serveroutput off @
terminate @
EOF

 

실행 결과

블록을 컴파일하면 컴파일과 동시에 실행이 되어 진다. 로직에 대한 논리적인 이름(껍데기?)을 갖고 있지 못하기에 당연한 결과일 수 있을 것이다.

connect to sample
 
   Database Connection Information
 
Database server        = DB2/LINUXX8664 9.7.4
SQL authorization ID   = ORA974
Local database alias   = SAMPLE
 
 
set serveroutput on
DB20000I  The SET SERVEROUTPUT command completed successfully.
 
declare ret_val integer ; ret_time timestamp; begin for k in 1..3 loop select k, current timestamp into ret_val, ret_time from dual; dbms_output.put_line('count number: ' || ret_val); dbms_output.put_line('loop time: ' || ret_time) ; end loop ; end;
DB20000I  The SQL command completed successfully.
 
count number: 1
loop time: 2011-10-31-00.03.21.099553
count number: 2
loop time: 2011-10-31-00.03.21.124113
count number: 3
loop time: 2011-10-31-00.03.21.124266
 
set serveroutput off
DB20000I  The SET SERVEROUTPUT command completed successfully.
 
terminate
DB20000I  The TERMINATE command completed successfully.

'Note' 카테고리의 다른 글

[SQL] Recursive 처리 (재귀 SQL)  (0) 2011.12.19
[관리] 백업, 복구 히스토리, 아카이브 로그 자동 관리  (0) 2011.12.12
[SQL] 자율 트랜잭션(Autonomous Transaction)  (0) 2011.11.28
[SQL] Logon Trigger  (0) 2011.11.07
[관리] DB2 Rebind 가 필요한 시점  (0) 2011.10.06

DB2 V9.7 FixPack3 에서 도입된 기능이다.

저장 프로시저(Stored Procedure) 내부에서 호출되는 저장 프로시저의 트랜잭션 처리를 “호출한 저장 프로시저 트랜잭션 상태”와 상관없이 DB에 반영(commit)할 수 있게 하는 기능이다.

다른 DBMS에서는 어떤 용도로 사용되는지 찾아보질 않아 모르겠지만, 곰곰이 생각해 보면 업무적으로 “특수한 요건”일 때 적용이 될 법하고, 이외는 “호출한/호출당한 저장 프로시저”의 트랜잭션 수행 기록을 남기는 용도로 사용할 수도 있지 않나 싶다.

먼가 중요한 처리를 담당하는 “저장 프로시저”인 경우, 이 저장 프로시저가 수행될 때 마다 처리한 트랜잭션들을 기록하도록 하기 위해 (commit 이던 rollback이던) “자율 트랜잭션”을 사용할 필요가 있지 않을까 하는 생각을 해본다.

테스트는 오라클 호환 벡터를 설정하고 테스트를 하였다.(오라클 관련 호환성 기능 테스트를 병행하고 있었기에..)  (DB2_COMPATIBILITY_VECTOR=ORA)

호출되는 내부 저장 프로시저 생성

db2 +p -td"@" -v << EOF
connect to sample @
drop table t1 @
create table t1(c1 timestamp) @
create or replace procedure sp2()
language sql
autonomous
begin
      insert into t1 values (current timestamp) ;
end
@
terminate @
EOF

선언부에 “Autonomous”를 명시한다.

 

호스트 저장 프로시저 생성

db2 +p -td"@" -v << EOF
connect to sample @
drop table t3@
create table t3 (c1 timestamp)@
create or replace procedure sp3()
begin
       insert into t3 values (current timestamp) ;
       call sp2();
end
@
terminate @
EOF

프로시저 sp2를 auto commit를 해제하고 수행 후, rollback을 수행함으로써 autonomous가 적용되었는지를 확인한다.

저장 프로시저 실행

$ db2 +c "call sp3()"

  Return Status = 0


$ db2 rollback

DB20000I  The SQL command completed successfully.

 

SP3프로시저의 수행 결과 확인

$ db2 "select * from t3"

C1
--------------------------

  0 record(s) selected.

SP2 프로시저의 수행 결과 확인

$ db2 "select * from t1"

C1
--------------------------
2011-10-17-17.46.39.148931

  1 record(s) selected.

'Note' 카테고리의 다른 글

[관리] 백업, 복구 히스토리, 아카이브 로그 자동 관리  (0) 2011.12.12
[SQL] 익명 블록 (Anonymous Block)  (0) 2011.12.05
[SQL] Logon Trigger  (0) 2011.11.07
[관리] DB2 Rebind 가 필요한 시점  (0) 2011.10.06
[설치] Linux CentOS에서 NFS 설정하기  (0) 2011.09.23

DB2 V9.7 FixPack3 부터는 로그온 트리거라는 기능을 지원한다. 오라클이나 MS-SQL에서는 트리거라는 이름으로 작성을 하지만, DB2에서는 procedure를 통하여 기능 구현하여 실행을 시킨다.

기능을 테스트해보면서 그 기능이 제약적이다라는 느낌이 들었지만, “기본 스키마 이름”를 변경하거나 “기본 library 경로”등을 세션단위로 설정하고자 할 경우에 도움이 될 듯 하다. 아마도 버전 upgrade되면서 보다 다양하게 활용되도록 개선되지 않을까 싶다.

DB의 구성변수에 CONNECT_PROC 라는 변수에 실행시키고자 하는 stored procedure를 설정하면 사용자가 “접속 하는 순간” 실행이 되어진다.

테스트 시나리오는 사용자가 접속할 때, 접속 계정 명, 접속 시간을 기록하도록 하는 것으로 정의하였다.

생성 스크립트

db2 +p -td"@" -v << EOF
connect to sample @

CREATE TABLE SECURITY.AUDIT(userid VARCHAR(20), event VARCHAR(20), tstamp TIMESTAMP)@

CREATE OR REPLACE PROCEDURE ENVIRONMENT.CONNECTION_SETUP_DBMOD()
BEGIN 
       INSERT INTO SECURITY.AUDIT VALUES(USER, 'CONNECT', CURRENT TIMESTAMP);
END
@

update db cfg using connect_proc ENVIRONMENT.CONNECTION_SETUP_DBMOD @

EOF

접속 전 Audit 테이블 조회

SQL 실행

db2 "select * from security.audit"

실행 결과

USERID               EVENT                TSTAMP
-------------------- -------------------- --------------------------

  0 record(s) selected.

 

접속 시도

db2 connect to sample

 

Audit 테이블 조회

SQL 실행

db2 "select * from security.audit"

실행 결과

USERID               EVENT                TSTAMP
-------------------- -------------------- --------------------------
ORA974               CONNECT              2011-10-19-17.55.20.485295

'Note' 카테고리의 다른 글

[SQL] 익명 블록 (Anonymous Block)  (0) 2011.12.05
[SQL] 자율 트랜잭션(Autonomous Transaction)  (0) 2011.11.28
[관리] DB2 Rebind 가 필요한 시점  (0) 2011.10.06
[설치] Linux CentOS에서 NFS 설정하기  (0) 2011.09.23
[설치] db2iprune으로 불필요한 설치 이미지 제거하기  (0) 2011.09.23

+ Recent posts

티스토리툴바