IBM DB2 UDB Blog Center

db2의 진단로그(db2diag.log) 파일은 db2diag 유틸리티와 –fmt 옵션으로 포맷을 수정할 수 있다.

이외에도 테이블 함수를 사용해서 진단로그 내용을 SQL 방식으로 조회가 가능하다.

단 문제는 MSG 컬럼의 내용이 “줄바꿈”처리 되어 있는 경우 결과 값이 흐트러져서 나오는 문제점이 있다.

이 경우는 db2diag –fmt 옵션으로 볼 때도 마찬가지로 줄이 바뀌어져서 흐트러진다.

테이블 함수로 “줄 바꿈”하지 않고 처리할 수 있는 방법을 생각하다가 다음과 같은 함수를 사용해서 줄바꿈처리를 못하도록 변경할 수 있었다.

select varchar(replace (MSG, CHR(10),’ ‘),1024) msg
from TABLE(PD_GET_DIAG_HIST(‘MAIN’,’D’,’’,CAST(NULL as timestamp), CAST(NULL as timestamp),-2)) as T

Carriage Return 의 아스키 값이 14와 10이 있는데 db2diag 로그의 줄바꿈은 10번 값으로 되어져 있는 듯 하다.

테스트

select varchar(replace(MSG,CHR(10),' '),1024) msg
from TABLE (PD_GET_DIAG_HIST('MAIN', 'D', '', CAST (NULL AS TIMESTAMP), CAST (NULL AS TIMESTAMP), -2 ) ) AS T
where dbname='SAMPLE' AND level IN ('C','E','S','W') and timestamp > '2013-03-21'
order by TIMESTAMP
fetch first 10 rows only;

결과

MSG
-
-
-
ADM4500W A package cache overflow condition has occurred. There is no error but this indicates that the package cache has exceeded the configured maximum size. If this condition persists, you should perform additional monitoring to determine if you need to change the PCKCACHESZ DB configuration parameter. You could also set it to AUTOMATIC.
ADM4500W A package cache overflow condition has occurred. There is no error but this indicates that the package cache has exceeded the configured maximum size. If this condition persists, you should perform additional monitoring to determine if you need to change the PCKCACHESZ DB configuration parameter. You could also set it to AUTOMATIC.
ADM4500W A package cache overflow condition has occurred. There is no error but this indicates that the package cache has exceeded the configured maximum size. If this condition persists, you should perform additional monitoring to determine if you need to change the PCKCACHESZ DB configuration parameter. You could also set it to AUTOMATIC.
ADM4500W A package cache overflow condition has occurred. There is no error but this indicates that the package cache has exceeded the configured maximum size. If this condition persists, you should perform additional monitoring to determine if you need to change the PCKCACHESZ DB configuration parameter. You could also set it to AUTOMATIC.
ADM4500W A package cache overflow condition has occurred. There is no error but this indicates that the package cache has exceeded the configured maximum size. If this condition persists, you should perform additional monitoring to determine if you need to change the PCKCACHESZ DB configuration parameter. You could also set it to AUTOMATIC.
ADM4500W A package cache overflow condition has occurred. There is no error but this indicates that the package cache has exceeded the configured maximum size. If this condition persists, you should perform additional monitoring to determine if you need to change the PCKCACHESZ DB configuration parameter. You could also set it to AUTOMATIC.
-

10 레코드가 선택되었습니다.

줄바꿈처리를 하지 않은 경우

select varchar(MSG, 1024) msg
from TABLE (PD_GET_DIAG_HIST('MAIN', 'D', '', CAST (NULL AS TIMESTAMP), CAST (NULL AS TIMESTAMP), -2 ) ) AS T
where dbname='SAMPLE' AND level IN ('C','E','S','W') and timestamp > '2013-03-21'
order by TIMESTAMP
fetch first 10 rows only;

-
-
-
ADM4500W A package cache overflow condition has occurred. There is no error
but this indicates that the package cache has exceeded the configured maximum
size. If this condition persists, you should perform additional monitoring to
determine if you need to change the PCKCACHESZ DB configuration parameter. You
could also set it to AUTOMATIC.

ADM4500W A package cache overflow condition has occurred. There is no error
but this indicates that the package cache has exceeded the configured maximum
size. If this condition persists, you should perform additional monitoring to
determine if you need to change the PCKCACHESZ DB configuration parameter. You
could also set it to AUTOMATIC.

ADM4500W A package cache overflow condition has occurred. There is no error
but this indicates that the package cache has exceeded the configured maximum
size. If this condition persists, you should perform additional monitoring to
determine if you need to change the PCKCACHESZ DB configuration parameter. You
could also set it to AUTOMATIC.

ADM4500W A package cache overflow condition has occurred. There is no error
but this indicates that the package cache has exceeded the configured maximum
size. If this condition persists, you should perform additional monitoring to
determine if you need to change the PCKCACHESZ DB configuration parameter. You
could also set it to AUTOMATIC.

ADM4500W A package cache overflow condition has occurred. There is no error
but this indicates that the package cache has exceeded the configured maximum
size. If this condition persists, you should perform additional monitoring to
determine if you need to change the PCKCACHESZ DB configuration parameter. You
could also set it to AUTOMATIC.

ADM4500W A package cache overflow condition has occurred. There is no error
but this indicates that the package cache has exceeded the configured maximum
size. If this condition persists, you should perform additional monitoring to
determine if you need to change the PCKCACHESZ DB configuration parameter. You
could also set it to AUTOMATIC.

-

10 레코드가 선택되었습니다.

'Note' 카테고리의 다른 글

[설치] Activation Code 및 버전 EOS (0)	2013.04.26
[설치] CentOS 6 64bit에서 DB2 V10.1 설치를 위한 Library 설정 (0)	2013.04.26
[SQL] 오라클 호환성 VARCHAR2 (0)	2013.03.20
[권한] 사용자에게 테이블 생성 및 DML만 하게 하려면? (0)	2013.03.07
[SQL] 변경된 데이터 추적 및 V10의 신기능 시간 테이블 (Temporal Table) (0)	2013.02.14

오라클 포팅작업을 하다보니 오라클 호환성 기능에 대해 새롭게 살펴보게 되는 것 같다.

오라클에서는 숫자형으로 선언된 컬럼에 공백값(‘’)을 입력하면 NULL 값 처리가 된다. DB2에서는 공백값을 입력하면 형 변환 오류가 발생하여 입력이 실패한다. 만일 문자열로 된 숫자 값 (예: ‘1234’) 식의 값을 입력하면 자동 캐스팅 되어 입력된다.

iBatis에 대해서는 잘 모르지만, DBMS Migration 작업을 하다보면 XML 문서에 SQL문들을 모아 놓고 dynamic 하게 SQL을 실행하는 환경을 보게 된다. 아마도 XML문서에 값들도 동적으로 받아 처리되도록 하다보니, 매개변수의 데이터 유형이 DB에 선언된 컬럼 유형과는 다르게 지정되는 경우가 많다. (아마도 개발편의성 때문에 대부분은 VARCHAR로 일괄 선언하는 경우가 많지 않을까 싶다.)

이렇게 되다 보니 숫자형으로 선언된 컬럼에 문자열 값이 입력이 되다보니 DB2에서는 자동으로 형 변환을 못하고 에러를 반환하는 경우가 많다. (V9.5 이후로 오라클 호환성 기능이 나오면서 자동형변환 처리는 많이 편해진 편이다.)

오라클처럼 공백값(‘’)을 NULL 값으로 처리하도록 하기 위해서는 호환성 벡터 0x20 값이 적용되어야 하며, 적용 후 DB 생성 작업을 해야 된다.

참고로 DB2 정보센터 문서에는 “호환성 벡터”를 적용하는 경우 DB locale은 유니코드(utf-8)을 사용할 것을 권고하고 있다.

또한 DB2 DeveloperWorks 기술문서에 VARCHAR2 (0x20) 값은 위험할 수 있다고 권고되는 값이니 업무 상 필요한 경우에만 사용하도록 주의해야 한다.

공백(‘’) 테스트 (오라클)

SQL> create table t1 (c1 integer, c2 integer);
SQL> insert into t1 values (1,'');
SQL> insert into t1 values (2,' ');
ORA-01722:
SQL> insert into t1 values (3,NULL);

SQL> select * from t1;

        C1         C2
---------- ----------
         1
         3

공백(‘’) 테스트 (DB2 – 호환성 적용 전)

db2 "create table t1(c1 int, c2 int)"
db2 “insert into t1 values (1,'')”

SQL0420N 함수 "INTEGER"의 문자열 인수에 유효하지 않은 문자가 있습니다.

db2 “insert into t1 values (2,' ')”

SQL0420N 함수 "INTEGER"의 문자열 인수에 유효하지 않은 문자가 있습니다.

db2 "insert into t1 values (3,'1234')"
DB20000I The SQL command completed successfully.

db2 "insert into t1 values (4,NULL)"
DB20000I The SQL command completed successfully.

db2 "select * from t1"

C1          C2
----------- -----------
          3        1234
          4           -

2 record(s) selected.

공백(‘’) 테스트 (DB2 – 호환성 적용 후)

db2set DB2_COMPATIBILITY_VECTOR=20
db2stop force
db2start
db2 create db test
db2 connect to test
db2 "create table t1(c1 int, c2 int)"
db2 “insert into t1 values (1,'')”
DB20000I The SQL command completed successfully.

db2 "insert into t1 values (2,' ')"

SQL0420N Invalid character found in a character string argument of the function "INTEGER".

db2 "select * from t1"

C1 C2
----------- -----------
1 -

1 record(s) selected.

참고로 0x20 값 적용하고 DB 생성 후, 0x20 값을 제거해도 공백(‘’)은 NULL 값으로 인식된다.

참고

1) 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

2) 호환성 벡터 값

http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.apdv.porting.doc%2Fdoc%2Fr0052867.html

'Note' 카테고리의 다른 글

[설치] CentOS 6 64bit에서 DB2 V10.1 설치를 위한 Library 설정 (0)	2013.04.26
[SQL] 테이블 함수 PD_GET_DIAG_HIST 로 진단로그의 MSG 컬럼 데이터 줄바꿈 변경 방법 (0)	2013.03.29
[권한] 사용자에게 테이블 생성 및 DML만 하게 하려면? (0)	2013.03.07
[SQL] 변경된 데이터 추적 및 V10의 신기능 시간 테이블 (Temporal Table) (0)	2013.02.14
[이중화] ECF=0x9000053E (pureScale:db2instance -list) (0)	2013.01.11

DB2는 OS 사용자를 대상으로 권한/특권 부여하여 관리한다. 따라서 계정 인증처리는 db2ckpwd 프로세스를 통하여 처리된다.

권한/특권 부여는 인스턴스 레벨 (OS 그룹을 dbm cfg 구성 변수에 설정), DB 레벨, 테이블 공간 등의 하위 object 레벨로 이뤄진다.

대부분은 DB 레벨에서만 권한 관리하는 방식으로 고객에게 제시를 하는 편이여서, 하위 object에 대한 특권 관리를 자세히 보지는 않았었다.

고객으로부터 특정 사용자가 테이블 생성, DML, LOAD 정도만 되도록 권한 관리 방안을 요청해 와서 살펴 보았다.

V10.1에서는 V9보다 조금 더 DB레벨 권한 종류가 다양해졌다.

모든 권한을 다 테스트해 본 것은 아니지만, DATAACCESS 권한이 상당히 비중이 큰 권한이라는 것을 이번 테스트를 통해서 알게 된 것 같다.

테스트 시나리오는 다음과 같다. (CentOS 6.2, V10.1 FixPack1)

데이터베이스 명	test
인스턴스 이름	inst10
사용자 명	db2user
스토리지 그룹 명	IBMSTOGROUP
테이블 공간 명	TS_DATA TS_IDX TS_LOB
버퍼풀	IBMDEFAULTBP
페이지 단위	4K
테이블 이름	t1

<데이터베이스 생성>

$ locale
LANG=ko_KR.UTF-8

$ db2 "create db test restrictive"

(참고) restrictive 옵션은 DB의 PUBLIC 그룹에 권한 부여를 하지 않아 권한없는 OS 사용자가 테이블 접근을 하지 못하도록 하겠다는 것을 의미한다.

작년 한 고객사에서 보안 강화 작업을 한다고 하여, DB 레벨의 PUBLIC 접속 권한 회수하면서 접속 장애가 발생한 적이 있었다. 명시적으로 권한부여되지 않는 OS 계정으로 업무 처리를 하도록 되어 있었기 때문이였다.

<테이블공간 생성>

db2 +p -tv << EOF
connect to test ;
create large tablespace TS_DATA pagesize 4K managed by automatic storage using stogroup IBMSTOGROUP extentsize 16 bufferpool IBMDEFAULTBP ;
create large tablespace TS_IDX pagesize 4K managed by automatic storage using stogroup IBMSTOGROUP extentsize 16 bufferpool IBMDEFAULTBP ;
create large tablespace TS_LOB pagesize 4K managed by automatic storage using stogroup IBMSTOGROUP extentsize 16 bufferpool IBMDEFAULTBP ;
terminate ;
EOF

<권한 및 특권 부여>

db2 +p -tv << EOF
connect to test ;
grant CONNECT,CREATETAB,EXPLAIN,LOAD on database to user db2user ;
grant USE of tablespace TS_DATA to user db2user ;
grant USE of tablespace TS_IDX to user db2user ;
grant USE of tablespace TS_LOB to user db2user ;
grant usage on workload sysdefaultuserworkload to user db2user ;
terminate ;
EOF

이 상태에서 테이블을 생성하면 에러 메시지가 발생한다.

SQL0551N "DB2USER" does not have the required authorization or privilege to perform operation "EXECUTE" on object "NULLID.SQLC2J23".

SQL3015N An SQL error "" occurred during processing.

위와 같이 에러가 발생한 이유는 object 관리를 위한 시스템(메타) object에 대한 특권이 부족해서 발생한다. 그래서 시스템 object 에 대한 접근특권을 부여해 주어야 한다.

다음과 같이 shell script 롤 SYSPROC, SYSIBMADM, SYSCAT, SYSIBM,NULLID 에 속한 object의 실행 및 조회특권을 부여한다. 이 작업을 하지 않고 가능하게 하는 방법은 DATAACCESS 권한을 부여하는 것이다.

단, 타인이 생성한 object에 대해서도 DML이 가능해진다.

db2 connect to test
USER=db2user

db2 -x "select rtrim(varchar(PKGSCHEMA,10))|| '.' || varchar(PKGNAME,50) packname from syscat.packages where PKGSCHEMA='NULLID'" > pack.lst
db2 -x "select rtrim(varchar(a.TABSCHEMA,10))|| '.' || varchar(a.TABNAME,50) tabname from syscat.tables a, (values ('SYSIBMADM'),('SYSCAT'),('SYSIBM')) as b(sname) where a.TABSCHEMA=b.sname" > tab.lst
#db2 -x "select rtrim(varchar(PROCSCHEMA,10))|| '.' || varchar(SPECIFICNAME,50) procname from syscat.procedures where PROCSCHEMA='SYSPROC'" > proc.lst
db2 -x "select rtrim(varchar(ROUTINESCHEMA,10))|| '.' || varchar(SPECIFICNAME,50) funcname from syscat.routines where ROUTINESCHEMA='SYSPROC' AND ROUTINETYPE='F'" > func.lst

exec < pack.lst
while read PACKNAME
do
db2 -v "grant execute on package ${PACKNAME} to user ${USER}"
done
rm -f pack.lst

exec < tab.lst
while read TABNAME
do
db2 -v "grant select on table ${TABNAME} to user ${USER}"
done
rm -f tab.lst

#exec < proc.lst
#while read PROCNAME
#do
# db2 -v "grant execute on specific procedure ${PROCNAME} to user ${USER}"
#done
#rm -f proc.lst

exec < func.lst
while read FNAME
do
db2 -v "grant execute on specific function ${FNAME} to user ${USER}"
done
rm -f func.lst

db2 terminate

<테이블 생성>

db2 +p -tv << EOF
connect to test user db2user using db2user ;
create schema db2user ;
create table t1 (c1 varchar(10), c2 clob(1k)) IN TS_DATA INDEX IN TS_IDX LONG IN TS_LOB ;
insert into t1 values ('가나다','가가가'),('라마바','라라라') ;
terminate ;
EOF

<데이터 import>

db2 connect to test user db2user
db2 "import from t1.ixf of ixf lobs from /instance/inst10/lobs commitcount automatic messages t1.imp.msg insert into t1"

db2 "select c1, varchar(c2,10) c2 from t1"

C1         C2
---------- ----------
가나다     가가가
라마바     라라라
가나다     가가가
라마바     라라라

4 record(s) selected.

<데이터 load>

db2 "load from t1.ixf of ixf lobs from /instance/inst10/lobs messages t1.imp.msg insert into t1"

db2 "select c1, varchar(c2,10) c2 from t1"

C1         C2
---------- ----------
가나다     가가가
라마바     라라라
가나다     가가가
라마바     라라라
가나다     가가가
라마바     라라라

6 record(s) selected.

인스턴스 계정으로 생성된 테이블 조회

db2 "select * from inst10.t1"
SQL0551N "DB2USER" does not have the required authorization or privilege to perform operation "SELECT" on object "INST10.T1". SQLSTATE=42501
db2 “terminate”

테스트를 해보면서 DATAACCESS 권한이 내가 생각한 것과는 달라서 실망을 했었다.

“CREATETAB 권한만 있어도 테이블 생성은 되도록 해야 하는데, 이게 뭐냐?”

나름 이유가 있었겠지만, 하위 object에 대해서 특권을 부여해야만 테이블 생성이 제대로 되니 작업이 많아 진 것은 안타깝다.

개인적으로 DATAACCESS 권한을 시스템 object 접근하는 것으로 한정한다면,

테이블 생성 작업은 위처럼 번잡스럽지 않을 것이고, 또한 타 계정의 object는 건들지 못하므로 보안적인 측면도 좋을 것이고..

개인적으로 DATAACCESS 권한에 개선이 있었으면 하는 바램을 가져 본다.

'Note' 카테고리의 다른 글

[SQL] 테이블 함수 PD_GET_DIAG_HIST 로 진단로그의 MSG 컬럼 데이터 줄바꿈 변경 방법 (0)	2013.03.29
[SQL] 오라클 호환성 VARCHAR2 (0)	2013.03.20
[SQL] 변경된 데이터 추적 및 V10의 신기능 시간 테이블 (Temporal Table) (0)	2013.02.14
[이중화] ECF=0x9000053E (pureScale:db2instance -list) (0)	2013.01.11
[이중화] DB2 HADR with TSA 구축 시 설정해 줘야 할 사항들 (0)	2012.12.23

IBM DB2 UDB Blog Center

전체 글

[SQL] 테이블 함수 PD_GET_DIAG_HIST 로 진단로그의 MSG 컬럼 데이터 줄바꿈 변경 방법

'Note' 카테고리의 다른 글

[SQL] 오라클 호환성 VARCHAR2

'Note' 카테고리의 다른 글

[권한] 사용자에게 테이블 생성 및 DML만 하게 하려면?

'Note' 카테고리의 다른 글

+ Recent posts

티스토리툴바