두 개념 모두 V9.7에서 나온 것으로 알고 있다.

옵티마이저가 SQL을 어떻게 계획을 수립하여 실행할 지를 알고자  access plan을 보지만, 이것은 예상 계획일 뿐 “실제로 실행한 plan”을 보여주지는 않는다.

실제 수행한 plan에 대한 정보는 db2에서 actual section 이라는 개념으로 제공되고 actual section을 얻기 위해서는 “워크로드"와 “이벤트 모니터"를 사용해야 된다.

 

1. actual section을 수집하기 위한 절차

   (1) DB CFG 구성변수 설정 - section_actuals (값: base)

   (2) Workload 생성           - 수집하고자 하는 SQL을 수행하는 application name 을 알아야 함

   (3) Event Monitor 생성

   (4) Explain 시간소 확인    -  저장 프로시저 EXPLAIN_FROM_ACTIVITY 수행

   (5) db2exfmt 수행하여 plan 정보 수집

 

2. 테스트

   - db2 서버 local에서 테스트를 하기 때문에 application name 이 db2bp.exe 에 대하여 workload를 생성함

# APP NAME 이름 확인
$> db2 list applications  

# db2bp.exe에 대한 워크로드 wl1 생성
$> db2 "create workload wl1 applname('db2bp.exe') collect activity data with details, section"      

# 사용 권한 PUBLIC 으로 설정
$> db2 "grant usage on workload wl1 to public"

# 이벤트 모니터 생성
$> db2 "create event monitor actevmon for activities write to table"

# 활성화
$> db2 "set event monitor actevmon state 1"

# SQL 수행 (db2bp.exe가 수행되는 터미널/CLP 창에서 수행)

# APP ID, UOW ID 등 확인
$> db2 "select varchar(appl_id,20) appl_id, uow_id, activity_id, varchar(stmt_text,50) stmt_text from activitystmt_actevmon"

APPL_ID                    UOW_ID         ACTIVITY_ID          STMT_TEXT
*LOCAL.DB2_01.140129           2                    1      select * from employee whe



# Explain 시간 확인
$> db2 "call explain_from_activity('*LOCAL.DB2_01.140129001143',2,1,'ACTEVMON','BRAD',?,?,?,?,?)"

출력 매개변수 값
--------------------------
매개변수 이름: EXPLAIN_SCHEMA
매개변수 값: BRAD

매개변수 이름: EXPLAIN_REQUESTER
매개변수 값: BRAD

매개변수 이름: EXPLAIN_TIME
매개변수 값: 2014-01-29-09.25.36.178000

매개변수 이름: SOURCE_NAME
매개변수 값: SQLC2K26

매개변수 이름: SOURCE_SCHEMA
매개변수 값: NULLID

매개변수 이름: SOURCE_VERSION
매개변수 값:

리턴 상태 = 0



# Actual Section 수집
$> db2exfmt -d sample -w 2014-01-29-09.25.36.178000 -n SQLC2K26 -s NULLID -# 0 -t

Access Plan:
-----------
        Total Cost:             6.8165
        Query Degree:           1

             Rows
          Rows Actual
            RETURN
            (   1)
             Cost
              I/O
              |
              10
             10
            FETCH
            (   2)
            6.8165
              NA
         /----+----\
       10            42
       10            NA
     IXSCAN    TABLE: BRAD
     (   3)       EMPLOYEE
   0.00851615        Q1
       NA
       |
       42
       NA
INDEX: BRAD
   PK_EMPLOYEE
       Q1


# 이벤트 모니터 비활성화
$> db2 "set event monitor actevmon state 0"

 

위 plan의 경우 예측된 rows 값과 actual rows값이 동일하다. 즉 통계정보 갱신이 적절히 되어 차이가 존재하지 않음을 알 수 있다.

actual section 의 존재에 대해서는 알게 된지 오래 되었지만, 설정 작업이 많고 application name 등을 확인해야 하는 등 불편함이 느껴져서 잘 쓰게 되지 않았다.

 

db2 문서들을 검색하다 알게된 db2caem 이라는 유틸리티를 통해서도 actual section을 수집할 수 있다.

위와 같은 여러 수행 절차없이 원하는 SQL 구문만 알고 있으면 actual section 정보를 수집할 수 있다.

(주) caem: Capture Activity Event Monitor data tool

 

3. db2caem 테스트

$> db2pd -db sample -dbcfg | grep -i section

SECTION_ACTUALS                NONE                 NONE

$> cat > 02.sql1.db2 <<EOF
select * from t2 where c2 > 0 ;
EOF

$> db2caem -d sample -sf 02.sql1.db2 –o 출력경로

$> cat 출력경로/db2caem.exfmt.1

Access Plan:
-----------
        Total Cost:             7.58138
        Query Degree:           1

      Rows
   Rows Actual
     RETURN
     (   1)
      Cost
       I/O
       |
        7
       8
     TBSCAN
     (   2)
     7.58138
       NA
       |
        8
       NA
TABLE: DB2INST
       T2
       Q1

 

actual section 을 통해서 옵티마이저가 계획한 방식대로 수행되었는지 확인을 할 수 있고,

건수의 차이를 통하여 통계정보의 적절성을 판단할 수 있을 것 같다.

'Note' 카테고리의 다른 글

[관리] Backup Pending 풀기  (0) 2014.03.31
[구성] Federation MS-SQL Server  (0) 2014.03.20
[성능] SQL Optimizer Profile  (0) 2014.02.06
[이관] lobsinfile 및 tempfiles path  (0) 2014.01.27
[이관] admin_cmd 프로시저를 이용한 이관 자동화  (0) 2014.01.27

요즘에도 오라클의 Hint가 SQL 튜닝의 방법으로 선호되는지 모르겠다.

JAVA 기반의 개발 환경에서 iBatis 등을 이용하여 XML 파일에 SQL을 중앙집중화(?) 방식으로 사용하는 추세이고, 오라클 아닌 다른 DBMS를 사용하더라도 개발된 SQL 수정없이 사용하는 요건이 중요해 지면서 “특정 DBMS의 기능”에 편중되는 방식은 지양되고 있는 것 같다.

사용 중인 오라클이 버전 업그레이드를 한다던가 하는 경우에 Hint가 사용된 SQL은 성능 저하를 일으키는 경우을 들어 본 적도 있다.

 

SQL의 성능 개선으로 Hint 사용이 중요한 튜닝 방법으로 인식되어 왔지만, 개인적으로 이런 방법은 “호환되지 않는" 구속성이 있어 튜닝 방법으로서는 부정적인 시각으로 보게 되었다.

 

DB2에서 실행 계획 변경은 Optimizer Guideline 이라는 XML 방식으로 한다.

간편한 방식은 SQL 뒤에 XML 태그를 붙이는 방식으로 많이 알려진 방식이다. 이외 optimizer profile 을 만들어서, 즉 ibatis의 xml 처럼 XML 문서에 SQL과 Guideline을 설정하여 사용도 가능하다.

 

1. 테스트 시나리오

   - 목적: 데이터 정렬을 ORDER BY 를 사용하지 않고, Index를 이용하여 오름차순으로 데이터 결과 집합을 반환

   - 테이블 명: T2  (컬럼: C1, C2, C3)

   - 인덱스    : T2_IDX2 (C2)

                    T2_IDX3 (C2, C3)

db2 +p -tv << EOF
connect to sample ;
drop table t2 ;
create table t2 (c1 int, c2 int, c3 int) ;
create index t2_idx2 on t2 (c2) ;
create index t2_idx3 on t2 (c2,c3) ;

insert into t2 values (1,5,10),(2,10,9),(3,3,8),(4,6,7),(5,9,6),(6,7,5),(7,5,1),(8,3,4);
runstats on table inst15.t2 and detailed indexes all ;

terminate ;
EOF

 

     - 테스트 SQL

db2 "select * from t2 where c2 > 0"

 

2. OPTIMIZER PROFILE 기능 적용

db2set db2_optprofile=yes
db2start

 

3. 실행 계획 확인

-- PLAN 테이블 생성
$ db2 "call sysproc.sysinstallobjects('EXPLAIN','C',NULL,CURRENT SCHEMA)"

-- 최적화 프로파일 테이블 생성
$ db2 "call sysproc.sysinstallobjects('OPT_PROFILES', 'c', '', CURRENT SCHEMA)"

cat > opt1.db2 << EOF
select * from t2 where c2 > 0
EOF

$ db2expln -d sample -f opt1.db2 -g –t
….

Optimizer Plan:

 Operator
   (ID)

RETURN
  ( 1)
   |
 TBSCAN
  ( 2)
   |
Table:
INST15
T2

 

-- 결과 값

C1          C2          C3
----------- ----------- -----------
          1           5          10
          2          10           9
          3           3           8
          4           6           7
          5           9           6
          6           7           5
          7           5           1
          8           3           4

* table scan이 선택된 이유는, 데이터가 적기 때문에 Index Scan 이 비효율적이라고 Optimizer 가 판단을 했기 때문이다.

  ( 통계정보를 갱신하지 않고 plan을 확인했다면 index scan이 수행되었을 것이다. 초보 시절 index scan 하던 것이 통계 수집 후 table scan 한 것을 이상하게 생각한 적이 있었다 ^^; )

 

4. 프로파일 작성 및 등록

파일명: opt1.xml

<?xml version="1.0" encoding="UTF-16"?>
 
<OPTPROFILE VERSION="10.5.0">

<STMTPROFILE ID="TEST1">
    <STMTKEY>
             <![CDATA[select * from t2 where c2 > 0]]>
    </STMTKEY>
 
    <OPTGUIDELINES>
            <IXSCAN TABLE="T2" INDEX="T2_IDX2"/>
    </OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>

(참고) XML 문서 명은 “스키마명.프로파일명.업무명.xml” 식으로 사용하는 것이 관리 상 좋을 것 같다.   “업무명"은 해당 SQL을 쉽게 파악할 수 있는 “업무 단위” 혹은 “테이블 단위” 등으로 명명하면 될 것 같다.

 

등록

cat > opt1.del << EOF
"INST15","TEST","opt1.xml"
EOF

 

$ db2 import from opt1.del of del modified by lobsinfile replace into systools.opt_profile

(주의) 스키마 명(INST15), 프로파일명(TEST)”를 지정해야 한다.

        프로파일명은 임의적이고, 스키마 명은 PLAN 테이블의 스키마 명을 지정한다.

 

5. PLAN 변경 확인

-- 프로파일 명 지정

$ db2 SET CURRENT OPTIMIZATION PROFILE="TEST"

 

-- PLAN만 수집

$ db2 SET CURRENT EXPLAIN MODE EXPLAIN

 

-- SQL 수행

$ db2 "select * from t2 where c2 > 0"

 

-- PLAN 확인

$ db2exfmt -d SAMPLE -1 -o exfmt1.out

$ cat exfmt1.out

…

Profile Information:
--------------------

OPT_PROF: (Optimization Profile Name)
        INST15.TEST
STMTPROF: (Statement Profile Name)
        TEST1

…


Access Plan:
-----------
        Total Cost:             6.83711
        Query Degree:           1

                Rows
               RETURN
               (   1)
                Cost
                 I/O
                 |
                  7
               FETCH
               (   2)
               6.83711
                  1
           /-----+-----\
          7               8

...skipping one line
       (   3)            T2
      0.0233252          Q1
          0
         |
          8
 INDEX:    INST15
       T2_IDX2
         Q1

 

$ db2 SET CURRENT EXPLAIN MODE no

$ db2 "select * from t2 where c2 > 0"

 

C1          C2          C3
----------- ----------- -----------
          3           3           8
          8           3           4
          1           5          10
          7           5           1
          4           6           7
          6           7           5
          5           9           6
          2          10           9

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

 

만일 C2와 C3에 대해서 오름차순 정렬을 한다면

db2 +p –tv << EOF
connect to sample ;
select * from t2 where c2 > 0
/* <OPTGUIDELINES>
     <IXSCAN TABLE='T2' INDEX='T2_IDX3'/>
   </OPTGUIDELINES>
*/ ;
EOF

-- 결과

C1          C2          C3
----------- ----------- -----------
          8           3           4
          3           3           8
          7           5           1
          1           5          10
          4           6           7
          6           7           5
          5           9           6
          2          10           9

 

Embedded SQL 및 Package 에서 Guideline을 적용하려는 경우,  prepare 단계에서 profile을 선언하면 된다.

 

문서 참고: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.perf.doc%2Fdoc%2Fc0024522.html

 

테스트는 Windows, Linux, AIX에서 했으나, Windows에서는 Guideline 이 수행되지 않았다.

table scan이 발생하는 SQL을 Index Scan으로 우회할 수 있다면, Guideline을 사용하는 것이 도움을 줄 수 있을 것이다.

성능이 좋지 않은 SQL을 디자인 어드바이저(db2advis) 등을 통하여 개선되지 않는 경우, 고려를 해 보면 좋을 듯 하다.

'Note' 카테고리의 다른 글

[구성] Federation MS-SQL Server  (0) 2014.03.20
[성능] Actual Section & db2caem  (0) 2014.02.28
[이관] lobsinfile 및 tempfiles path  (0) 2014.01.27
[이관] admin_cmd 프로시저를 이용한 이관 자동화  (0) 2014.01.27
[SQL] 오라클 호환성 VARCHAR2에 대한 고찰  (0) 2014.01.07

데이터 이관 작업 시 lob 데이터를 고려하지 않고 export 나 load를 수행하게 된다.

많은 테이블에 대해서 이관 작업을 급히 수행해야 되는 경우, 이관 대상의 테이블 컬럼 정보를 확인하지 않고 수행을 하다 보면 lob 데이터가 있는 테이블은 load 시 “문자 짤림" 로그가 남게 된다.

 

데이터 코드 셋이 잘못되어서 발생하는 경우도 있지만, lob 컬럼이 속한 테이블 경우 추출/적재 시 lob 컬럼에 대한 옵션을 주지 않으면 발생하기도 한다. 또한 load 작업 시 여러 이유에서 실패를 하거나 중단되는 경우 load 작업을 위한 임시 공간이 full 발생하여 load 작업이 실패하게 되는 경우도 발생한다.

 

데이터 이관 작업 시, lob 데이터 및 대용량 데이터 이관을 고려하여 다음과 같이 export 및 import/load 구문을 사용하는 것이 바람직하지 않을까 싶다.

 

1. export 구문

  lob 컬럼이 존재하는 테이블을 LOBS TO 옵션을 주어 별도의 경로에 데이터를 저장하도록 함

  (녹색 글자: 경로 및 파일명을 의미함, {}: shell 변수들을 의미함)

export to export/${TABSCHEMA}.${TABNAME}.ixf of ixf LOBS TO lobs/${TABNAME}  modified by lobsinfile messages export/${TABSCHEMA}.${TABNAME}.exp select * from ${TABSCHEMA}.${TABNAME} with ur

 

2. load 구문

  LOB 데이터의 경로를 LOBS FROM 옵션을 통하여 지정하고, load 작업 시 필요한 임시 공간은 tempfiles path 옵션으로 지정함

load from export/${TABSCHEMA}.${TABNAME}.ixf of ixf LOBS FROM lobs/${TABNAME}  modified by lobsinfile messages ${TABSCHEMA}.${TABNAME}.msg tempfiles path tmp replace into ${TABSCHEMA}.${TABNAME}

'Note' 카테고리의 다른 글

[성능] Actual Section & db2caem  (0) 2014.02.28
[성능] SQL Optimizer Profile  (0) 2014.02.06
[이관] admin_cmd 프로시저를 이용한 이관 자동화  (0) 2014.01.27
[SQL] 오라클 호환성 VARCHAR2에 대한 고찰  (0) 2014.01.07
[개발] db2dclgn (선언부 생성기)  (0) 2013.12.17

고객사 기술지원을 하다보면, 데이터 추출/적재 작업은 개발 프로그램을 통하여 진행하고자 하는 요건이 발생한다.

개발자들이 이관 작업을 수행하는 경우는 개발 language를 통해서 구현하고 싶어하고, DBA가 수행하는 경우는 shell 방식으로 처리하는 경우가 보편적이지 않을까 싶다.

 

admin_cmd를 이용하여 다양한 관리 작업을 개발 레벨에서 관리를 할 수 있다. 자세한 정보는 정보센터를 통해서 확인이 가능하다.

문서: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0012547.html

 

0. 테스트 환경

- DB2: V10.5

- 오라클 호환성: 적용하지 않음

- 인스턴스명: db2inst

- DB 명: sample

- 테이블: employee

 

1. 데이터 추출/적재(export/load)

- procedure code

  문법 체크 및 debug를 위해서 dbms_output.put_line() 를 사용하였고, export/load의 실행 로그 확인을 위해 커서를 선언하여 출력 변수로 추출하여 반환하도록 구현하였다.

db2 +p -td@ << EOF
connect to sample @
create or replace procedure sp_load ( IN I_VAR1 char(3), OUT O_EXP_MSG varchar(256), OUT O_LOA_MSG varchar(256), OUT O_MSG varchar(256))
specific sp_load
dynamic result sets 2
begin
     DECLARE EXP_ROWS_EXPORTED  BIGINT ;
     DECLARE EXP_RETRIEVAL      VARCHAR(512);
     DECLARE EXP_REMOVAL        VARCHAR(512) ;
    
     DECLARE LOA_ROWS_READ      BIGINT ;
     DECLARE LOA_ROWS_SKIPPED   BIGINT ;
     DECLARE LOA_ROWS_LOADED    BIGINT ;
     DECLARE LOA_ROWS_REJECTED  BIGINT ;
     DECLARE LOA_ROWs_DELETED   BIGINT ;
     DECLARE LOA_ROWS_COMMITED  BIGINT ;
     DECLARE LOA_ROWS_PARTITIONED BIGINT ;
     DECLARE LOA_NUM_AGENTINFO_ENTRIES BIGINT;
     DECLARE LOA_MSG_RETRIEVAL  VARCHAR(512) ;
     DECLARE LOA_MSG_REMOVAL    VARCHAR(512) ;
    
     DECLARE P_STMT             VARCHAR(2048) ;
     DECLARE P_EXPORT_PATH      VARCHAR(512) ;
     DECLARE P_EXPORT_FILE      VARCHAR(32)  ;
    
     DECLARE EXP_RESULT             RESULT_SET_LOCATOR VARYING;
     DECLARE LOA_RESULT             RESULT_SET_LOCATOR VARYING;
    
     declare EXIT handler for SQLEXCEPTION
     begin
           get DIAGNOSTICS EXCEPTION 1 O_MSG = MESSAGE_TEXT;
     end ;
    
     SET P_EXPORT_PATH = '/db2user/db2inst/script/' ;
     SET P_EXPORT_FILE = 'emp.ixf' ;
     SET P_STMT = 'select * from emp where workdept = ''' || I_VAR1 || '''' ;
    
     call dbms_output.put_line('export to ''' || P_EXPORT_PATH || P_EXPORT_FILE || ''' of ixf messages on server ' || P_STMT) ;
    
     call sysproc.admin_cmd('export to ''' || P_EXPORT_PATH || P_EXPORT_FILE || ''' of ixf messages on server ' || P_STMT) ;
    
     associate result set locators(EXP_RESULT) with procedure sysproc.admin_cmd ;
     allocate EXP_CUR cursor for result set EXP_RESULT ;
     fetch from EXP_CUR into EXP_ROWS_EXPORTED, EXP_RETRIEVAL, EXP_REMOVAL ;
    
     call dbms_output.put_line('ROWS_EXPORTED : ' || TO_CHAR(EXP_ROWS_EXPORTED) || ',  RETRIVAL : ' || EXP_RETRIEVAL) ;
     SET O_EXP_MSG = EXP_RETRIEVAL ;
     
     
     call dbms_output.put_line('load from ''' || P_EXPORT_PATH || P_EXPORT_FILE || ''' of ixf messages on server insert into t_emp nonrecoverable indexing mode autoselect') ;
    
     call sysproc.admin_cmd('load from ''' || P_EXPORT_PATH || P_EXPORT_FILE || ''' of ixf messages on server insert into t_emp nonrecoverable indexing mode autoselect') ;

     associate result set locators(LOA_RESULT) with procedure sysproc.admin_cmd ;
     allocate LOA_CUR cursor for result set LOA_RESULT ;
     fetch from LOA_CUR into LOA_ROWS_READ, LOA_ROWS_SKIPPED, LOA_ROWS_LOADED, LOA_ROWS_REJECTED, LOA_ROWS_DELETED, LOA_ROWS_COMMITED, LOA_ROWS_PARTITIONED, LOA_NUM_AGENTINFO_ENTRIES, LOA_MSG_RETRIEVAL, LOA_MSG_REMOVAL ;
    
     call dbms_output.put_line('ROWS_READ(' ||TO_CHAR(LOA_ROWS_READ) || ') ROWS_SKIPPED(' || TO_CHAR(LOA_ROWS_SKIPPED) || ') ROWS_LOADED(' || TO_CHAR(LOA_ROWS_LOADED) || ') ROWS_REJECTED(' || TO_CHAR(LOA_ROWS_REJECTED) || ') ROWS_DELETED('|| TO_CHAR(LOA_ROWS_DELETED) || ') ROWS_COMMITED(' || TO_CHAR(LOA_ROWS_COMMITED) || ') MSG_RETRIEVAL(' || LOA_MSG_RETRIEVAL) ;
     SET O_LOA_MSG = LOA_MSG_RETRIEVAL ;
     --select sqlcode, msg into O_LOA_SQLCODE,O_LOA_MSG from table (sysproc.admin_get_msgs(LOA_MSG_RETRIEVAL)) as msg ;
     --call sysproc.admin_remove_msgs(LOA_MSG_REMOVAL) ;
    
end
@
terminate @
EOF

 

실행 테스트

   Database Connection Information

Database server        = DB2/AIX64 10.5.2
SQL authorization ID   = DB2INST
Local database alias   = SAMPLE

[db2inst@dbcni1:/db2user/db2inst/script] $ db2 set serveroutput on
DB20000I  The SET SERVEROUTPUT command completed successfully.

[db2inst@dbcni1:/db2user/db2inst/script] $ db2 "call sp_load ('A00', ?,  ?, ?)"

  Value of output parameters
  --------------------------
  Parameter Name  : O_EXP_MSG
  Parameter Value : SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('15911_503_DB2INST')) AS MSG

  Parameter Name  : O_LOA_MSG
  Parameter Value : SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('17722_11123_DB2INST')) AS MSG

  Parameter Name  : O_MSG
  Parameter Value : -

  Return Status = 0

export to '/db2user/db2inst/script/emp.ixf' of ixf messages on server select * from emp where workdept = 'A00'
ROWS_EXPORTED : 5,  RETRIVAL : SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('2786_6890_DB2INST')) AS MSG
load from '/db2user/db2inst/script/emp.ixf' of ixf messages on server insert into t_emp nonrecoverable indexing mode autoselect
ROWS_READ(5) ROWS_SKIPPED(0) ROWS_LOADED(5) ROWS_REJECTED(0) ROWS_DELETED(0) ROWS_COMMITED(5) MSG_RETRIEVAL(SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('28553_15499_DB2INST')) AS MSG

 

 

2. 데이터 적재(load cursor 방식)

export하지 않고 cursor에 담아 load 처리하는 방법

 

db2 +p -td@ << EOF
connect to sample @
create or replace procedure sp_cur ( IN I_VAR1 char(3), OUT O_LOA_MSG varchar(256), OUT O_MSG varchar(256))
specific sp_cur
dynamic result sets 1
begin
     DECLARE LOA_ROWS_READ      BIGINT ;
     DECLARE LOA_ROWS_SKIPPED   BIGINT ;
     DECLARE LOA_ROWS_LOADED    BIGINT ;
     DECLARE LOA_ROWS_REJECTED  BIGINT ;
     DECLARE LOA_ROWs_DELETED   BIGINT ;
     DECLARE LOA_ROWS_COMMITED  BIGINT ;
     DECLARE LOA_ROWS_PARTITIONED BIGINT ;
     DECLARE LOA_NUM_AGENTINFO_ENTRIES BIGINT;
     DECLARE LOA_MSG_RETRIEVAL  VARCHAR(512) ;
     DECLARE LOA_MSG_REMOVAL    VARCHAR(512) ;
    
     DECLARE P_STMT             VARCHAR(2048) ;
     DECLARE LOA_RESULT             RESULT_SET_LOCATOR VARYING;
    
     declare EXIT handler for SQLEXCEPTION
     begin
           get DIAGNOSTICS EXCEPTION 1 O_MSG = MESSAGE_TEXT;
     end ;
    
     SET P_STMT = 'select * from emp where workdept = ''' || I_VAR1 || '''' ;
    
    
     call dbms_output.put_line('load from ( database sample ' || P_STMT || ' ) of cursor messages on server insert into t_emp nonrecoverable indexing mode autoselect') ;
    
     call sysproc.admin_cmd('load from ( database sample ' || P_STMT || ' ) of cursor messages on server insert into t_emp nonrecoverable indexing mode autoselect') ;

     associate result set locators(LOA_RESULT) with procedure sysproc.admin_cmd ;
     allocate LOA_CUR cursor for result set LOA_RESULT ;
     fetch from LOA_CUR into LOA_ROWS_READ, LOA_ROWS_SKIPPED, LOA_ROWS_LOADED, LOA_ROWS_REJECTED, LOA_ROWS_DELETED, LOA_ROWS_COMMITED, LOA_ROWS_PARTITIONED, LOA_NUM_AGENTINFO_ENTRIES, LOA_MSG_RETRIEVAL, LOA_MSG_REMOVAL ;
    
     call dbms_output.put_line('ROWS_READ(' ||TO_CHAR(LOA_ROWS_READ) || ') ROWS_SKIPPED(' || TO_CHAR(LOA_ROWS_SKIPPED) || ') ROWS_LOADED(' || TO_CHAR(LOA_ROWS_LOADED) || ') ROWS_REJECTED(' || TO_CHAR(LOA_ROWS_REJECTED) || ') ROWS_DELETED('|| TO_CHAR(LOA_ROWS_DELETED) || ') ROWS_COMMITED(' || TO_CHAR(LOA_ROWS_COMMITED) || ') MSG_RETRIEVAL(' || LOA_MSG_RETRIEVAL) ;
     SET O_LOA_MSG = LOA_MSG_RETRIEVAL ;
end
@
terminate @
EOF

 

실행 테스트

$ db2 connect to sample user db2inst using
$ db2 set serveroutput on
$ db2 "call sp_cur('A00',?,?)"

  Value of output parameters
  --------------------------
  Parameter Name  : O_LOA_MSG
  Parameter Value : SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('22620_26387_DB2INST')) AS MSG

  Parameter Name  : O_MSG
  Parameter Value : -

  Return Status = 0

load from ( database sample select * from emp where workdept = 'A00' ) of cursor messages on server insert into t_emp indexing mode autoselect
ROWS_READ(5) ROWS_SKIPPED(0) ROWS_LOADED(5) ROWS_REJECTED(0) ROWS_DELETED(0) ROWS_COMMITED(5) MSG_RETRIEVAL(SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('22620_26387_DB2INST')) AS MSG

 

참고로 lob 컬럼이 있는 경우는 export 및 import/load 시 추가 옵션이 필요하며, 대용량 데이터의 load 작업 경우 temp 경로를 설정하여 주도록 한다.

'Note' 카테고리의 다른 글

[성능] SQL Optimizer Profile  (0) 2014.02.06
[이관] lobsinfile 및 tempfiles path  (0) 2014.01.27
[SQL] 오라클 호환성 VARCHAR2에 대한 고찰  (0) 2014.01.07
[개발] db2dclgn (선언부 생성기)  (0) 2013.12.17
[개발] db2jcc 와 db2jcc4 의 차이  (0) 2013.11.28

오라클 호환성 기능은 DB2_COMPATIBILITY_VECTOR 레지스트리 변수에 값을 적용함으로서 오라클 문법/표현등을 사용하게 된다.

그러나 일부 기능은 데이터베이스를 생성하기 전에만 적용되는 것들이 있다.

Developerworks에 나온 문서(https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/about_the_db2_compatibility_vector_and_what_not_to_say_at_the_dinner_table282?lang=en) 의 내용 및 기억을 더듬어보면

1. NUMBER

2. VARCHAR2

3. DATE

4. Oracle Data Dictionary

정도가 아닌가 싶다.

 

이 중 VARCHAR2를 세부적으로 들여다보면 특별한 기능을 포함하고 있다.

오라클이 공백과 NULL 값 구분을 하지 않기 때문에, DB2로 Data Migration을 하게 되면 이것은 문제가 된다. (DB2는 공백과 NULL을 구분하기에)

 

따라서 SQL 내에서 공백의 데이터를 처리하다보면 Oracle과는 다른 결과를 갖게 된다.

이 문제를 해결하기 위해서 VARCHAR2 (0x20) 값을 적용할 필요가 생긴다.

 

1. DB2에서 공백 값 테스트

$ db2 "values (COALESCE('','NULL'))"

1
----


$ db2 "values (COALESCE(' ','NULL'))"

1
----


$ db2 "values (COALESCE(NULL,'NULL'))"

1
----
NULL



$> db2 "values (bigint(''))"

1
--------------------


SQL0420N  Invalid character found in a character string argument of the function "BIGINT".  SQLSTATE=22018

 

2. 오라클의 공백 값 테스트

SQL> select nvl('','NULL') as C1 from dual;

C1
----
NULL


SQL> select nvl(' ','NULL') as C2 from dual;

C
-


SQL> select nvl(NULL,'NULL') as C3 from dual;

C3
----
NULL



SQL> select to_number('') as C4 from dual;

        C4
----------

 

4번째 SQL을 통하여 DB2에서는 “공백 값"이 숫자로 캐스팅되지 못하고 SQL0420N 오류가 발생했다.

 

오라클 호환성 (db2set DB2_COMPATIBILITY_VECTOR=20) 을 적용해도 “공백"값은 숫자형으로 형 변환되지 못한다.

 

이 경우 오라클처럼 “공백" 값을  숫자로 형변환 되도록 하기 위해서는 db2set DB2_COMPATIBILITY_VECTOR=20 를 적용하고 DB 재생성을 해야 된다.

$> db2set -all
[i] DB2_COMPATIBILITY_VECTOR=20

$> db2 drop db sample
$> db2stop
$> db2start
$> db2sampl
$> db2 connect to sample
$> db2 "values (bigint(''))"

1
--------------------
                   -

 

위 문제는 사전 테스트를 통하여 “공백"처리 문제가 발생함을 확인하는 경우 VARCHAR2 호환성을 적용되어야 할 것이다.

그렇지 않다면 SQL 수정이 되어야 할 것이다.

 

다른 관점에서는 “해당 DB가 오라클 호환성을 적용하고 생성된 DB인지” 확인하는 방법이 될 수도 있다.

예를 들어 DB2 구성 작업에 많은 수의 인스턴스 및 DB 생성 작업 시,  “오라클 호환성"을 적용하고 DB가 만들어져야 되는 상황이라면

오라클 호환성이 제대로 적용되어 생성된 것인지 확인하고 싶을 수 있다.

- 많은 반복 작업으로 작업 검증 절차를 빠뜨리거나

- 인스턴스 구성 후 자동으로 인스턴스가 실행 중인 상황에서, 호환성 적용하고 (인스턴스 재시작 없이) DB를 생성된 것은 아닌지

- 이미 구축된 DB2 환경에 (히스토리 없이 지원을 하게 되어) 오라클 호환성 관련된 문제가 발생한 경우 호환성 적용 시점이 언제인지 확인할 때

'Note' 카테고리의 다른 글

[이관] lobsinfile 및 tempfiles path  (0) 2014.01.27
[이관] admin_cmd 프로시저를 이용한 이관 자동화  (0) 2014.01.27
[개발] db2dclgn (선언부 생성기)  (0) 2013.12.17
[개발] db2jcc 와 db2jcc4 의 차이  (0) 2013.11.28
[개발] Windows에서 ESQL 컴파일 및 Connect By Siblings  (0) 2013.11.28

요즘의 개발 추세는 java 기반으로 가고 있지만, 과거 시스템에는 Embedded SQL로 개발되어진 시스템들이 많다.

특히 OS390, AS400 등의 host 시스템에서는 여러 류의 ESQL 프로그램들이 사용되는 것 같다.

 

ESQL 이 DB와 연동하기 위해서는 테이블에 대한 정의서(DDL)와 개발 언어로 변환된 변수 선언부가 필요해진다.

수많은 테이블에 대해서 테이블의 데이터 유형을 개발 언어에 맞게 변환을 수작업으로 한다면 (단순한 일에) 많은 시간이 소요될 것이다.

 

그래서 DBMS마다 테이블에 대한 유형을 개발 언어에 맞게 변환해주는 유틸리티를 제공하는 것 같다.

DB2 for LUW에서는 db2dclgn 이라는 유틸리티를 제공한다.

db2dclgn를 수행하여 테이블의 데이터 유형들이 개발언어에 맞는 유형들로 변환된 결과물을 얻을 수 있다.

(내가 정보를 덜 찾아본 것일 수 있겠지만) 호스트 시스템에서 제공하는 유틸리티보다는 효용성이 떨어지는 것 같다.

테이블에 대한 정의서는 출력을 해 주지 않기 때문이다.

 

범용 DB2 (DB2 for LUW)에서는 db2dclgn과 db2look을 같이 사용해야 개발자들에게 필요한 “선언부 생성물”이 만들어 질 듯 하다.

 

(명령어 예)
db2dclgn -d 디비명 -t 테이블명 -ㅣ COBOL  -o 출력파일.cbl -u 접속계정 -p 비밀번호

 

(테스트)
db2dclgn -d sample -t employee -b lob -l cobol -n PFIX_ -c -s employee

vi employee.cbl

01 EMPLOYEE.
  05 PFIX-EMPNO PIC X(6).
  05 PFIX-FIRSTNME.
    49 LEN PIC S9(4) COMP-5.
    49 DAT PIC X(12).
  05 PFIX-MIDINIT PIC X(1).
  05 PFIX-LASTNAME.
    49 LEN PIC S9(4) COMP-5.
    49 DAT PIC X(15).
  05 PFIX-WORKDEPT PIC X(3).
  05 PFIX-PHONENO PIC X(4).
  05 PFIX-HIREDATE PIC X(10).
  05 PFIX-JOB PIC X(8).
  05 PFIX-EDLEVEL PIC S9(4) COMP-5.
  05 PFIX-SEX PIC X(1).
  05 PFIX-BIRTHDATE PIC X(10).
  05 PFIX-SALARY PIC S9(7)V9(2) COMP-3.
  05 PFIX-BONUS PIC S9(7)V9(2) COMP-3.
  05 PFIX-COMM PIC S9(7)V9(2) COMP-3.

 

고객사 지원 시 단순하게 사용했었는데, –n (접두사), –c(접미사) 를 사용하면 개발자들에게 그나마 편할 듯 하다.

접두사는 “테이블의 스키마 명" 지정하고 접미사는 –c 옵션을 주면 컬럼명을 사용하게 된다.

-s 옵션은 “구조체 명"으로 출력파일의 첫 줄을 정의하는 옵션이다.

 

스키마 이름이 다른 동명 테이블의 dcl를 추출하는 경우, 접두사(-n) 와 구조체 명(-s) 를 통하여 구분을 지을 수 있고,

-c 옵션으로 컬럼명을 “구조체의 필드명(변수명)"으로 사용하여 테이블 컬럼 정보를 참조할 수 있을 것 같다.

 

개발지원 시, 더 테스트를 했으면 개발자 분들이 조금은 더 편하게 작업을 할 수 있도록 도움을 드렸을텐데, 지금에 와서 시간을 두고 옵션들을 테스트해 보니 어떻게 db2dclgn을 사용해야 할 지가 보인다. (정보센터에서 다양한 예제가 있었으면 도움이 되었을 텐데, 그렇지 못함을 탓해 본다.)

'Note' 카테고리의 다른 글

[이관] admin_cmd 프로시저를 이용한 이관 자동화  (0) 2014.01.27
[SQL] 오라클 호환성 VARCHAR2에 대한 고찰  (0) 2014.01.07
[개발] db2jcc 와 db2jcc4 의 차이  (0) 2013.11.28
[개발] Windows에서 ESQL 컴파일 및 Connect By Siblings  (0) 2013.11.28
[SQL] 오라클 호환성에 따른 문자열 길이 변화  (0) 2013.10.31

이전에 db2 jdbc driver 버전에 대해 기술한 적이 있다.

JAVA 6 (JDK 1.6) 버전 관점에서 설명을 하고 버전 확인하는 법들을 설명했는데, Driver 버전이 4인 경우와 아닌 경우에 따른JAVA 연동 시의 문제점을 최근에 알게 되었다.

 

문제점

- JDBC 4.0 (db2jcc4.jar) 으로 DB2와 연동하는 경우 SELECT문의 “컬럼 별명"이 반영되지 않는(무시되는) 현상

 

원인

- jdbc 4.0 에서는 ResultSetMetaData.getColumnName 과 ResultSetMetaData.getColumnLabel 이 명확히 구분되어

  ResultSetMetaData.getColumnLabel 를 사용하지 않는 이상 “컬럼 명"으로 결과 값들이 반환됨

 

정리

JDBC & SQLJ getColumnName getColumnLabel
버전 4 이전 별명 반환 별명 반환
버전 4 이상 컬럼 명 반환 별명 반환

 

DB2 V9.5 정보센터를 보면 Application Developer 변경 사항에 이에 대한 언급이 설명되어 있다.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.wn.doc%2Fdoc%2Fi0052606.html

 

DB2 V9.7 에서도 이에 대해 예제를 들어 설명해 놓았다. (오타가 있어 감안해서 봐야 될 것 같다)

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.apdv.java.doc%2Fsrc%2Ftpc%2Fimjcc_c0052593.html

 

두 버전의 호환 사용방안

(방법1) useJDBC4ColumnNameAndLabelSemantics Connection  설정

(방법2) DataSource property to DB2BaseDataSource.NO (2) 설정

'Note' 카테고리의 다른 글

[SQL] 오라클 호환성 VARCHAR2에 대한 고찰  (0) 2014.01.07
[개발] db2dclgn (선언부 생성기)  (0) 2013.12.17
[개발] Windows에서 ESQL 컴파일 및 Connect By Siblings  (0) 2013.11.28
[SQL] 오라클 호환성에 따른 문자열 길이 변화  (0) 2013.10.31
[보안] 인스턴스 구성변수(dbm cfg) authentication  (0) 2013.10.11

고객사의 connect by 관련 지원하다 C 소스 코드를 컴파일 해보는 작업을 잠시 해 보게 되었다.

윈도우 환경에서 사용되어 C 컴파일러가 필요하여 지인을 통해 무료 컴파일러인 MinGW 다운받아 설치를 했다.

그러나 bldrtn 을 이용해서 컴파일은 되지 않았다. (cl.exe 오류)

왜 오류가 날까? bldrtn을 열어보니 기본 컴파일러로 Visual Studio C++ 를 사용하도록 BLDCOMP 변수에 박여 있었다.

즉 Visual Studio를 설치해야 bldrtn을 구동할 수 있게 셋팅되어 있던 것…

MinGW 및 기타 gcc 들을 설치해 봐야 “어떤 값"을 넣어 “기본 컴파일러" 설정해야 하는지 알 수 없기에 Trial version으로 VS를 설치하였다.

 

물론 VC++이 설치되어 있다고 bldrtn이 실행되지 않는다.

환경변수 설정이 필요하다. (내 컴퓨터 > 속성 > 고급 시스템 설정 > 고급 탭 > 환경 변수)

 

1) INCLUDE 변수에 경로 추가  (stdio.h 에러)

    C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC\include

2) CLASSPATH, LIB 변수에 경로 추가

    C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC\lib    (msvcrt.lib 에러)

    C:\Program Files (x86)\Microsoft SDKs\Windows\v7.1A\Lib   (kernel132.lib 에러)

    SDK가 설치 되지 않은 경우 kernel132.lib 에러가 발생하므로 Visual Studio 업데이트 파일 실행 후 kernel132.lib 라이브러리 확인 필요

 

이후 bldrtn 이 정상적으로 수행되었다.

 

C:\Program Files\IBM\SQLLIB\samples\c>bldrtn connect_by
Microsoft (R) C/C++ Optimizing Compiler Version 17.00.51106.1 for x86
Copyright (C) Microsoft Corporation.  All rights reserved.

connect_by.c
Microsoft (R) Incremental Linker Version 11.00.51106.1
Copyright (C) Microsoft Corporation.  All rights reserved.

connect_by.def(2) : warning LNK4017: DESCRIPTION statement not supported for the
target platform; ignored
   Creating library connect_by.lib and object connect_by.exp
        1개 파일이 복사되었습니다.

 

오라클의 connect by 구문은 DB2 V9.7 이상부터 지원되고 있다.

(V9.5에서도 오라클 호환성 변수가 적용되어 지원은 된다.)

 

표준 recursive SQL 인 Common table 을 이용해서는 “위계/계층"의 순서를 살릴 수가 없다.

(common 테이블 결과는 위계/계층별로 정렬되어 보여 줌)

성능 이슈가 제기되어 DB2 의 connect by의 대안으로 ibm developer works에 소개된 c로 만든 connect by였다.

(참고: http://www.ibm.com/developerworks/data/library/techarticle/dm-0510rielau/ )

 

connect by 대상 테이블에 다른 테이블이 조인되어 사용되는 경우 siblings by 처리 시 미묘하게 값 정렬이 틀려진다.

성능은 좋았지만 적용할 수가 없었다.

 

현재까지 DB2에서 제공되는 connect by 는  self join 방식에서는 성능 문제가 없지만, 다른 테이블과 join 되는 경우 성능이 많이 느려지는 느낌을 받았고, self join 이여도 siblings by 를 추가하는 경우 느려지는 느낌을 받았다.

 

다른 테이블과의 join이 발생하는 경우,  connect by 처리를 inline view(subquery)로 처리하여 되도록 self join 만 하도록 해야 성능 저하를 막을 수 있을 것으로 보인다.

'Note' 카테고리의 다른 글

[개발] db2dclgn (선언부 생성기)  (0) 2013.12.17
[개발] db2jcc 와 db2jcc4 의 차이  (0) 2013.11.28
[SQL] 오라클 호환성에 따른 문자열 길이 변화  (0) 2013.10.31
[보안] 인스턴스 구성변수(dbm cfg) authentication  (0) 2013.10.11
[이중화] 로그 쉬핑(shipping)을 통한 복제서버 구성  (0) 2013.09.26

DB V10.5 케플러가 출시된 올해, V9.7 코브라는 이제 오래된 제품처럼 여겨진다.

얘기하고자 하는 내용은 V9.7 때 있었기 때문에 지금은 달라졌을 수도 있다고 생각한다.

 

샘플 데이터

db2 +p –tv << EOF
create table t1 (c1 int);
insert into t1 values (1),(2);
select * from t1;
EOF

 

테스트 SQL

select c1, case when c1=1 then '1234'

            else '12' end c1_string,

            length( case when c1=1 then '1234' else '12' end) c1_length

from t1

 

(Case 1) 오라클 호환성 없는 환경

C1       C1_STRING        C1_LENGTH
---       -------------       ---------------
1          1234                4
2          12                   2

 

(Case 2) 오라클 호환성 켜지 않고 생성된 DB에 ORA값을 적용한 경우

C1 C1_STRING C1_LENGTH
--- ------------- ---------------
1    1234          4
2    12             4

 

위 2개의 사례로 보면 “오라클 호환성”을 적용함으로서 데이터 길이가 틀리게 인식됨을 확인할 수 있다.

 

IBM Lab에서는 다음과 같이 정리해서 답변을 주었다.

case1) ORA mode + DB2_COMPATIBILITY_VECTOR=

case2) ORA mode + DB2_COMPATIBILITY_VECTOR=ORA

case3) Non ORA mode + DB2_COMPATIBILITY_VECTOR=

C1 C1_STRING C1_LENGTH
--- ------------- ---------------
1   1234          4
2   12             2

 

case4) Non ORA mode + DB2_COMPATIBILITY_VECTOR=ORA

C1 C1_STRING C1_LENGTH
--- ------------- ---------------
1    1234          4
2    12             4

 

ORA mode 는 “호환성 변수를 적용하고 DB 생성”함을 의미한다.

 

결론은 “호환성 모드가 아닌 DB에 ORA 값을 적용한 경우, 문자열이 CHAR로 변환”이 된다는 점이다.

즉  출력되어야 문자열 중 “가장 긴 길이를 기준”으로 문자열 길이가 정해져서, 짧은 문자열이 출력되어도 패딩처리되어 문자열 길이는 동일하게 처리되는 것이다. (varchar로 인식이 되었다면 이런 문제는 발생하지 않았을 것이다.)

 

Trace 분석 결과 (0x20은 ASCII에서 공백문자를 의미함)

10509 data DB2 UDB runtime interpreter sqlri_trace_bno_zvals fnc (3.3.112.1463.0.0)

pid 3436680 tid 2829 cpid 2711578 node 0 sec 4 nsec 200418531 probe 0

bytes 17

Data1 (PD_TYPE_DEFAULT,12) Hexdump:

6161 6161 6161 2020 2020 2020 aaaaaa

 

개인적으로 오라클 호환성 변수 사용을 권장하지는 않지만, 사용을 해야 되는 경우 F 정도까지는 무난하지 않나 싶다.

오라클 호환성 옵션 사용 시 검토할 기술문서를 소개하고 마무리 해 본다.

 

https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/about_the_db2_compatibility_vector_and_what_not_to_say_at_the_dinner_table282?lang=en

'Note' 카테고리의 다른 글

[개발] db2jcc 와 db2jcc4 의 차이  (0) 2013.11.28
[개발] Windows에서 ESQL 컴파일 및 Connect By Siblings  (0) 2013.11.28
[보안] 인스턴스 구성변수(dbm cfg) authentication  (0) 2013.10.11
[이중화] 로그 쉬핑(shipping)을 통한 복제서버 구성  (0) 2013.09.26
[관리] db2dart 와 db2 inspect  (0) 2013.08.27

 

 preprpnode 오류 내용 

Error
2632-044 The domain cannot be created due to the following errors that were detected while harvesting information from the target nodes:
svr2: 2632-068 This node has the same internal identifier as svr1 and cannot be included

 원인

preprpnode svr1 svr2 명령으로 2대의 서버 (svr1, svr2)를 한 도메인에 등록시킬 때, 각각의 서버는 서로 다른 RSCT(Reliable Scalable Cluster Technology) ID를 가지고 있어야 하는데, 어떠한 이유에서 (주로 VMware로 구축할 경우, svr1의 이미지를 그대로 복사하여 svr2를 만든 경우) RSCT ID가 동일해서 발생하는 오류임!

 조치

다음의 명령을 두 서버 중 하나에서 실행시켜 RSCT ID를 재 설정한 후, preprpnode 명령을 재 실행 함.

/usr/sbin/rsct/install/bin/recfgct

preprpnode svr1 svr2

 

저작자표시 비영리 변경금지 (새창열림)

'Troubleshooting' 카테고리의 다른 글

[일반] SQL0902C (Restore:ENOSPC (28))  (0) 2014.06.03
[접속] DB 접속 지연  (0) 2014.05.19
[암호화] SQL0902C (db2pfchr)  (0) 2013.09.26
[이중화] ERRORCODE=-4210 (pureScale:maxTransports)  (0) 2013.05.27
[연계] SQL30090N Reason Code 31 (Federation:Wrapper)  (0) 2013.02.27

+ Recent posts

티스토리툴바