DECIMAL 형식의 컬럼 길이 확인하기

Informix에서 DECIMAL 형식으로 정의된 컬럼의 길이를 확인하는 방법을 정리해보겠습니다. 컬럼 정보는 syscolumns 카탈로그 테이블에서 참조할 수 있는데, 컬럼의 길이는 collength 컬럼의 값으로 확인할 수 있습니다.

DECIMAL이나 MONEY 형식의 경우 전체 자릿수(precision), 소수 자릿수(scale)로 정의되는데 각 자릿수에 대해서 표시하는 컬럼은 없습니다. 이 정보를 나타내는 카탈로그 테이블이 있는지 IBM Community에 자문을 구했습니다.

 

먼저 Jonathan의 답변에 의하면 precision과 scale에 해당하는 값을 보여주는 컬럼은 없고 syscolumns 테이블의 collength 값을 16진수로 변환하여 확인할 수 있다고 합니다. 

$ dbaccess stores_demo -

Database selected.

> SELECT t.tabid, t.tabname, c.colno, c.colname, c.coltype, c.collength, HEX(c.collength)
> FROM "informix".systables AS t
> JOIN "informix".syscolumns AS c ON t.tabid = c.tabid
> WHERE c.coltype IN (5, 8, 261, 264);

tabid         102
tabname       orders
colno         8
colname       ship_weight
coltype       5
collength     2050
(expression)  0x00000802

tabid         102
tabname       orders
colno         9
colname       ship_charge
coltype       8
collength     1538
(expression)  0x00000602
...

coltype의 조건인 5, 8, 261, 264는 DECIMAL과 MONEY 형식을 참조하기 위한 것입니다. IBM Knowledge Center 내용에 따르면 collength 값을 16진수로 변환했을때의 가장 낮은 byte 주소가 precision, 다음으로 낮은 주소가 scale에 해당하는 값이라고 하네요.

The next example displays the data type and column length of the columns of the orders table in hexadecimal format. For MONEY and DECIMAL columns, you can then determine the precision and scale from the lowest and next-to-the-lowest bytes.

위의 orders 테이블의 경우를 예를 들면 ship_weight 컬럼의 collength 값 2050을 16진수로 변환하면 '0x00000802' 입니다. 16진수는 한자리가 4bit이므로 가장 낮은 byte 주소는 '08' 입니다. 그 다음은 '02' 이므로 최종적으로 DECIMAL(8,2) 형식의 컬럼이라는 것을 알 수 있습니다.

 

형식을 확인하는 방법은 알았는데 카탈로그 테이블의 값을 사용하여 쿼리 결과로 표시할 수 있으면 편리할 것 같다는 생각이 듭니다. 저는 16진수 문자열을 SUBSTR 함수를 사용해서 precision, scale에 해당하는 해당 16진수 부분을 BIGINT로 형변환하는 식으로 구했는데요. Art Kagel씨의 답변에 따르면 decimal.h 파일에 힌트가 있군요.

#define PRECTOT(x)      (((x)>>8) & 0xff)
#define PRECDEC(x)      ((x) & 0xff)

연산자 '>>' 와 '&' 는 C프로그램에서 비트연산을 의미합니다. '>>'의 경우 비트 우측 시프트 연산, '&'는 비트 AND 연산인데요. '& 0xff'는 최하위 8비트를 추출하기 위한, 소위 마스킹이라고도 합니다.

앞서 제가 구한 방법과 비슷하다고도 할 수 있습니다. precision의 경우 '0x00000802'를 비트 우측 시프트 연산으로 8비트 만큼 이동하게 되면 '0x00000008' 이 되고 다시 '0xff' 와 비트 AND 연산을 하면 '08' 을 추출하게 되니까요. 비트 이동 없이 '0xff'와 비트 AND 연산을 하면 2가 됩니다.

그런데 오라클이나 MySQL, PostgreSQL은 비트 연산자가 존재하는데 Informix에는 비트 시프트 연산 기능이 존재하는지 문서에 나오질 않는군요. 그래서 IBM Community에 Informix에 비트 시프트 연산 기능이 있는지 추가로 질문해서 Andreas의 답변으로 ifx_bit_rightshift 함수가 존재한다는 것을 알게 되었습니다.

SELECT t.tabid, t.tabname, c.colno, c.colname, c.coltype, c.collength, HEX(c.collength),
       decode(bitand(c.coltype, "0xff"), 5, "decimal", 8, "money", "<...>") || "(" ||
        ifx_bit_rightshift(c.collength, 8) || "," || bitand(c.collength, "0xff") || ")"
        as type
  FROM "informix".systables AS t
  JOIN "informix".syscolumns AS c ON t.tabid = c.tabid
 WHERE c.coltype IN (5, 8, 261, 264);

물론 ifx_bit_leftshift 함수도 존재하네요. BITAND, BITXOR 같은 함수는 매뉴얼에 나와있는데 비트 시프트 함수들은 문서화되지 않았거나 숨겨진 기능 같습니다. 자주 쓰이진 않겠지만 경우에 따라서 유용할 듯 합니다.

 

www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_1516.htm