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

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 레코드가 선택되었습니다.