pajama

최근에 공개된 Informix 14.10.xC6 버전에서 QUERY TIMEOUT 기능이 추가되었습니다.

아직 IBM Documentation에 설명된 내용이 없어서 며칠전에 있었던 Webinar의 내용을 참고해서 정리해보겠습니다.

QUERY TIMEOUT은 쿼리가 실행되는 시간을 제한하는 기능으로, OS 또는 DB 세션 환경 변수로 설정할 수 있습니다. onstat 명령에서 -g ses 과 -g sql 옵션을 사용하면 QUERY TIMEOUT 설정 값과 수행시간이 추가로 표시됩니다.

아래는 onstat -g sql의 출력결과입니다. Current SQL Statement 부분에만 표시됩니다. 기존에 보이는 내용에 추가로 QUERY_TIMEOUT setting과 Clock time elapsed 에 대한 정보가 표시되네요. QUERY_TIMEOUT 설정여부와 관계없이 항상 보입니다.

IBM Informix Dynamic Server Version 14.10.FC6 -- On-Line -- Up 3 days 22:36:44 -- 2664076 Kbytes
2021-06-26 09:00:13
Sess       SQL            Current            Iso Lock       SQL  ISAM F.E.
Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain
98         SELECT         stores_demo        CR  Not Wait   0    0    9.24  Off 
Current statement name : slctcur
Current SQL statement (3) :
  select * from systables, syscolumns, sysindexes
  QUERY_TIMEOUT setting: 00:00:20
  Clock time elapsed   : 00:00:15

위 내용은 QUERY_TIMEOUT을 20초로 설정한 예시인데, 쿼리 실행시간이 20초를 초과하면 아래와 같이 해당 문장이 중단됩니다. Ctrl + C 키를 누른 것과 동일한 효과입니다. 내부적으로 중단 처리 과정에서 설정된 값(20초)을 초과할 수 있다고 하네요.

...
levels           2
leaves           5.000000000000
nunique          113.0000000000
clust            10.00000000000
  213: Statement timed out or interrupted by user.
  157: ISAM error: Interrupted ISAM call
Error in line 1
Near character position 46

QUERY_TIMEOUT 설정은 아래와 같이 DML(update/insert/delete) 문장에는 적용되지 않네요.

설정된 시간을 초과해도 계속 수행됨을 알 수 있습니다.

IBM Informix Dynamic Server Version 14.10.FC6 -- On-Line -- Up 4 days 10:36:43 -- 2664076 Kbytes
2021-06-26 21:00:12
Sess       SQL            Current            Iso Lock       SQL  ISAM F.E.
Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain
106        SELECT INTO    sysmaster          CR  Not Wait   0    0    9.24  Off 
Current SQL statement (3) :
  select * from systabinfo, sysvplst, sysrawdsk into temp ttt
  QUERY_TIMEOUT setting: 00:00:20
  Clock time elapsed   : 00:02:25

공식적인 문서가 곧 나오겠지만, 현재까지 제가 확인한 바로는 QUERY_TIMEOUT 설정은 SELECT 문장에만 적용되는 것 같습니다. 잘못된 내용이나 추가로 확인한 부분에 대해서는 업데이트 하겠습니다.

안녕하세요. 오늘은 인포믹스의 AUTOLOCATE 기능에 대해 정리해보겠습니다.

인포믹스 12.10.xC3 버전부터 AUTOLOCATE라는 기능을 제공합니다. 이 기능은 테이블이나 인덱스의 배치 및 분할(fragmentation)을 자동화할 수 있습니다.

분할방식은 라운드 로빈(round-robin)으로 이뤄지고 AUTOLOCATE에 지정한 값의 수만큼 분할(fragment)이 생성됩니다.

인덱스의 경우는 라운드 로빈 방식으로 구성될 수 없으므로 적절한 페이지 크기의 단일 dbspace에 배치됩니다.

AUTOLOCATE 기능으로 테이블이나 인덱스가 배치될때 기본적으로는 모든 dbspace를 사용하는데, 사용자가 저장될 dbspace를 지정할 수도 있습니다. sysadmin 데이터베이스의 프로시저를 사용하여 autolocate database 옵션을 사용하면 됩니다. 지정한 dbspace 목록은 sysautolocate 카탈로그 테이블에서 확인할 수 있습니다.

사족으로 sysautolocate는 sysmaster 데이터베이스가 아닌 지정한 데이터베이스 별로 확인해야합니다. 저도 오늘에야 알게된 사실인데 IBM Community의 Benjamin Thompson의 답변으로 확인을 했습니다.

$ onmode -wf AUTOLOCATE=3
Value for AUTOLOCATE (3) was saved in config file.
Value of AUTOLOCATE has been changed to 3.
$ export DB_LOCALE=ko_kr.ksc
$ export CLIENT_LOCALE=ko_kr.ksc
$ dbaccess sysadmin -
Database selected.
> EXECUTE FUNCTION task("create database","demo","datadbs1","ko_kr.ksc");
(expression) Database demo created
1 row(s) retrieved.
> EXECUTE FUNCTION task("autolocate database", "demo", "datadbs1,datadbs2");
(expression) OK
$ echo "select dbsnum,dbsname[1,10],pagesize,flags from sysautolocate" | dbaccess demo
Database selected.
     dbsnum dbsname    pagesize       flags
          5 datadbs2       2048           1
          0 *                 0           2
          4 datadbs1       2048           1

위와 같이 설정한 상태에서 특정 테이블을 생성해보면 아래와 같이 datadbs1, datadbs2 두개의 dbspace에 자동으로 분할되어 생성된 것을 확인할 수 있습니다.

$ dbaccess demo -
Database selected.
> create table test (a int);
Table created.
$ dbschema -d demo -t test -ss
DBSCHEMA Schema Utility       INFORMIX-SQL Version 14.10.FC5
{ TABLE "informix".test row size = 4 number of columns = 1 index size = 0 }
create table "informix".test
  (
    a integer
  )
  fragment by round robin partition datadbs1_1 in datadbs1, partition datadbs2_2
    in datadbs2, partition datadbs1_3 in datadbs1
  extent size 8 next size 16 lock mode row;

최근에는 이런 기능들을 활용해서 일반 테이블은 AUTOLOCATE 설정을 따르게 하고, 대용량 테이블의 경우는 INTERVAL에 의한 분할 방식으로 구성하려고 하는 편입니다.

https://www.ibm.com/docs/en/informix-servers/14.10?topic=parameters-autolocate-configuration-parameter

Troubleshooting is a systematic approach to solving a problem. The goal of troubleshooting is to determine why something does not work as expected and how to resolve the problem. Certain common techniques can help with the task of troubleshooting.

The first step in the troubleshooting process is to describe the problem completely. Problem descriptions help you and the IBM® technical-support representative know where to start to find the cause of the problem. This step includes asking yourself basic questions:

• What are the symptoms of the problem?
• Where does the problem occur?
• When does the problem occur?
• Under which conditions does the problem occur?
• Can the problem be reproduced?

The answers to these questions typically lead to a good description of the problem, which can then lead you to a problem resolution.

What are the symptoms of the problem?

When starting to describe a problem, the most obvious question is "What is the problem?" This question might seem straightforward; however, you can break it down into several more-focused questions that create a more descriptive picture of the problem. These questions can include:

• Who, or what, is reporting the problem?
• What are the error codes and messages?
• How does the system fail? For example, is it a loop, hang, crash, performance degradation, or incorrect result?

Where does the problem occur?

Determining where the problem originates is not always easy, but it is one of the most important steps in resolving a problem. Many layers of technology can exist between the reporting and failing components. Networks, disks, and drivers are only a few of the components to consider when you are investigating problems.

The following questions help you to focus on where the problem occurs to isolate the problem layer:

• Is the problem specific to one platform or operating system, or is it common across multiple platforms or operating systems?
• Is the current environment and configuration supported?
• Do all users have the problem?
• (For multi-site installations.) Do all sites have the problem?

If one layer reports the problem, the problem does not necessarily originate in that layer. Part of identifying where a problem originates is understanding the environment in which it exists. Take some time to completely describe the problem environment, including the operating system and version, all corresponding software and versions, and hardware information. Confirm that you are running within an environment that is a supported configuration; many problems can be traced back to incompatible levels of software that are not intended to run together or have not been fully tested together.

When does the problem occur?

Develop a detailed timeline of events leading up to a failure, especially for those cases that are one-time occurrences. You can most easily develop a timeline by working backward: Start at the time an error was reported (as precisely as possible, even down to the millisecond), and work backward through the available logs and information. Typically, you need to look only as far as the first suspicious event that you find in a diagnostic log.

To develop a detailed timeline of events, answer these questions:

• Does the problem happen only at a certain time of day or night?
• How often does the problem happen?
• What sequence of events leads up to the time that the problem is reported?
• Does the problem happen after an environment change, such as upgrading or installing software or hardware?

Responding to these types of questions can give you a frame of reference in which to investigate the problem.

Under which conditions does the problem occur?

Knowing which systems and applications are running at the time that a problem occurs is an important part of troubleshooting. These questions about your environment can help you to identify the root cause of the problem:

• Does the problem always occur when the same task is being performed?
• Does a certain sequence of events need to happen for the problem to occur?
• Do any other applications fail at the same time?

Answering these types of questions can help you explain the environment in which the problem occurs and correlate any dependencies. Remember that just because multiple problems might have occurred around the same time, the problems are not necessarily related.

Can the problem be reproduced?

From a troubleshooting standpoint, the ideal problem is one that can be reproduced. Typically, when a problem can be reproduced you have a larger set of tools or procedures at your disposal to help you investigate. Consequently, problems that you can reproduce are often easier to debug and solve.

However, problems that you can reproduce can have a disadvantage: If the problem is of significant business impact, you do not want it to recur. If possible, re-create the problem in a test or development environment, which typically offers you more flexibility and control during your investigation.

• Can the problem be re-created on a test system?
• Are multiple users or applications encountering the same type of problem?
• Can the problem be re-created by running a single command, a set of commands, or a particular application?

출처 : https://www.ibm.com/docs/en/informix-servers/12.10?topic=support-techniques-troubleshooting-problems

You can perform routine administrative tasks that can prevent assertion failures or help prepare for recovering from assertion failures.

To prevent assertion failures on Informix®, perform the following tasks regularly:

Table 1. Preventing assertion failuresPrevention taskPurpose

To prepare for assertion failures on Informix, perform the following tasks regularly:

Table 2. Preparing for assertion failuresPreparation taskPurpose

출처 : https://www.ibm.com/docs/en/informix-servers/14.10?topic=failures-prevent-prepare-assertion

얼마전에 고객사 인포믹스에서 243 오류(Could not position within a table table-name)가 발생해서 onmode -I 명령으로 동일한 오류가 발생했을 때 진단 정보를 수집하도록 설정해두었습니다. 

그런데 진단 정보를 수집하는 설정 상태를 확인하는 방법이 있는지 알고싶어 IBM Community에 질문 글을 썼습니다.

onmode -I 명령을 수행하면 아래와 같이 인포믹스 온라인 로그에 메시지가 표시되기는 하지만 현재 설정되었는지 여부를 확인하는 방법을 알고 싶었습니다.

11:55:15  Verbose error trapping set, errno = 243, session_id = -1

질문 글을 올리고 금방 답변을 받았는데.. onstat 명령에 숨겨진 옵션이 있더군요.

$ onmode -I 243
$ onstat -g ras
IBM Informix Dynamic Server Version 11.70.FC9 -- On-Line -- Up 01:07:23 -- 23486904 Kbytes
 Diagnostics           Current Settings
 AFCRASH               0x00002481
 AFFAIL                0x00000401
 AFWARN                0x00000401
 AFDEBUG               0
 AFLINES               0
 BLOCKTIMEOUT          3600
 Block Status          Unblocked
 CCFLAGS               0x00000000
 CCFLAGS2              0x00000200
 DUMPCORE              0
 DUMPGCORE             0
 DUMPSHMEM             1
 DUMPCNT               1
 DUMPDIR               /work1/informix/ids1150fc6/tmp
 RHEAD_T               0x00000000541c3800
 SYSALARMPROGRAM       /work1/informix/ids1150fc6/etc/evidence.sh
 LTXHWM                70%
 LTXEHWM               80%
 DYNAMIC_LOGS          2
Traperror info:
 Errno          Session(s)
 243            All

onstat -g ras 명령의 출력 결과입니다. -g ras 옵션은 onstat 도움말과 IBM의 매뉴얼에 설명되지 않은 숨겨진 옵션입니다. 결과의 Traperror info 부분에서 현재 에러번호(Errno)와 특정 세션번호에 대해 진단 정보를 수집하도록 설정되어 있음을 확인할 수 있습니다.

안녕하세요. CURSOR Service Distribution의 2021년 1분기 뉴스레터에서 꽤 유용한 정보를 발견해서 정리해보려고 합니다. 참고로 CURSOR Service Distribution은 독일에서 Informix와 Db2에 대한 서비스를 제공하는 회사입니다.

인포믹스에서 테이블을 정의할 때 Smart Blob (데이터 유형 BLOB 또는 CLOB) 컬럼에 대해 스토리지 공간 (SBSpace)을 지정할 수 있습니다. 저장 위치를 ​​지정하지 않으면 $ONCONFIG의 SBSPACE 및 SYSSBSPACE에 설정한 값이 사용되죠.  여러개의 SBSpace를 저장 위치로 지정하면 row별로 SBSpace에 분산되는데, 정확한 저장 위치를 SQL 쿼리로 확인할 수 있다고 합니다.

SBSpace 위치를 확인하는 SQL 쿼리를 사용할 때는 Little Endian (Linux, Windows)과 Big Endian (AIX, SPARC Solaris..) 시스템을 구분해서 작성해야 한다네요.

Big Endian 시스템에서 SBSpace 번호를 확인하는 방법은 비교적 간단합니다.

("0x" || substr(<sblob_column>::lvarchar,17,8))::INT

Little Endian의 경우 좀 복잡해집니다.

("0x"|| substr(<sblob_column>::lvarchar,23,2)
     || substr(<sblob_column>::lvarchar,21,2)
     || substr(<sblob_column>::lvarchar,19,2)
     || substr(<sblob_column>::lvarchar,17,2))::INT

아래는 리눅스 환경에서의 테스트를 위한 SQL문장입니다. AIX같은 Big Endian에서는 쿼리 내용을 수정해야겠죠.

-- 테이블 생성
create table blob_test (
objekt_nr int,
doc blob
) put doc in (sbdbs,sbdbs2)
;
-- 샘플 데이터 입력
insert into blob_test values (42, filetoblob("/etc/passwd","server"));
insert into blob_test values (43, filetoblob("/etc/hosts","server"));
insert into blob_test values (44, filetoblob("/etc/services","server"));
-- 각 row별 BLOB 데이터가 저장된 SBSpace 확인
select objekt_nr , 
       ("0x"||substr(doc::lvarchar,23,2)
            ||substr(doc::lvarchar,21,2)
            ||substr(doc::lvarchar,19,2)
            ||substr(doc::lvarchar,17,2))::INT as space_nr,
       (select name::char(40)
          from sysmaster:sysdbspaces 
         where dbsnum = ("0x"||substr(doc::lvarchar,23,2)
                             ||substr(doc::lvarchar,21,2)
                             ||substr(doc::lvarchar,19,2)
                             ||substr(doc::lvarchar,17,2))::INT) as spacename
  from blob_test
 where doc is not null;

아래는 제가 AIX에서 테스트 해본 결과입니다.

> dbaccess stores_demo -
select objekt_nr , 
       ("0x" || substr(doc::lvarchar,17,8))::INT as space_nr,
       (select name::char(40)
       from sysmaster:sysdbspaces 
       where dbsnum = ("0x" || substr(doc::lvarchar,17,8))::INT) as spacename
  from blob_test
 where doc is not null;
   objekt_nr    space_nr spacename
         42          11 sbspace1
         43          15 sbspace2
         44          11 sbspace1
3 row(s) retrieved.

이 쿼리는 공식적으로 문서화되지 않았기 때문에 향후에 변경될 가능성이 있다고 합니다.

제가 테스트한 버전은 12.1과 14.1입니다. SQL 사용전에 맞는 정보가 표시되는지 테스트는 해보시기 바랍니다.

www.cursor-distribution.de/de/ibm-software/informix-fuer-administratoren/informix-newsletter-ibm/send/434-informix-newsletter/6364-informix-nl-2021q1

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에 해당하는 값이라고 하네요.

위의 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

Informix 서비스가 온라인 상태에서 어떻게 백업이 수행되는지 조사해봤습니다. 오랜기간 Informix를 경험했으면서도 아직도 내부 아키텍처에 정확히 알지 못하고 있었네요.

Informix 데이터베이스가 동시에 여러 트랜잭션에 의해 변경되는 중에도 백업을 수행할 수 있는 이유는 Physical Log가 있기 때문입니다. 백업을 시작한 이후부터 데이터가 저장된 청크의 페이지에 변경이 일어나면, 변경된 데이터의 기존 이미지(before-images)를 Physical Log에 기록합니다. 그리고 다시 temp table에 before-images를 복사하여 최종적으로는 백업 미디어(tape)에 기록한다고 하네요. 아래는 그 내용을 도식화한 그림입니다.

백업이 시작된 이후부터는 데이터 청크의 변경된 페이지는 무시합니다. before image를 가지고 있고 최종적으로는 Logical Log로 복구하기 때문이겠죠? 그리고 tape은 연속적으로 기록되어야하므로, before image는 백업 수행 도중에 기록되는 것 같습니다.

백업 수행중에 데이터를 변경할 때, before image가 temp table로 생성되는지 간단한 테스트를 해봤습니다.

ontape 백업이 실행중인 상태에서, 데이터를 증식하여 입력해보니 temporary dbspace에 temp table이 생기는 것을 확인할 수 있었습니다.

$ cat q1.sql
insert into customer_copy select * from customer connect by level < 6;
insert into customer_copy select * from customer connect by level < 7;
insert into customer_copy select * from customer connect by level < 8;
$ nohup dbaccess stores_demo q1.sql &
$ oncheck -pe tempdbs1
DBspace Usage Report: tempdbs1            Owner: informix  Created: 07/03/2013
 Chunk Pathname                             Pagesize(k)  Size(p)  Used(p)  Free(p)
     3 /informix/dbspace/tempdbs1                     4   250000   131125   118875
 Description                                                   Offset(p)  Size(p)
 ------------------------------------------------------------- -------- --------
 RESERVED PAGES                                                       0        2
 CHUNK FREELIST PAGE                                                  2        1
 (0x300001) tempdbs1:'informix'.TBLSpace                              3       50
 (0x300002) stores_demo:'informix'._temptable                        53   131072
 FREE                                                            131125   118875

해당 temp table(_temptable)은 트랜잭션이 종료되면 oncheck 명령으로 보았을 때 사라지는 걸로 봐선 곧바로 백업 파일에 기록되는 것 같습니다. temp dbspace 공간이 부족해서 백업이 실패하는 경우도 간혹 있으니 참고하세요~

인포믹스에서 함수 인덱스를 찾는 방법에 대한 질문이 있어서 찾아보았습니다.

우선 카탈로그 테이블인 sysindexes 부터 살펴보니 함수 인덱스임을 표시하는 컬럼은 없었습니다.

그래서 sysindexes 테이블의 함수 인덱스 정보가 다른 인덱스와 차이가 있는지 확인해봤습니다.

먼저 테이블과 함수 인덱스를 만들었습니다. 내용은 IBM 문서를 참고했습니다.

www.ibm.com/developerworks/data/library/techarticle/dm-0712wilcox/index.html

$ dbaccess stores_demo -
Database selected.
> CREATE TABLE circles ( radius FLOAT );
Table created.
> CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
>     WITH (NOT VARIANT);
>
>     RETURN 3.14159 * radius * radius;
> END FUNCTION;
Routine created.
> CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );
Index created.

해당 인덱스에 대한 sysindexes 테이블의 정보를 확인해보았습니다.

> select * from sysindexes where idxname='areaofcircleindex';
idxname    areaofcircleindex
owner      informix
tabid      162
idxtype    D
clustered
part1      0
part2      0
part3      0
part4      0
part5      0
part6      0
part7      0
part8      0
part9      0
part10     0
part11     0
part12     0
part13     0
part14     0
part15     0
part16     0
levels     1
leaves     1.000000000000
nunique    0.00
clust      0.00
1 row(s) retrieved.

특이하게도 일반 인덱스와 달리 part1 값이 0으로 표시됩니다. 인덱스를 만들 때 컬럼 순서를 바꾸고 일반 컬럼과 혼용해도 동일한 결과를 보여줍니다. 그럼 part1 값이 0이면 함수 인덱스인가? 라는 생각이 들어서 정말 그런지 실행해보았습니다.

> select owner, idxname from sysindexes where tabid > 99 and idxname[1,1] <> ' ' and part1 = 0;
owner    informix
idxname  ucnameindex
owner    informix
idxname  ts_data_location_spix
owner    informix
idxname  areaofcircleindex
3 row(s) retrieved.

데모용 데이터베이스인 stores_demo에서 수행해보니 opclass에 대한 인덱스와 함수 인덱스 두개가 보입니다. 정확히 함수 인덱스만 나오는 것은 아니고 일반적인 타입의 인덱스는 제외되는 것 같습니다. 어설프지만 확인은 가능하네요.

그래서 좀 더 정확한 방법에 대한 조언을 구하기위해 IBM Community에 질문을 올렸습니다. 바로 Art Kagel씨와 Paul Watson씨가 명쾌한 답을 주시는군요. 답은 sysindices 카탈로그 테이블에 있었습니다.

> select idxname from sysindices where indexkeys::lvarchar matches '*<[0-9]*>*';
idxname  ucnameindex
idxname  areaofcircleindex
2 row(s) retrieved.
> select idxname, indexkeys from sysindices where indexkeys::lvarchar matches '*<[0-9]*>*';
idxname    ucnameindex
indexkeys  2 [1], <1757>(3) [1], 1 [1]
idxname    areaofcircleindex
indexkeys  <1758>(1) [1]
2 row(s) retrieved.

sysindices 테이블의 indexkeys 컬럼 값에 함수 인덱스에 대한 정보가 있더군요. opclass에 대한 정보도 있는데 이부분은 저도 잘은 모르고 이 내용과는 벗어나는 내용이라 넘어가겠습니다. indexkeys 컬럼에도 딱 이 인덱스가 함수 인덱스다! 라고 알려주는 플래그 값이 있는건 아니지만, 함수 인덱스인 경우 <> 사이에 procid 값 정보가 있습니다. 이 정보로 인덱스가 함수 인덱스임을 알 수 있는 것입니다.

아래는 IBM Knowledge Center의 sysindices 테이블에 대한 설명입니다.

The fields within the indexkeys columns have the following significance:

• The procid (as in sysprocedures) exists only for a functional index on return values of a function defined on columns of the table.
• The list of columns (col1, col2, ... , coln) in the second field identifies the columns on which the index is defined. The maximum is language-dependent: up to 341 for an SPL or Java™ UDR; up to 102 for a C UDR.
• The opclassid identifies the secondary access method that the database server used to build and to search the index. This is the same as the sysopclasses.opclassid value for the access method.
•  

IBM Community에서 좋은 것을 배웠네요.

얼마전에 고객사에 Informix 12.10.FC14버전을 설치했었는데 기존에 실행했던 쉘스크립트가 실행이 안되더군요. 오늘 IBM My Notifications 메일을 보고 관련된 내용을 알게 되어 공유하고자 합니다.

관련된 문서는 아래 링크에서 확인하실 수 있습니다.

www.ibm.com/support/pages/when-invoking-dbinfodbspacepartnum-error-727-raised

DBINFO 함수를 호출할 때 발생하는 문제인데요. partnum 값이 작은 경우 727 오류가 발생합니다.

$ echo "select first 1 dbinfo('dbspace',partnum) from systabnames;" | dbaccess sysmaster
Database selected.
  727: Invalid or NULL TBLspace number given to dbinfo(dbspace).
Error in line 1
Near character position 56
Database closed.

이전까지는 partnum이 가장 작은 값은 1048577이었지만 12.10.xC14 버전부터는 pseudo-tables, 소위 의사 테이블이 몇가지 추가되었다고 합니다. 14버전은 14.10.xC2 부터라는군요. 아래처럼 partnum 값이 6부터 153 까지 몇개 추가되었음을 알 수 있습니다.

$ echo "select first 10 partnum from sysmaster:systabnames;" | dbaccess stores_demo
Database selected.
    partnum
          6
         10
         15
         89
        153
    1048577
    1048578
    1048579
    1048580
    1048582

따라서 DBINFO 사용시 오류를 회피하기 위해서는 partnum 값이 1048576 이상인 값을 대상으로 조회해야 오류가 발생하지 않는다고 합니다. 관리적인 목적으로 DBINFO 함수를 사용하고 있다면 조건을 추가해야겠네요. 제가 테스트한바로는 위에서 보이는 153 이상의 조건으로도 오류가 발생하지는 않지만 IBM에서 권고하는 방법을 사용하는게 좋겠죠.

IBM문서에는 DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM 환경변수를 설정하면 된다고 합니다만 설정 전후 동작에 차이가 없는 것 같습니다. IBM에 별도의 패치버전을 요청해야되나 봅니다.

$ export DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM=1
$ echo "select first 1 dbinfo('dbspace',partnum) from systabnames;" | dbaccess sysmaster
Database selected.
  727: Invalid or NULL TBLspace number given to dbinfo(dbspace).
Error in line 1
Near character position 56
Database closed.
$ dbaccess sysmaster -
Database selected.
> set environment DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM "1";
19840: Invalid session environment variable.
Error in line 1
Near character position 65
>

12.10.xC14 버전 이상을 사용하실 때 참고하시면 좋겠습니다.