국내외적으로 Oracle 사용자가 많은 관계로 (여러 DBMS의 제조사 세미나를 가보면서) Oracle은 DBMS 계의 공공의 적이 되었다는 생각이 들곤 한다. IBM DB2의 경우 Mainframe (z-Series) 분야에서는 평정한 DBMS 이나 많이 사용되는 Unix, Linux, Windows 시장에서는 그렇지 못하기에 DB2 for LUW 제품에는 오라클 SQL을 실행할 수 있는 요건이 필수적이 되었다. 오라클을 사용하는 고객을 대상으로 DB2로 전환하기 위해서는 Oracle 의 기능을 흡수할 필요도 있고 낯선 DB2를 친숙하게 하는 효과를 만들었어야 했을 것이다.

V9.1까지는 java 기반으로 만들어진 udf를 Oracle DB 전환 시 사용하였으나, V9.5부터는 DB2 자체에 변환기를 내포하기 시작했다. V9.7에서는 훨씬 많은 기능들이 내장되어 Oracle DB를 전환하는 경우 많은 수작업들을 안해도 되어 DB2 기술자의 단순 작업 부담이 줄었다 할 수 있겠다.

그러나 이로 인한 단점도 생긴다. (필자의 경우는 오라클 호환성 기능이 생기면서 부정적인 생각을 많이 했다. DB2 자체 기능으로 개발하여 보완하는 식으로 갔으면 좋은데 변환 방식으로 갔기 때문이다..)

앞으로 말하려고 하는 오라클 호환성 rownum 은 테스트를 해본 바, Oracle rownum 보다 더 일관적인 느낌이여서 낫다는 생각을 해본다.

KDUG 등에  오라클과의 rownum 비교 자료를 보곤 했는데, 그것 말고도 고려할 점이 있었다.

 

테이블 DDL Script

오라클

create table t1(c1 integer,c2 integer);
insert into t1 values(1,6);
insert into t1 values(2,5);
insert into t1 values(3,4);
insert into t1 values(4,3);
insert into t1 values(5,2);
insert into t1 values(6,1);
commit;


DB2

db2 "create table t1(c1 integer,c2 integer)"
db2 "insert into t1 values (1,6),(2,5),(3,4),(4,3),(5,2),(6,1)"

 

ORACLE DB에 저장된 데이터

ORACLE> select rownum, t1.* from t1;

    ROWNUM         C1         C2
---------- ---------- ----------
         1          1          6
         2          2          5
         3          3          4
         4          4          3
         5          5          2
         6          6          1

DB2 DB에 저장된 데이터

DB2 > db2 "select rownum, t1.* from t1"

ROWNUM               C1          C2
-------------------- ----------- -----------
                   1           1           6
                   2           2           5
                   3           3           4
                   4           4           3
                   5           5           2
                   6           6           1

 

1. ORDER BY 시의 오라클과 DB2의 차이점

오라클 결과

ORACLE> select rownum, t1.* from t1 order by c2;

    ROWNUM         C1         C2
---------- ---------- ----------
         6          6          1
         5          5          2
         4          4          3
         3          3          4
         2          2          5
         1          1          6

DB2 결과

ROWNUM               C1          C2
-------------------- ----------- -----------
                   1           6           1
                   2           5           2
                   3           4           3
                   4           3           4
                   5           2           5
                   6           1           6

오라클과 DB2의 rownum 결과값이 달라졌다.

이 결과를 보면, Oracle은 Insert 시에 rownum의 값이 만들어져 저장되는 것 처럼 여겨진다. DB2는 “최종 데이터 결과 집합”에 rownum 값을 부여하는 것처럼 여겨진다.

그러면서 의문을 하게 갖게 된다. 테이블 2개를 join하여 rownum 값을 출력시키면 어떻게 될 것인가? DB2는 “최종 데이터 결과 집합”에 부여를 하니 문제없을 것 같은데, Oracle은 rownum 값에 대해 “애매하다” (ambiguous)는 에러를 반환하게 되지 않을까? 하는 예측을 하게 된다.

이 내용관련 정리하는 시점에는 에러가 발생하지 않았지만, 맨 처음 테스트를 했을 때는 오라클에서는 이런 에러 메시지가 났었다.

ORA-01747: 열명을 올바르게 지정해 주십시오

동일 SQL을 DB2와 oracle에 동일하게 수행했으니 신택스 오류는 없었을 것으로 생각되는데, 다시 테스트를 했을 때는 동일한 오류가 발생하지 않으니 신택스 문제처럼 보여지게 된다..

위 메시지가 났을 때 “오라클은 Insert 시에 rownum 값에  많은 영향을 주는 것 처럼” 판단을 했었다.

 

2. JOIN 시의 rownum 결과

오라클

ORACLE> select rownum, t1.c1, t2.c1 from t1, t2 where t1.c1=t2.c1;

    ROWNUM         C1         C1
---------- ---------- ----------
         1          2          2
         2          3          3

DB2

DB2 > db2 "select rownum, t1.c1, t2.c1 from t1, t2 where t1.c1=t2.c1"

ROWNUM               C1          C1
-------------------- ----------- -----------
                   1           2           2
                   2           3           3

위 결과를 보면, 오라클도 DB2로 “최종 데이터 결과 집합”에 rownum을 부여하는 것으로 나온다.

이 결과로 오라클은 rownum 처리 방식을 약간 혼란스럽게 만드는 반면, DB2는 예측되는 결과를 가져다 준다. (DB2가 더 일관적인 처리를 해준다라는 생각을 한다.)

이런 결과들을 통해서 group by 절과 조건 절에 rownum을 추가하면 어떤 결과를 주게 될 것인가?를 예측해 본다면 “최종 결과 집합”에 대해 rownum 으로 값이 짤리겠지하는 예측을 하게 될 것이다.

 

3. GROUP BY 및 검색 조건의 ROWNUM

오라클

create table t1 (c1 integer, c2 varchar(3));
insert into t1 values (1,'a');
insert into t1 values (2,'a');
insert into t1 values (3,'b');

create table t2 (c1 integer, c2 varchar(3));
insert into t2 values (1,'a');
insert into t2 values (2,'b');

create table t3 (c1 integer, c2 integer);
insert into t3 values (1,1);
insert into t3 values (1,2);
insert into t3 values (1,3);
insert into t3 values (2,1);
insert into t3 values (2,2);

DB2

db2 "create table t1 (c1 integer, c2 varchar(3))"
db2 "create table t2 (c1 integer, c2 varchar(3))"
db2 "create table t3 (c1 integer, c2 integer)"
db2 "insert into t1 values (1,'a'),(2,'a'),(3,'b')"
db2 "insert into t2 values (1,'a'),(2,'b')"
db2 "insert into t3 values (1,1),(1,2),(1,3),(2,1),(2,2)"

오라클 결과

ORACLE> select t1.c1, count(t1.c2),count(t2.c1) from t1, t2, t3 where t1.c2=t2.c2 and t1.c1=t3.c1 and rownum < 4 group by t1.c1 ;


        C1 COUNT(T1.C2) COUNT(T2.C1)
---------- ------------ ------------
         1            3            3

DB2 결과

DB2> db2 "select t1.c1, count(t1.c2),count(t2.c1) from t1, t2, t3 where t1.c2=t2.c2 and t1.c1=t3.c1 and rownum < 4 group by t1.c1"

C1          2           3
----------- ----------- -----------
          1           3           3

rownum < 4 이므로 3건의 데이터가 나와야 하는데, 1건만 나온다.

예측한 결과와 다른 반응을 보여준다.

 

그렇다면 group by 만 제외한 결과는 어떻게 나올까?

오라클

ORACLE> select t1.c1, t2.c1, t3.c1 from t1, t2, t3 where t1.c2=t2.c2 and t1.c1=t3.c1 and rownum < 4 ;

        C1         C1         C1
---------- ---------- ----------
         1          1          1
         1          1          1
         1          1          1

 

DB2

DB2> db2 "select t1.c1, t2.c1, t3.c1 from t1, t2, t3 where t1.c2=t2.c2 and t1.c1=t3.c1 and rownum < 4"

C1          C1          C1
----------- ----------- -----------
          1           1           1
          1           1           1
          1           1           1

위 결과를 통해서 “이유”를 알게 되었을 것이다.

rownum 보다 group by가 우선 실행되어지는 것을!!

 

이 문제는 프로젝트 지원하면서 개발자가 페이징처리를 하면서 “왜 rownum으로 짜른 만큼의 데이터 건 수가 나오지 않느냐?” 하는 질문을 하게 되어 살펴 보게 되었다.

이런 현상에 대해 이것 저것 테스트를 하면서 위와 같은 궁금증에 대해 테스트를 해보면서 (SQL을 잘 쓰지 않아서 생기는 문제일 수도 있겠지만) 사용자의 허를 찌르는 부분이 존재하는 것을 깨닫게 되었다.

맨 처음 접했을 때는 SQL의 조그마한 변경에도 일관되지 않는 결과값들이 나와서,,별별 의심을 다 해보게 되고, DB2의 rownum에 무슨 문제가 있는 것은 아닌가 하는 부정적인 시각도 갖게 되었었다.

위 경우 페이징처리는 오라클도, DB2도 subquery (결과 집합을 한번 더 감싸서 짤라내는)로 만들어 처리할 수 밖에는 없다. (페이징처리를 하는 기준 컬럼이 인덱스를 활용할 수 있다면 다른 방법으로 처리할 수도 있겠지만..)

'Note' 카테고리의 다른 글

[이중화] db2havend (pureScale:db2instance)  (0) 2012.10.29
[개발] DB2 JDBC Driver 버전  (0) 2012.10.29
[관리] Lock 모니터링 및 세션 관리  (0) 2012.09.11
[관리] 접속한 Application 개수 및 정보 확인  (0) 2012.09.10
[개발] 오라클 정규식 함수의 이관  (0) 2012.09.10

고객사 지원 시, DB2 계정 정책은 DB 레벨에서 설정한다.

설치 작업만 하는 경우는 대부분 고객사에서 인스턴스 계정으로 업무용으로도 사용하는 것 같고, 프로젝트 업무를 지원하게 되면 테이블 공간까지 정의를 하면서 계정 정책을 수립하게 된다.

보통은 DBADM 권한을 부여해서 사용하기에 크게 문제가 되지 않았는데, DBADM 권한을 주지 않고 더 약한 권한을 주는 경우 다음과 같은 문제를 겪을 수 있다.

필자의 경우는 V10.1 에서 겪은 문제이나, 그 이하 버전에서도 접할 가능성이 있어 보인다.

계정 생성

db2 “grant CONNECT,DATAACCESS on database to USER db2user”

테이블 조회

db2 connect to sample user db2user using db2user
db2 "select count(1) from 스키마명.employee"
SQL5193N  The current session user does not have usage privilege on any enabled workloads.  SQLSTATE=42524

(참고) 본 테스트는 약간 시나리오가 인위적으로 구성되어서 테스트해본다면 재현되지 않을 수 있다.

위 문제로 해당 테이블에 테이블 레벨 권한을 부여(INDEX,INSERT,SELECT,UPDATE,DELETE) 했으나 동일한 메시지가 반환되었다.

문제는 DB2가 기본적으로 제공하는 Workload 때문이였다.

아래처럼 2개의 workload가 DB가 생성되는 시점에 생성된다. Admin용 워크로드와, User용 워크로드…

db2 "select substr(workloadname,1,30) wlname from syscat.workloads"

WLNAME
------------------------------
SYSDEFAULTUSERWORKLOAD
SYSDEFAULTADMWORKLOAD

 

db2 "select substr(a.workloadname,1,30) wlname,substr(b.grantor,1,20) grantor, substr(b.grantee,1,20) grantee, b.granteetype,usageauth from syscat.workloads a, syscat.workloadauth b where a.workloadid=b.workloadid"

WLNAME                         GRANTOR              GRANTEE              GRANTEETYPE USAGEAUTH
------------------------------ -------------------- -------------------- ----------- ---------
SYSDEFAULTUSERWORKLOAD        SYSIBM               PUBLIC               G           Y

 

문제가 난 시점에는 위 script를 조회하지 않아 정확히 설정된 값이 어떻게 되어 있는지 모르지만, 예측하기로는 SysDefaultUserWorkload의  GRANTEE가 PUBLIC 으로 되어 있지 않았을 것 같다.

(V9.1 부터 제공되는 DB 생성시 restrictive 옵션과 V9.5에서 제공된 Default Workload 영향으로 위 값는 상황에 따라 다를 수 있을 것 같다. 위 문제가 발생한 DB는 restrictive 옵션을 주면서 DB를 생성하였다.)

 

V9.7 정보센터의 내용을 살펴보니 다음과 같이 정의되어 있다.

기본 사용자 워크로드 : DB 서버에 접속 시 모든 접속에 대해 적용되는 워크로드

기본 관리자 워크로드 : 관리작업을 수행할 때 허용하게 할지 말지를 결정하는 워크로드

 

그래서 위의 문제는 다음과 같이 권한 부여를 하여 정상 실행됨을 확인하였다.

db2 “grant usage on workload sysdefaultuserworkload to user db2user”

 

db2 connect to sample user db2user using db2user

db2 "select count(1) from db2inst1.employee"

1
-----------
         42

  1 record(s) selected.

'Troubleshooting' 카테고리의 다른 글

[일반] 64-bit Module Load 관련 오류 메시지  (0) 2012.12.20
[SQL] SQL0444N Reason code 4 (built in function)  (0) 2012.12.17
[이중화] SQL1517N (pureScale:db2cluster)  (0) 2012.06.25
[이중화] ECF=0x90000542 (HADR:db2haicu)  (0) 2011.11.21
[이중화] ECF=0x90000557=-1879046825=ECF_SQLHA_CLUSTER_ERROR (HADR:db2haicu)  (0) 2011.10.19

락 상태를 확인하거나 Waiting에 빠진 상태를 확인하기 위해 db2top (U옵션) 혹은 db2pd –locks wait 등의 명령어를 통해 확인을 한다.

두 명령어 모두 서버에서만 실행가능한 명령어이기 때문에 사용의 제약이 발생한다. SQL로 실행하여 lock 상태는 다음과 같이 snapshot 함수를 통해 확인 가능하다.

 

SELECT a.locks_held,

             a.MEMBER,

              a.agent_id app_handl,

              substr(b.appl_name,1,20) app_name,

              a.APPL_CON_TIME,

              a.lock_wait_time,

              substr(b.client_nname,1,20) client_name,

              b.client_platform

FROM SYSIBMADM.SNAPAPPL AS a,

          SYSIBMADM.APPLICATIONS b

WHERE locks_held > 0 AND a.agent_id = b.agent_id

order by locks_held desc

결과 값

LOCKS_HELD  MEMBER  APP_HANDL  APP_NAME  APPL_CON_TIME  LOCK_WAIT_TIME CLIENT_NAME  CLIENT_PLATFORM

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

      2               0             80                db2bp          2012-08-24-12.14.15.363344  0  pureScale                  LINUXX8664

 

App_Handle이 80인 Application이 2개의 lock를 가지고 있음을 확인할 수 있다.

잠금 대기(Lock waiting)에 놓인 테이블을 통하여 어떤 Application에 의해 Lock Chain이 발생했는지 확인 가능하다.

select substr(tabschema,1,8) || '.' || substr(tabname,1,16) tabname,

          lock_name,

          substr(lock_object_type,1,12) lock_object_type,

          req_application_handle,

          req_member,substr(req_application_name,1,14) req_app_name,hld_member,

           hld_application_handle,

           substr(hld_application_name,1,14) hld_app_name,

           substr(req_stmt_text,1,32) req_stmt,

           substr(hld_current_stmt_text,1,26) hel_cur_stmt

from SYSIBMADM.MON_LOCKWAITS

결과 값

TABNAME LOCK_NAME LOCK_OBJECT_TYPE REQ_APPLICATION_HANDLE REQ_MEMBER REQ_APP_NAME HLD_MEMBER HLD_APPLICATION_HANDLE HLD_APP_NAME REQ_STMT HEL_CUR_STMT

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

INST10 .T1 03000800040000000000000052 ROW 70 0 db2bp 0 60 db2bp delete from t1

T1 테이블에 대해서 Lock을 잡고 있는 Application(HLD_APPLICATION_HANDLE) 은 60번이고,

Lock 을 얻고자 요청한 Application(REQ_APPLICATION_HANDLE)은 70번이다.

이 경우 lock chain 현상을 풀고자 60번 Application을 강제로 죽이면 chain현상은 풀리게 된다.

db2 “force application(‘60’)”

'Note' 카테고리의 다른 글

[개발] DB2 JDBC Driver 버전  (0) 2012.10.29
[SQL] rownum 사용 시 주의점  (0) 2012.10.22
[관리] 접속한 Application 개수 및 정보 확인  (0) 2012.09.10
[개발] 오라클 정규식 함수의 이관  (0) 2012.09.10
[SQL] Multi Action Trigger  (0) 2012.05.31

+ Recent posts

티스토리툴바