보통은 이관 작업을 위한 Oracle Federation 구성 정도가 전부인데, 일이 생겨서 MS-SQL Server의 Federation 구성을 하게 되었다.

MS-SQL 서버 Federation을 위해서는 InfoSphere Federation Server를 설치하던가, DB2 서버 설치 후 DB2 FixPack 사이트에서 관계형 wrapper와 비관계형 wrapper 설치 파일을 내려받아 설치를 해야 한다. (Advanced E/W Server Edition로는 구성 불가능)

 

또한 MS-SQL 서버에 원격 접속을 할 수 있도록 환경 구성을 위해 ODBC 클라이언트가 필요하다.

 

1. ODBC Driver 내려받기

    - 경로: ftp://ftp.software.ibm.com/software/db2ii/downloads/odbc_driver/

    - 드라이버 버전 정보: DataDirect ODBC Version 7.1

 

2. 설치 (root 계정)

    - 설치위치: /db2/v10.1f/odbc_driver  (기본 경로: /opt/IBM/ODBC_Driver)

#> export LANG=C
#> ./install.bin

 

3. 환경 설정 (인스턴스 계정)

    (1)  환경 변수 설정

$> cat ~/sqllib/userprofile << EOF

export ODBCINI=/instance/inst10f/odbc.ini
export DJX_ODBC_LIBRARY_PATH=/db2/v10.1f/odbc_driver/branded_odbc/lib
#export LD_LIBRARY_PATH=\$LD_LIBRARY_PATH:\$DJX_ODBC_LIBRARY_PATH
export LIBPATH=\$LIBPATH:\$DJX_ODBC_LIBRARY_PATH
EOF

(주의) LIBPATH는 AIX 환경 변수 임 (운영체제에 맞는 변수명 사용 필요)

 

     (2) db2dj.ini 설정

$> cat ~/sqllib/cfg/db2dj.ini << EOF

DJX_ODBC_LIBRARY_PATH=/db2/v10.1f/odbc_driver/branded_odbc/lib
ODBCINI=/instance/inst10f/odbc.ini
EOF

 

   (3) ODBC 설정

        - 설정 파일 위치: 설치경로/branded_odbc/IBM_Tools/odbc.ini

        - MSSQL서버 정보: 192.168.137.109:1433

                           계정: sa/sa1111

                           DB  : test

                           DSN: testdsn

 

[ODBC Data Sources]
mssqlserver=Microsoft SQL Server

[ODBC]
IANAAppCodePage=4
InstallDir=/db2/v10.1f/odbc_driver
Trace=0
TraceDll=/db2/v10.1f/odbc_driver/branded_odbc/lib/VMtrc00.so
TraceFile=odbctrace.out
UseCursorLib=0


[testdsn]
Driver=/db2/v10.1f/odbc_driver/branded_odbc/lib/VMsqls00.so
Description=DataDirect 7.1 SQL Server Wire Protocol
Database=test
HostName=192.168.137.109
PortNumber=1433
LogonID=sa
password=sa1111

 

        - 연결테스트

$> cd /db2/v10.1f/odbc_driver/branded_odbc/samples/example
$> ./example testdsn

Enter the user name        : sa

Enter the password         : sa1111

SQL> select * from t1;
6066:
[IBM(DataDirect OEM)][ODBC 20101 driver]2711
[IBM(DataDirect OEM)][ODBC 20101 driver]6091

C1    C2
1     aaa
2     mssql2012
3     sqlserver


SQL> select * from sys.tables;

 

 

4. Federation 서버 구성

(1) 라이브러리 확인

$> ls –al DB2_설치경로/lib64/libdb2mssql*

-r-xr-xr-x 1 bin bin  76184 2013-11-17 16:33 libdb2mssql3.so
-r-xr-xr-x 1 bin bin 506698 2013-11-17 16:33 libdb2mssql3F.so
-r-xr-xr-x 1 bin bin 356907 2013-11-17 16:33 libdb2mssql3U.so

 

(2) 서버 구성

   - 랩퍼명: sql2000

   - 서버명: sql2012

   - 노드명: testdsn   (odbc dsn 명)

   - DB명  : test

   - 사용자 매핑: inst10f(db2) 사용자를 sa(mssql) 로 매핑함

$> db2set DB2LIBPATH=$LIBPATH

$> db2set DB2ENVLIST=DB2LIBPATH

$> db2stop force

$> db2start

$> db2 connect to sample
$> db2 "create wrapper sql2000 library 'libdb2mssql3.so'"

$> db2 "create server sql2012 TYPE mssqlserver VERSION '2012' Wrapper sql2000 options(add node 'testdsn', dbname 'test')"

$> db2 "create user mapping for inst10f server sql2012 options (add remote_authid 'sa',add remote_password 'sa1111')"

$> db2 "set passthru sql2012"

$> db2 "select * from t1"

C1          C2
----------- --------------------
          1 aaa
          2 mssql2012
          3 sqlserver

  3 record(s) selected.


$> db2 "set passthru reset"

'Note' 카테고리의 다른 글

[복구] 경로재지정 복구  (0) 2014.04.15
[관리] Backup Pending 풀기  (0) 2014.03.31
[성능] Actual Section & db2caem  (0) 2014.02.28
[성능] SQL Optimizer Profile  (0) 2014.02.06
[이관] lobsinfile 및 tempfiles path  (0) 2014.01.27

두 개념 모두 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

+ Recent posts

티스토리툴바