시소당

Oracle 사용자를 위한 DB2 Conversion 프로젝트, Part 3

Oracle Metadata

Data Dictionary라 불리는 테이블과 뷰로 구성되어 있으며, SYS User에 의해 소유되며 System Table Space에 저장되어 있다. 이러한 메타데이터들은 오라클 DB 서버에 의해 업데이트 되며 사용자에 의한 업데이트가 불가능한 Read-Only 테이블과 뷰들이다.

Metadata 종류

Data Dictionary View는 크게 DBA_, ALL_, USER_로 나뉘어져 있다. (이 분류에 포함되지 않는 뷰들도 존재한다) 각 분류에 따라 접근할 수 있는 오브젝트에 대한 내용이 조금씩 다르다.
* DBA_: 모든 사용자의 객체에 대한 정보
* ALL_: 자신이 접근 할 수 있는 모든 Object에 대한 정보
* USER_: 자신이 소유한 Object에 대한 정보

Oracle Performance View

Data Dictionary와는 별개로 V$_로 시작하는 가상의 테이블인 Dynamic performance View가 존재하며 이는 catalog.sql 스크립트를 실행하여 생성된다.

DB2 Meatadata

DB2에서의 메타데이터는 Catalog라 불리며, 이 Catalog는 데이터베이스의 오브젝트 정보, 권한 정보, 무 결성 정보 등에 관한 논리적, 물리적 정보를 보유하고 있다. 이러한 Catalog 데이터는 데이터베이스 생성시 자동으로 생성되며, SYSCATSPACE 테이블 스페이스에 SYSIBM이라는 스키마를 갖는 테이블들로 이루어져 있다.

Metadata 종류

SYSIBM이라는 스키마를 갖는 테이블 중 중요 정보들 조합은 SYSCAT과 SYSSTAT 스키마로 이루어진 뷰로 제공된다. SYSCAT 스키마로 구성된 뷰들은 오라클 DB에서와 마찬가지로 시스템에 의해 업데이트 되는 Read-only 속성을 가지l고 있다. 하지만 SYSSTAT 스키마로 구성된 뷰들은 이와 달리 사용자에 의해 업데이트가 가능한 뷰들로 구성되어 있다.

이 SYSSTAT 스카마 뷰들은 DB2 옵티마이저가 참조하는 통계 정보를 포함하고 있다. 즉 SYSSTAT 뷰를 업데이트하여 옵티마이저를 제어해 상황별 데이터베이스이 퍼포먼스를 파악할 수가 있다. 하지만 이렇게 변경된 SYSSTAT 정보도 해당 오브젝트에 RUNSTATS를 수행하면 시스템은 자동으로 Catalog 정보를 최신으로 갱신하게 된다.

DB2에서의 메타 정보들은 오라클의 DBA_, ALL_, USER_처럼 계층적으로 이루어져 있지 않으며 Catalog 정보에 대한 Select 권한은 기본적으로 Public에 허용되어 있다. 따라서 필요하다면 Public Select 권한을 취소하고 특정 유저나 그룹에게만 정보 접근을 허용할 필요가 있다. 물론 SYSSTAT 관련 뷰들을 업데이트 하려면 테이블에 대한 컨트롤 특권이 있어야 한다. 참고로 Catalog 뷰들에 대한 Select 권한 허용 취소는 시스템 관리자 혹은 DB 관리자의 권한이 필요하다.

DB2 Performance View

DB2에서는 Dynamic Performance 뷰들을 제공하지 않는다. 하지만 db2clp 명령을 통해 현재 상태를 추적하거나, 스냅샷 정보를 통해 DB2의 dynamic performance view와 같은 정보를 얻을 수 있다. 또한 8.2버전부터는 db2pd라는 명령과 옵션을 통해 OS 레벨, 인스턴스 레벨, 데이터베이스 레벨에서의 좀더 자세하고 많은 정보를 얻을 수 있다.

Oracle ⇔ DB2 Metadata

Object




COMMENT 조회

DB2는 comment에 대한 별도의 뷰를 제공하지 않으나 테이블과 컬럼을 조회하는 SYSCAT.TABLES, SYSCAT.COLUMNS의 REMARK 컬럼을 통해 테이블과 컬럼의 주석을 조회할 수 있다. 그 외 다른 오브젝트(Procedure, Function...)들도 해당 오브젝트를 조회하는 뷰에 Remark 컬럼에서 주석을 참조하면 된다.

ALIAS 조회

오라클 DBMS에서의 Synonyms을 DB2에서는 Alias라고 부르며, Alias만을 별도로 조회하는 System Catalog View는 존재하지 않는다. SYSCAT.TABLES에서 속성(Type)이 ‘A’인 테이블이 Alias다.

DB LINK 조회

오라클 DBMS에서의 DB Link와 같은 기능인 DB2의 Federate DB에서 오브젝트에 대한 조회는 V9 (Viper) 이전에는 SYSCAT.TABOPTIONS과 SYSCAT.TABLES의 JOIN으로 조회하였지만 V9에서는 SYSCAT.NICKNAMES를 이용하여 조회 가능하다.

Routine (Procedure / Function)



Authorization



User 정보

DB2에서는 OS에 등록되어 있는 유저를 DB 유저로 사용하기 때문에 오라클DBMS의 DBA_USERS와 같은 별도의 Catalog View를 보유하고 있지 않다.

권한&특권 정보

DB2에서의 Security는 권한과 특권으로 분리가 되며, 각 Object에 관한 특권은 SYSCAT.DBAUTH, SYSCAT.TABAUTH, SYSCAT.TABLESPACEAUTH와 같이 AUTH로 끝나는 SYSCAT VIEW에서 조회할 수 있다.
오라클의 ROLE과 같은 특권의 조합은 DB2에는 존재하지 않는다. 하지만 이와 비슷한 개념으로 특권이 있으며 이는 인스턴스 레벨(SYSADM_GROUP, SYSCTRL_GROUP, SYSMAINT_GROUP, SYSMON_GROUP)의 운영그룹과 DB 레벨(DBADM)의 운영그룹으로 분리되어 있다. 인스턴스 레벨의 운영 그룹은 DBM CFG 항목에서 _GROUP으로 조회할 수 있으며, DBADM 유저는 SYSCAT.DBAUTH 카탈로그 뷰에서 검색할 수 있다.

$ db2 get dbm cfg | grep _GROUP
 SYSADM group name                        (SYSADM_GROUP) = DB2GRP 
 SYSCTRL group name                      (SYSCTRL_GROUP) =
 SYSMAINT group name                    (SYSMAINT_GROUP) =
 SYSMON group name                        (SYSMON_GROUP) =

SELECT DISTINCT GRANTEE, GRANTEETYPE
FROM SYSCAT.DBAUTH
WHERE DBADMAUTH = 'Y'




Oracle ⇔ DB2 Dynamic View

*DB=Database Name (Default =’’) / PN=Partition Number (Default=-1)





DB2 Metadata 조회

지금까지 DB2와 오라클의 메타데이터 차이에 대해서 간략하게 알아 보았다. 이제 실질적으로 DB2 Catalog 정보를 활용하여 어떤 식으로 DBMS의 상태를 모니터링 하는지에 대해 알아보고자 한다.
Metadata 조회에 대한 설명 부분은 데이터베이스 모니터링에 관련된 성격을 지니지만, Snapshot / db2pd를 통한 모니터링 부분을 많이 다루지는 않았다. SQL TOP에 대한 조회, Log 사용량, Agent & Application 사용 현황 등 DB에서 주로 모니터링하고 튜닝해야 하는 부분은 추후 기회가 된다면 Monitoring&Tuning 세션을 통해 다루도록 하겠다.

DBMS 정보

Instance Configuration
인스턴스를 구성정보는 DB2CLP 창에서 db2 command를 통해 확인하거나 snapshot 또는 db2pd를 이용하여 확인할 수 있다.
* db2 get dbm cfg
* db2 “select * from table (snapshot_dbm (-1)) as dbm”
* db2pd ? dbmcfg

Database Configuration
데이터베이스 정보를 확인 하는 방법 또한 Instance의 정보확인과 같이 db2 command / snapshot / db2pd를 이용한 방법이 있다.
* db2 get db cfg
* db2 “select * from table (snapshot_db (‘’,-1)) as dbm”
* db2pd ? dbcfg

Memory

데이터베이스를 구성하는 메모리에 대한 설정 정보는 DBM CFG 항목과 DB CFG 항목을 통해 확인할 수 있으며, 데이터베이스에 현재 할당되어 있는 메모리에 대한 정보는 db2mtrk를 통해 확인할 수 있다.  참고로, ver9.1 이전까지는 db2mtrk를 이용하여 db2clp 창에서 확인하는 방법밖에 없었지만 ver9 이후에는 데이터베이스에 접속하여 admin view를 통해 instance level / database level / application level의 조회가 가능해 졌다.
* db2mtrk ?I ?d (인스턴스 및 모든 데이터 베이스의 현재 메모리 사용량)
* db2mtrk -i -p ?m (인스턴스 및 에이전트 개인용 메모리에 허용되는 최대 크기)
* db2mtrk -p -r 1 5 (1초 간격으로 에이전트 개인용 메모리를 다섯 번 보고)




<참고> Ver 9의 Admin View를 활용한 Memory 정보
SELECT 'Instance' as level
     , 'Instance' as id
     , pool_id as "Name"
     , pool_cur_size as "Current SZ"
     , pool_watermark as "Water mark"
     , pool_config_size as "Confiured SZ"
FROM SYSIBMADM.SNAPDBM_MEMORY_POOL

UNION ALL

select 'Database' as level
     , db_name as id
     , case pool_id
       when 'BP' then pool_id ||  '('  || COALESCE(pool_secondary_id,'')  ||')'
                   else pool_id 
    end "Name"
     , pool_cur_size as "Current SZ"
     , pool_watermark as "Water mark"
     , pool_config_size as "Confiured SZ"
FROM SYSIBMADM.SNAPDB_MEMORY_POOL

UNION ALL

SELECT 'AGENT'||'('||rtrim(db_name)||')' as level
      , RTRIM(CAST(agent_id AS CHAR(10)))
|| ' ('||RTRIM(CAST(agent_pid  AS CHAR(10)))||')' AS id
     , pool_id as "Name"
     , pool_cur_size as "Current SZ"
     , pool_watermark as "Water mark"
     , pool_config_size as "Confiured SZ"
FROM  SYSIBMADM.SNAPAGENT_MEMORY_POOL

Log 정보

LOG 설정 값을 통한 LOG 정보 확인
$> db2 get db cfg | grep LOG
Log file size (4KB)                         (LOGFILSIZ) = 1024
Number of primary log files                (LOGPRIMARY) = 13
Number of secondary log files               (LOGSECOND) = 4
? LOG 크기 LOGFILSIZ * 4KB * LOGPRIMARY * LOGSECOND = 68 MB

First active log file                                   = S0000584.LOG
? 현재 데이터베이스에 변경이 확약 되지 않은 첫 번째 Log 즉, Database가 재 기동시 첫번째 필요한 파일

First log archive method                 (LOGARCHMETH1) = DISK:/db2archlog/
? Archive Log를 위한 옵션으로 LOGRETAIN이 ON이거나 LOGARCHNETH1에 설정 값이 있는 경우 Circular Log가 아닌 Archive Log임

SIZE

Table Size
테이블의 크기는 테이블이 속해 있는 테이블 스페이스의 페이지 크기에 해당 테이블에 할당되어 있는 페이지 수를 곱하여 테이블의 크기를 구한다.
select tab.tabschema
     , tab.tabname
     , tab.fpages  -- Fpages : 물리적으로 할당된 page
     , tab.npages  -- Npages : 실제 할당된 page
     , tab.tbspace
     , tbs.pagesize
     , (case tab.fpages when -1 then -1
else tab.fpages * tbs.pagesize end) / 1024 as "Assigned_SZ (KB)"
, (case tab.npages when -1 then -1
else tab.npages * tbs.pagesize end) / 1024 as "Actual_SZ (KB)"
from syscat.tables as tab, syscat.tablespaces as tbs
where tab.tbspace = tbs.tbspace;

Index Size
SELECT inx.indschema -- 인덱스 스키마
   ,inx.indname -- 인덱스 이름
 ,inx.tabschema -- 인덱스가 보유하고 있는 테이블 스키마
   ,inx.tabname -- 인덱스를 보유하고 있는 테이블 이름
   ,inx.colnames -- 인덱스를 구성하고 있는 컬럼 이름
   ,inx.nleaf -- 인덱스 스키마
   ,inx.num_empty_leafs -- 인덱스 스키마
   ,tab.card as rows# -- 인덱스 스키마
   , case when tab.INDEX_TBSPACE IS NOT NULL then RTRIM(tab.INDEX_TBSPACE)
     when tab.TBSPACE IS NOT NULL then RTRIM(tab.TBSPACE)
     else  'N/A' end INXSPACE -- 인덱스 테이블 스페이스
   , (case inx.nleaf when -1 then -1
       else inx.nleaf * tbs.pagesize end)/1024  as "Assigned_Index Size (KB)"
, (case inx.num_empty_leafs when -1 then -1
      else (inx.nleaf-inx.num_empty_leafs) * tbs.pagesize end)/1024  as
"Actual_Index Size (KB)"
From SYSCAT.INDEXES inx
INNER JOIN SYSCAT.TABLES  tab ON inx.TABNAME = tab.TABNAME
AND inx.TABSCHEMA = tab.TABSCHEMA , syscat.tablespaces as tbs
WHERE tab.tbspace = tbs.tbspace;

Table Space Size
select a.tablespace,
       a.page_size,
       a.total_size_M,
       a.used_size_M,
       a.free_size_M,
       dec((dec(a.used_size_M + 1)
/dec(a.total_size_M + 1))*100,4,1) as USED
from (select value(substr(tablespace_name,1,20),'TOTAL') as ts,
               substr(char(max(page_size)),1,9) as page_size,
/* Size는 Byte로 나오기 때문에 /1024/1024해서 MB로 변경 */
               sum(total_pages*page_size)/1024/1024 as total_size_M,
               sum(used_pages*page_size)/1024/1024 as used_size_M,
               sum(free_pages*page_size)/1024/1024 as free_size_M
         from table(snapshot_tbs_cfg('', -1)) as snapshot_tbs_cfg
         group by rollup(substr(tablespace_name,1,20)) ) a ;

Table Space Container Size
select substr(con.tablespace_name,1,20) as tablespace,
       case smallint(tbs.tablespace_state)
            when 0           then '정상'
            when 1           then 'Quiesce 상태 공유'
            when 2           then 'Quiesce 상태 갱신'
            when 4           then 'Quiesce 상태 독점'
            when 8           then '로드 보류'
            when 16 then '삭제 보류'
            when 32 then '백업 보류'
            when 64 then '롤 포워드 진행 중'
            when 128 then '롤 포워드 보류'
            when 256 then '리스토어 보류'
            when 1024 then 'Reorg 진행 중'
            when 2048 then '백업 진행 중'
            when 4096 then '스토리지가 정의되어 있어야 함'
            when 8192 then '리스토어 진행 중'
            when 16384 then '오프라인이며 액세스 불가능'
            when 32768 then '삭제 보류'
            when 33554432 then '스토리지가 정의되었을 수도 있음'
            when 67108864 then '스토리지 정의가 최종 상태임'
            when 134217728 then '롤 포워드 이전에 스토리지 정의가 변경됨'
            when 268435456 then 'DMS 재조정 프로그램 사용 중'
            when 536870912 then 'TBS 삭제 진행 중'
            when 1073741824    then 'TBS 작성 진행 중'
            else 'Un-Kown State' end as state,
       substr(con.container_name,1,50) as conainer_name,
       smallint(con.total_pages) as total_page,
       smallint(con.usable_pages) as usable_page,
       cast(con.total_pages*dec(tbs.page_size)/1024/1024 as dec(11,1)) as total_size_M,
       cast(con.usable_pages*dec(tbs.page_size)/1024/1024 as integer) as usable_pages_M
     from table(snapshot_container('', -1)) as con,
          table(snapshot_tbs_cfg('', -1)) as tbs
     where con.tablespace_id = tbs.tablespace_id order by tablespace, con.container_id;

PERFORMANCE

Connection Status
$> db2 list application [show detail






기본적으로 Authorization ID / Appl. Name / Appl. Handle / Appl. ID / DB Name 정보가 출력된다. Application Handle 번호는 데이터베이스에 의해 각 애플리케이션이 부여 받은 ID로 애플리케이션을 강제 종료 시킬 때 사용되는 ID이다. 예> db2 force application APPl. Handle
Application ID는 통신 프로토콜에 따라 형식이 결정되며 각각의 형식은 마침표로 구분되며 대표적인 TCP/IP와 NetBios가 있다. TCP/IP인 경우 IP Address, Port. Application의 3개의 섹션으로 구성되며 IP Address의 경우 Ver8과 Ver9은 상이하게 출력된다.
Ver8 : ip_address(16진 문자로 표시되는 32비트번호), port(16진 문자), application(인스턴스 고유 ID)
eg> H1B41DA2.GE0A.071130132904 (0~9로 시작하면 G~P로 맵핑 된다)

--->? H1 B4 1D A2 ? 11 B4 1D A2 ? ip address : 17.180.29.162
--->? GE0A ? E0A ? Port: 3594




Ver9 : IPV6주소를 수용함. IPv4인 경우 xx.xx.xx.xx의 형태로 표시된다.




오라클 DB의 다이내믹 뷰를 이용해 시스템을 모니터 하던 사용자들이 DB2를 사용하면서 가장 아쉬워하는 부분이 WAIT 관련 이벤트 일 것이다. 필자 또한 이 부분이 오라클 모니터링 툴을 DB2로 포팅하면서 가장 힘들었던 부분이다. 아쉽게도 아직까지 DB2에서는 몇몇 WAIT EVENT(LOCK, PREFETCH..)만을 스냅샷을 통해 제공할 뿐, WAIT 이벤트와 같은 항목은 제공하지 않고 있다. 


출처: http://www.bloter.net 김명훈, DB2 프리랜서 DBA