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

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

안녕하세요. 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

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

우선 카탈로그 테이블인 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에서 Oracle 데이터베이스 참조하기 위한 구성을 테스트해봤습니다.

마찬가지로 Enterprise Gateway Manager를 사용했습니다. 그런데 Informix 데이터베이스에 접속한 상태에서 Oracle 데이터베이스를 참조하려고 하니 908 오류가 발생하면서 접속이 되지 않더군요. 그래서 이번에는 EGM 7.31.UD3 버전으로 테스트를 시도해봤습니다.

1. 테스트 환경

① O/S : CentOS 7

② 클라이언트 : Informix Server 12.10, Informix Server 14.10

③ 접속 대상 : Oracle 11gR2, SQL Server 2017 on Linux

④ EGM 버전 : 7.31.UD3

2. 환경변수 설정

별도 EGM 인스턴스를 기동해야해서 INFORMIXSERVER, INFORMIXDIR을 수정했습니다.

Oracle을 사용하려는 경우 egmenv.sh 에서 ORACLE_HOME 환경변수를 지정하라고 합니다.

사실 ODBC를 사용하는 경우 tnsnames.ora 파일만 지정하면 되고 별도 라이브러리는 필요없으므로 ORACLE_HOME 설정이 되어있지 않어도 접속이 되었습니다. 따라서 오라클 클라이언트를 별도로 설치하지 않아도 됩니다. 이건 기본 테스트라 그렇지 설치가 필요한 경우도 있겠죠?

3. odbc.ini 파일 수정

EGM 7.31.UD4 버전의 ODBC 드라이버와 odbc.ini 파일을 참고하여 설정했습니다. 앞서 egmenv.sh 스크립트에서 ODBC 드라이버 매니저는 7.31.UD3의 라이브러리를 참조하도록 했습니다. (LD_LIBRARY_PATH=$INFORMIXDIR/egm/odbc/lib)

그리고 접속 대상인 Oracle 서버의 접속정보인 tnsnames.ora 파일의 경로를 TNSNamesFile에 설정하면 됩니다.

[oracle]
Driver=/work1/informix/egm/egm/odbc/lib/IXora27.so
Description=DataDirect 7.1 Oracle
AlternateServers=
ApplicationUsingThreads=1
ArraySize=7.100
CatalogIncludesSynonyms=1
CatalogOptions=0
ClientVersion=9iR2
ConnectionRetryCount=0
ConnectionRetryDelay=3
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
LoadBalancing=0
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
OptimizeLongPerformance=0
Password=
ProcedureRetResults=0
ReportCodePageConversionErrors=0
ServerName=test
TimestampEscapeMapping=0
TNSNamesFile=/work1/informix/egm/tnsnames.ora
UseCurrentSchema=1

4. SQL 실행 테스트

Oracle의 샘플 스키마인 scott 계정의 emp테이블을 Informix 데이터베이스로 가져와봅니다.

$ export CLIENT_LOCALE=en_us.819
$ export DB_LOCALE=en_us.819
$ echo "create table emp as select * from oracle@egm731ud3:\"SCOTT\".emp" | dbaccess stores_demo
Database selected.
14 row(s) retrieved into table.
Database closed.
[informix@db2 1210FC12W1XC]$ echo "select * from emp" | dbaccess stores_demo
Database selected.
 empno ename      job          mgr hiredate                  sal      comm deptno
  7369 SMITH      CLERK       7902 1980-12-17 00:00:00    800.00               20
  7566 JONES      MANAGER     7839 1981-04-02 00:00:00   2975.00               21
  7782 CLARK      MANAGER     7839 1981-06-09 00:00:00   2450.00               11
  7844 TURNER     SALESMAN    7698 1981-09-08 00:00:00   1500.00      0.00     30
  7902 FORD       ANALYST     7566 1981-12-03 00:00:00   3000.00               20
  7499 ALLEN      SALESMAN    7698 1981-02-20 00:00:00   1600.00    300.00     30
  7654 MARTIN     SALESMAN    7698 1981-09-28 00:00:00   1250.00   1400.00     30
  7788 SCOTT      ANALYST     7566 1987-04-19 00:00:00   3000.00               20
  7876 ADAMS      CLERK       7788 1987-05-23 00:00:00   1100.00               20
  7934 MILLER     CLERK       7782 1982-01-23 00:00:00   1300.00               10
  7521 WARD       SALESMAN    7698 1981-02-22 00:00:00   1250.00    500.00     30
  7698 BLAKE      MANAGER     7839 1981-05-01 00:00:00   2850.00               31
  7839 KING       PRESIDENT        1981-11-17 00:00:00   5000.00               10
  7900 JAMES      CLERK       7698 1981-12-03 00:00:00    950.00               31

Informix와 Oracle 테이블간의 inner join도 실행해보고 실행계획도 확인해봤습니다. 이것 참 재미있네요. Informix 테이블에 대한 정보만 표시되고 Oracle의 테이블에 대해서는 REMOTE PATH로 표시되네요.

$ echo "set explain on ;select b.* from emp a, oracle@egm731ud3:\"SCOTT\".emp b where a.empno = b.empno and a.empno = 7369" | dbaccess stores_demo
Database selected.
Explain set.
 empno ename      job          mgr hiredate                  sal      comm deptno
  7369 SMITH      CLERK       7902 1980-12-17 00:00:00    800.00               20
1 row(s) retrieved.
Database closed.
$  cat sqexplain.out
QUERY: (OPTIMIZATION TIMESTAMP: 09-14-2020 17:33:20)
------
select b.* from emp a, oracle@egm731ud3:"SCOTT".emp b where a.empno = b.empno and a.empno = 7369
Estimated Cost: 6
Estimated # of Rows Returned: 1
  1) informix.a: SEQUENTIAL SCAN  (Serial, fragments: ALL)
  2) informix.b: REMOTE PATH
    REMOTE SESSION ID FOR 'egm731ud3' is UNKNOWN
    Remote SQL Request:
    select x0.empno ,x0.ename ,x0.job ,x0.mgr ,x0.hiredate ,x0.sal ,x0.comm ,x0.deptno from oracle:"SCOTT".emp x0 where ((? = x0.empno ) AND (x0.empno = 7369 ) )                            
NESTED LOOP JOIN
Query statistics:
-----------------
  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                a
  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     14         1         14         00:00.00   4
  type     rows_prod  est_rows  time       est_cost
  -------------------------------------------------
  remote   1          1         00:00.01   0
  type     rows_prod  est_rows  time       est_cost
  -------------------------------------------------
  nljoin   1          1         00:00.01   7

SQL Server의 경우도 inner join이 잘 실행되었습니다. 테스트 환경이라 ODBC 라이브러리가 꼬인건지 모르겠지만.. 실행이 되는건 확인했네요. ODBC 드라이버 매니저와 드라이버가 적절하게 구성되는 것이 제일 중요한 것 같습니다. 인터넷에서 문서들을 찾다보면 unixODBC와 각 데이터베이스에서 제공되는 ODBC 드라이버로 구성한 사례들도 있는데 저는 라이브러리를 새로 컴파일하기가 귀찮아서 기본으로 제공되는 라이브러리들로 테스트했습니다. 깔끔한 상태의 컴퓨터에서 테스트해보시길 권장드립니다.

안녕하세요. Informix는 기본적으로 Informix간의 데이터베이스 상호 참조는 가능하지만, 이기종 데이터베이스를 참조하는 기능은 없습니다. 이기종 데이터베이스를 참조하려면 Informix Enterprise Gateway Manager(EGM)가 필요한데요. EGM은 아래 그림과 같이 Informix Server와 별도의 프로세스를 거쳐서 이기종 데이터베이스에 접근할 수 있도록 해줍니다.

Enterprise Gateway는 DRDA 프로토콜과 ODBC를 사용하는 두가지 제품군이 있습니다. DRDA 프로토콜로는 DB2/zos와 UDB에, ODBC로는 DataDirect 사에서 제공하는 ODBC 관리자 및 드라이버를 통해 Oracle, SQL Server, MySQL 등의 데이터베이스에 접근할 수 있습니다. 한가지 주의할 점은 32비트 ODBC 관리자를 사용하므로 DataDirect에서 제공되지 않는 ODBC 드라이버를 사용할 경우 32비트용으로 사용해야한다는 하는 점입니다.

저는 EGM 7.31.UD4 버전을 설치하고 SQL Server에 접속하는 테스트를 해보았습니다. 시행착오가 좀 있었는데 구성 자체는 어렵지 않았습니다.

1. EGM 설치환경

① O/S : CentOS 7

② 클라이언트 : Informix Server 14.10.FC4W1

③ 접속대상DB : SQL Server 2017 on Linux

클라이언트와 접속대상DB가 같은 호스트에 있는 상태에서 구성 및 테스트를 했습니다.

2. EGM 구성순서

① EGM 설치 

설치전에 INFORMIXDIR 설정이 필요하고, 설치는 root 계정으로 합니다.

② EGM 인스턴스의 sqlhosts 파일 작성

Informix Server와 동일한 형식의 sqlhosts 파일을 작성합니다. 위치는 $INFORMIXDIR/etc 입니다.

③ EGM 환경변수 파일 수정

INFORMIXSERVER와 LD_LIBRARY 환경변수 설정의 주석을 제거하고 필요한 경우 라이브러리 경로를 추가합니다.

egmdba 유틸리티를 사용하려면 INFORMIXTERM 환경변수도 설정하는 것이 좋습니다.

그리고 GWDIRECTMODE를 설정해야 dbaccess 같은 유틸리티에서 직접 이기종 데이터베이스에 접속할 수 있습니다.

관련 내용은 아래 링크에서 확인했습니다.

members.iiug.org/forums/ids/index.cgi/read/23480

www.ibm.com/support/pages/error-29080-when-trying-use-substr-function-oracle-database

④ odbc.ini 파일 수정

$INFORMIXDIR/egm/odbc 경로에 odbc.ini 파일이 있습니다. 이 파일에서 SQL Server에 접속 정보를 수정하거나 추가합니다. 수정할 경우 Address와 dbname만 수정하면 됩니다.

[mssql]
Driver=/work1/informix/egm/egm/odbc/lib/IXsqls28.so
Description=DataDirect 8.0 SQL Server Wire Protocol
Address=xxx.xx.xx.xx,1433
AlternateServers=
AnsiNPW=Yes
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=dbname
LoadBalancing=0
LogonID=
Password=
QuotedId=No
SnapshotSerializable=0

3. EGM 프로세스 실행

EGM은 root 계정으로만 실행 가능합니다. root가 아닌 계정으로 실행하면 아래와 같이 오류가 발생합니다.

[informix@db2 odbc]$ egmd egm731ud4 -s egm -l /work1/informix/egm/egmd.log
2020-09-11 11:10:37.916976 daemon err = 25501: oserr = 0: errstr =  : The sqlexecd daemon must be started by root.

4. EGM 유저 및 데이터소스 등록

egmdba 유틸리티를 사용하여 EGM에서 사용할 유저명과 ODBC 데이터소스 이름, 데이터 소스에 접속하기위한 정보를 등록합니다. egmdba는 root 계정으로도 실행할 수 있지만 전체 관리 기능을 사용하려면 informix 계정으로 실행해야 합니다.

5. 접속 테스트

dbaccess를 사용하여 접속 테스트를 해봅니다. 

6. Informix의 데이터베이스에 접속한 상태에서 SQL Server 데이터베이스의 테이블을 조회하려고 할 때는, 아래처럼 오류가 발생하면서 EGM 프로세스가 다운되었습니다. 이 부분은 좀 더 테스트를 해봐야할 것 같습니다.

이기종 데이터베이스에 접근하는 것은 상당히 좋은 기능이지만 EGM은 별도의 상용제품이기 때문에 구매를 해야합니다. 따라서 기업입장에서는 비용을 들여서 구축하기에는 다소 부담이 될 것 같습니다.

시간나는대로 Oracle이나 MySQL도 접속 테스트를 해봐야겠습니다.

14.10.xC4 버전부터 공식적으로 클라이언트에서도 쿼리 플랜을 확인할 수 있는 함수가 제공되는군요. 인포믹스는 과거부터 DB서버에서 set explain 문장을 실행한 후 SQL 문장을 실행하면 생성되는 파일에서 쿼리 플랜을 참조할 수 밖에 없었는데요. 그렇다보니 클라이언트에서는 쿼리 플랜을 볼 방법이 없었습니다.

그래서 11.50버전부터 쿼리 플랜을 시각화하는 explain_sql이라는 루틴이 제공되었는데 IBM Data Studio에서 제한적으로만 쓸 수 있어서 그렇게 많이 사용되지는 않았습니다. 사실 Data Studio는 Db2에 더 최적화 된 느낌이라 인포믹스 DB에 사용하기에는 좀 불편하지요.

그래서 Fernando Nunes씨가 SQL 프로시저를 사용해서 쿼리 플랜을 보는 방법을 제시하기도 했습니다. 사실 이번에 제공되는 루틴과 대동소이합니다. 여러번 루틴을 실행해야하는 번거로움이 있긴 하지만요.

informix-technology.blogspot.com/2012/12/execution-plans-on-client-planos-de.html

어쨌든 많이 늦었지만, 이제라도 쿼리 플랜을 IBM에서 제작된 루틴으로 조회할 수 있다는 점에서 매우 환영할만한 기능입니다. IBM Knowledge Center에서 제공되는 문서의 내용을 따라서 getExplain 함수를 만들어 보았습니다.

> CREATE FUNCTION getExplain(LVARCHAR) RETURNS LVARCHAR(30000) EXTERNAL NAME 'com.informix.judrs.Explain.getExplain(java.lang.String)' LANGUAGE JAVA;
Routine created.
> GRANT EXECUTE ON FUNCTION getExplain(LVARCHAR) TO PUBLIC;
Permission granted.

결과는 LVARCHAR 형식으로 출력되는데, 최대 32KB이니 어지간하면 잘리지 않을 것 같습니다. CLOB 형식으로 해도 괜찮지 않을까 싶습니다.

> execute function getExplain("SELECT * FROM systables a, syscolumns b where a.tabid = b.tabid");
(expression)
              QUERY: (OPTIMIZATION TIMESTAMP: 06-25-2020 22:42:56)
              ------
              SELECT * FROM systables a, syscolumns b where a.tabid = b.tabid
              Estimated Cost: 108
              Estimated # of Rows Returned: 779
                1) informix.a: SEQUENTIAL SCAN
                2) informix.b: INDEX PATH
                  (1) Index Name: informix.column
                      Index Keys: tabid colno
                      Lower Index Filter: informix.a.tabid = informix.b.tabid
              NESTED LOOP JOIN
              ----------
              Procedure: informix.getexplain
              Query statistics:
              -----------------
                Table map :
                ----------------------------
                Internal name     Table name
                ----------------------------
                t1                a
                t2                b
                type     table  rows_prod  est_rows  rows_scan  time       est_cost
                -------------------------------------------------------------------
                scan     t1     1          113       7          00:00.00   14
                type     table  rows_prod  est_rows  rows_scan  time       est_cost
                -------------------------------------------------------------------
                scan     t2     4          786       4          00:00.00   1
                type     rows_prod  est_rows  time       est_cost
                -------------------------------------------------
                nljoin   4          780       00:00.00   109
1 row(s) retrieved.

조금 더 욕심을 부리자면 SQL 문장을 실행하지 않는 AVOID_EXECUTE와 같은 기능을 제공하는 인자도 지정할 수 있다면 좋겠네요.

아래는 getExplain을 실행했을때 online.log에 표시되는 내용입니다.

2020-06-25 22:41:41.70  Booting Language <java> from module <$INFORMIXDIR/extend/krakatoa/lmjava.so>
2020-06-25 22:41:41.70  Loading Module <$INFORMIXDIR/extend/krakatoa/lmjava.so>
2020-06-25 22:41:41.82  cannot extract stack unwind information for /work1/informix/ids1410fc4w1/gls/dll/64-libicudata.so.60
2020-06-25 22:41:41.83  stack dumps out of /work1/informix/ids1410fc4w1/gls/dll/64-libicudata.so.60 may be incomplete
2020-06-25 22:41:41.83  The C Language Module </work1/informix/ids1410fc4w1/extend/krakatoa/lmjava.so> loaded
2020-06-25 22:41:41.83  Loading Module <com.informix.judrs.Explain>
2020-06-25 22:41:41.86  INFO (autoregvp 1) (Execution of [ autoregvp ] dymamically creating VPCLASS jvp)
2020-06-25 22:41:41.957  Dynamically added 1 jvp VP
2020-06-25 22:41:42.859  Got the mutex
2020-06-25 22:41:42.859  LD_LIBRARY_PATH=/work1/informix/ids1410fc4w1/extend/krakatoa/jre/bin/j9vm
2020-06-25 22:41:42.860  VM args[0]= -Xss512k
2020-06-25 22:41:42.860  VM args[1]= -Djava.security.policy=/work1/informix/ids1410fc4w1/tmp/JVM_security
2020-06-25 22:41:42.860  VM args[2]= -Xms16m
2020-06-25 22:41:42.860  VM args[3]= -Xmx16m
2020-06-25 22:41:42.860  VM args[4]= exit
2020-06-25 22:41:42.860  VM args[5]= abort
2020-06-25 22:41:42.860  VM args[6]= -Djava.class.path=/work1/informix/ids1410fc4w1/extend/krakatoa/krakatoa.jar:/work1/informix/ids1410fc4w1/extend/krakatoa
2020-06-25 22:41:43.24  Successfully created Java VM.
2020-06-25 22:41:43.820  Explain file for session 240 : /tmp/informix-explain1554368260018369330.tmp
2020-06-25 22:42:45.158  Explain file for session 240 : /tmp/informix-explain753362435265703881.tmp
2020-06-25 22:42:56.869  Explain file for session 241 : /tmp/informix-explain3777225348977126759.tmp

내용을 살펴보면 java 클래스를 사용한 사용자 함수를 호출한 형태임을 알 수 있습니다. 그리고 set explain 명령을 수행했을 때 처럼 'Explain file for session ...' 이라는 내용이 표시되는데요. 함수가 실행된 직후에는 해당 파일은 삭제되는 것 같습니다.

체감될만한 좋은 기능들이 계속 추가되고 있어서, 앞으로의 변화도 기대되는군요.

online.log 파일을 보다가 바뀐부분을 또 찾았네요.

online.log 내용에서 라인마다 날짜를 표시하는 방식이 몇가지 추가되었습니다.

기존에는 MSG_DATE 파라미터 설정값을 0과 1로만 설정할 수 있었는데요. 14.10.xC4 버전부터는 0,1,2,3 의 값을 설정할 수 있습니다. 아래는 onstat에서 보여주는 MSG_DATE 파라미터의 설명입니다.

$ onstat -g cfg full MSG_DATE
IBM Informix Dynamic Server Version 14.10.FC4W1DE -- On-Line -- Up 23:01:10 -- 2631308 Kbytes
Configuration Parameter Info
id   name                      type    maxlen   units   rsvd  tunable
150  MSG_DATE                  INT4    12                     *
     min/max : 0,3
     default : 0
     onconfig: 3
     current : 3
     Description:
     Use the MSG_DATE configuration parameter to specify the format of the
     timestamp preceding messages in the log. The choices are as follows:
     Value     Format
     0         HH:MM:SS
     1         MM/DD/YYYY HH:MM:SS (Locale-dependent ordering of MM and DD)
     2         Milliseconds since epoch + MM/DD/YYYY HH:MM:SS (Locale-dependent
               ordering of MM and DD)
     3         YYYY-MM-DD HH:MM:SS.FFF

14.10.FC3과 14.10.FC4W1 버전에서 출력되는 내용을 비교해보았습니다.

-- 14.10.xC3 (MSG_DATE=1)
06/24/20 21:45:40  Checkpoint Completed:  duration was 0 seconds.
06/24/20 21:45:40  Wed Jun 24 - loguniq 1101, logpos 0x8234, timestamp: 0x13fca499 Interval: 358
06/24/20 21:45:40  Maximum server connections 5
06/24/20 21:45:40  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 1229, Llog used 10125
-- 14.10.xC4 (MSG_DATE=2, SERVER_LOCALE=ko_kr.ksc)
1593004908957 20/06/24 22:21:48 Checkpoint Completed:  duration was 0 seconds.
1593004908957 20/06/24 22:21:48 Wed Jun 24 - loguniq 20, logpos 0x861018, timestamp: 0x2875df Interval: 345
1593004908957 20/06/24 22:21:48 Maximum server connections 3
1593004908957 20/06/24 22:21:48 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 39, Llog used 5
-- 14.10.xC4 (MSG_DATE=3)
2020-06-24 21:46:42.356  Checkpoint Completed:  duration was 0 seconds.
2020-06-24 21:46:42.356  Wed Jun 24 - loguniq 20, logpos 0x5e64d4, timestamp: 0x27ae32 Interval: 333
2020-06-24 21:46:42.356  Maximum server connections 11
2020-06-24 21:46:42.356  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 63, Llog used 55

 아무래도 연월일 순서로 나오는게 보기가 편하겠죠? 근데 밀리세컨드까진 필요없을 것도 같고..