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

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

6월 23일자로 인포믹스 14.10 버전의 새로운 Fix Pack 14.10.xC4W1이 공개되었습니다.

아직 문서에 새로운 기능 소개는 나오지 않아서 onstat 유틸리티에 새로운 옵션이 생겼나 찾아보니 하나가 눈에 띄네요.

onstat -g top이라는 옵션이 새로 생겼습니다. 리눅스의 top과 유사한 기능을 제공하는 옵션인데요. 기본값으로 5초간의 성능수치를 비교하여 시스템 자원을 많이 사용하는 세션이나 스레드를 확인할 수 있습니다. 시간 간격이나 표시할 라인 수, 반복 횟수를 지정할 수 있습니다.

아래는 onstat 명령을 실행했을 때의 top 옵션 설명입니다.

        top [ <entity> <stat> [ <max lines> [ <intvl> [ <reps> ]]]]
            Print top consumers of various resources over specified interval
            Valid <entity> <stat> combinations:
                thread    cpu   (CPU usage)
                thread    drd   (disk reads)
                thread    bfr   (buffer reads)
                thread    bfw   (buffer writes)
                thread    plg   (physical log usage)
                thread    llg   (logical log usage)
                session   cpu   (CPU usage)
                session   drd   (disk reads)
                session   bfr   (buffer reads)
                session   bfw   (buffer writes)
                session   plg   (physical log usage)
                session   llg   (logical log usage)
                chunk     ios   (page reads/writes)
                chunk     art   (average read times)
                chunk     awt   (average write times)
                space     ios   (page reads/writes)
                space     art   (average read times)
                space     awt   (average write times)
                mempool   gro   (memory growth)
                sessmem   gro   (memory growth)
                partition drd   (disk reads)
                table     drd   (disk reads)

위에 나열된 항목을 전부 또는 지정해서 볼 수 있습니다.

watch 명령을 이용해서 인포믹스를 모니터링 하는 것도 괜찮아 보입니다.

아래는 제가 약간의 트랜잭션을 발생시키고 나서 top 옵션으로 본 화면입니다.

[informix@db2 skjeong]$ onstat -g top 10 3
IBM Informix Dynamic Server Version 14.10.FC4W1DE -- On-Line -- Up 18:46:36 -- 2631308 Kbytes
Top Resource Usage (Max lines 10, Time interval 3 seconds):
Top Threads (CPU usage)
 tid    name              sid        CPU_time   #scheds  status
 343    sqlexec           208          0.0014         6  sleeping secs: 1
 344    sqlexec           209          0.0009         6  sleeping secs: 1
 345    sqlexec           210          0.0009         6  sleeping secs: 1
 19     periodic          14           0.0001         5  sleeping secs: 1
Top pools (memory growth)
 name                           increase(b)      total_size(b)
 rsam                           8                3081712
(No partition disk reads to display)
(No DBspace activity to display)
(No physlog activity to display)
Top threads (logical log usage)
 tid      rstcb              llog_bytes   name
 344      0x459af1a8         552          sqlexec
 343      0x459afa88         552          sqlexec
 345      0x459b0c48         552          sqlexec

기존에 스크립트로 모니터링 하시는 분들도 계시겠지만, onstat 명령으로 본다면 좀 더 시스템에 부담은 덜 줄 것 같군요. 실시간 모니터링에 많은 도움이 될 것 같습니다.

다른 유용한 기능이 더 생겼는지 찾아봐야겠네요.

안녕하세요. 요즘 IBM 커뮤니티에서 열심히 댓글놀이를 하고 있는데요. 며칠전에 Informix에서 문자열에 대한 최빈값(MODE)을 구하는 방법에 대한 질문글이 올라왔습니다. 최빈값은 통계학에서 쓰는 용어로 액셀이나 분석 솔루션에서 MODE라는 함수로 사용되기도 합니다. 아래는 위키백과의 최빈값에 대한 설명입니다.

그런데 최빈값이라 함은 숫자가 대상이지 문자열은 아닙니다. 그리고 SQL에서는 MODE라는 함수가 따로 제공되는 것도 아니어서 문자열을 대상으로 하려면 별도의 로직을 구현해야합니다.

질문에서 제시하는 샘플데이터와 원하는 결과세트는 아래와 같습니다.

<샘플데이터>

<결과세트>

결과세트에서 요구하는 사항은 다음과 같습니다.

1) 이름별로 가장 연도와 월 기준으로 가장 많은 값을 찾는다.

2) 일자는 해당 월의 아무 일자나 허용한다.

3) 1에서 찾은 값의 개수가 같은 것이 있으면 연도, 월, 일자를 같은 값을 우선한다.

그래서 이리저리 검색을 해서 아래와 같은 쿼리를 만들었습니다.

WITH t1 AS
(
SELECT 'JESSE' Name, '1992/10/27' dob, 10 score 
UNION ALL SELECT 'JESSE', '1992/10/27', 20 
UNION ALL SELECT 'JESSE', '1992/11/06', 30 
UNION ALL SELECT 'JESSE', '1992/11/11', 40 
UNION ALL SELECT 'JESSICA', '1992/03/11', 50 
UNION ALL SELECT 'JESSICA', '1992/11/03', 60 
UNION ALL SELECT 'JESSICA', '1992/10/29', 70 
UNION ALL SELECT 'JESSICA', '1992/11/10', 80 
UNION ALL SELECT 'JESSICA', '1992/11/30', 90 
UNION ALL SELECT 'JESSICA', '1992/11/12', 10 
UNION ALL SELECT 'JESSICA', '1992/12/07', 20  
UNION ALL SELECT 'JESSICA', '1992/12/09', 30 
)
SELECT a.Name,
       a.dob,
       a.avg	  
  FROM (SELECT t1.Name, 
               t1.dob, 
               ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rn, 
               AVG(t1.score) OVER (PARTITION BY t1.Name) avg
          FROM t1,
               (SELECT t1.Name, 
                       t1.dob[1,7], 
                       RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk
                  FROM t1
                 GROUP BY t1.Name, t1.dob[1,7]
               ) t2
         WHERE t2.rk = 1
           AND t1.Name = t2.Name
           AND t1.dob like t2.dob||'%'
         GROUP BY t1.Name, t1.dob, t1.score
       ) a
 WHERE rn =1

쿼리의 흐름은 다음과 같습니다.

1) 이름을 기준으로 연도,월별 개수가 가장 많은 것을 구합니다. 가장 많은 개수가 같을 경우를 고려해 RANK 함수를 사용합니다. 그러면 같은 랭킹인 경우는 모두 첫번째가 됩니다.

2) 1에서 구한 데이터를 기준으로 일자를 포함하여 개수가 가장 많은 것을 구합니다. 여기선 월별로 일자 하나만 선택해야하므로 ROW_NUMBER를 사용합니다.

아래는 실행한 결과입니다.

[informix@db2 skjeong]$ dbaccess stores_demo mode.sql
Database selected.
name    dob                     avg
JESSE   1992/10/27 25.0000000000000
JESSICA 1992/11/30 60.0000000000000
2 row(s) retrieved.

인포믹스에서의 예제이긴 하지만 대부분의 RDBMS에서 지원하는 함수이기 때문에 필요한 경우 적용하기는 어렵지 않으실겁니다. 혹시 이 글을 읽는 분들께서 더 나은 방법을 알고 계시다면 조언 부탁드리겠습니다.

제가 참고한 사이트는 아래와 같습니다.

https://www.mssqltips.com/sqlservertip/3543/calculate-the-statistical-mode-in-sql-server-using-tsql/

데이터베이스 사랑넷에 자문을 구해 쿼리를 수정해보고, 새로운 쿼리도 알게 됐습니다.

위의 요구사항보다 조건을 좀 더 구체적으로 설정했습니다.

1) 평균은 월평균이 되어야한다. 이전 것은 이름만 기준으로한 평균

2) 가장 많은 갯수가 동일할 경우의 우선순위는? (빠른 날짜, 느린 날짜, 낮은 평균, 높은 평균 등)

여기서는 빠른 날짜를 기준으로 합니다.

아래는 수정된 쿼리입니다. 제가 수정한 쿼리는 마농님의 조언에 따라 약간 수정한 것입니다.

테스트를 위해 WITH절에  샘플 레코드를 추가했습니다.

--- 제가 수정한 쿼리
WITH t1 AS
(
SELECT 'JESSE' Name , '1992/08/06' dob, 30 score
UNION ALL SELECT 'JESSE', '1992/08/06', 40 
UNION ALL SELECT 'JESSE', '1992/07/27', 10
UNION ALL SELECT 'JESSE', '1992/07/27', 20 
UNION ALL SELECT 'JESSE', '1992/11/06', 30 
UNION ALL SELECT 'JESSE', '1992/11/11', 40 
UNION ALL SELECT 'JESSICA', '1992/03/11', 50 
UNION ALL SELECT 'JESSICA', '1992/11/03', 60 
UNION ALL SELECT 'JESSICA', '1992/10/29', 70 
UNION ALL SELECT 'JESSICA', '1992/11/10', 80 
UNION ALL SELECT 'JESSICA', '1992/11/30', 90 
UNION ALL SELECT 'JESSICA', '1992/11/12', 10 
UNION ALL SELECT 'JESSICA', '1992/12/07', 20  
UNION ALL SELECT 'JESSICA', '1992/12/09', 30 
)
SELECT a.Name,
       a.dob,
       a.avg	  
  FROM (SELECT t1.Name, 
               t1.dob, 
               ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC, t1.dob ASC) rn, 
               AVG(t1.score) OVER (PARTITION BY t1.Name, t1.dob[1,7] ) avg
          FROM t1,
               (SELECT t1.Name, 
                       t1.dob[1,7], 
                       RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk
                  FROM t1
                 GROUP BY t1.Name, t1.dob[1,7]
               ) t2
         WHERE t2.rk = 1
           AND t1.Name = t2.Name
           AND t1.dob like t2.dob||'%'
         GROUP BY t1.Name, t1.dob, t1.score
       ) a
 WHERE rn =1
--- 마농님의 쿼리 (WITH문 생략)
SELECT *
  FROM (SELECT name
             , dob
             , avg_m
             , ROW_NUMBER() OVER(
               PARTITION BY name ORDER BY cnt_m DESC, cnt_d DESC, dob) rn
          FROM (SELECT name
                     , dob
                     , COUNT(*)   OVER(PARTITION BY name, dob[1,7]) cnt_m
                     , COUNT(*)   OVER(PARTITION BY name, dob     ) cnt_d
                     , AVG(score) OVER(PARTITION BY name, dob[1,7]) avg_m
                  FROM t1
                ) a
        ) a
 WHERE rn = 1
;

제가 올린 질문글은 아래 링크에서 참고해주세요.

http://database.sarang.net/?inc=read&aid=3484&criteria=informix&subcrit=qna&id=&limit=20&keyword=&page=1

안녕하세요. 인포믹스 14.10.xC2 버전부터 파일시스템의 Chunk를 빠르게 생성하는 기능이 추가되었습니다.

기존에는 파일시스템에서 Chunk를 생성하면 크기가 클수록 오래걸렸는데요.

이 기능을 사용하면 onspaces 명령을 실행하는 즉시 Chunk가 생성됩니다.

Configuration 설명을 보면 다음과 같이 나와있습니다.

Configuration Parameter Info
id   name                      type    maxlen   units   rsvd  tunable
108  USE_FALLOCATE             BOOL    2                      *
     default : 1
     onconfig: 1
     current : 1
     This parameter is undocumented.
     Description:
     Enabling USE_FALLOCATE allows the server to allocate space for a new
     cooked chunk much more quickly by making use of posix_fallocate(). This
     is only supported on platforms which have posix_fallocate() available.
     A performance improvement can only be expected when the underlying file
     system supports the required functionality. Please refer to your operating
     system vendor documentation for further details.

posix_fallocate 함수를 살펴보면 리눅스 커널에서만 지원되는 함수인 것 같습니다. 혹시 잘못된 내용이라면 알려주세요.

xfs , ext4 , btrfs, tmpfs 등의 파일시스템에서 이 동작을 지원합니다.

아래는 실제로 CentOS 7.6의 Informix 14.10.FC3 환경에서 테스트해본 내용입니다.

onspaces 명령을 실행하자마자 청크가 생성된 것을 확인할 수 있습니다.

[informix@db2 ids1410fc3]$ ls -la /work1/informix/storage/test
-rw-rw----. 1 informix informix 0 Mar 10 09:09 /work1/informix/storage/test
[informix@db2 ids1410fc3]$ time onspaces -c -d test -p /work1/informix/storage/test -o 0 -s 6000000
Verifying physical disk space, please wait ...
Space successfully added.
** WARNING **  A level 0 archive of Root DBSpace will need to be done.
real    0m0.094s
user    0m0.011s
sys     0m0.025s
[informix@db2 ids1410fc3]$ ls -la /work1/informix/storage/test
-rw-rw----. 1 informix informix 6144000000 Mar 10 09:18 /work1/informix/storage/test

해당 기능에 대해서는 아직 IBM Knowledge Center에는 나와있지 않네요.

아 그리고 참고로 스토리지 암호화 기능(DISK_ENCRYPTION)이 활성화 된 상태에서는 이 옵션이 적용되지 않는 것 같습니다.

index page 최대 개수에 대해 조사하다보니 extent 할당에 대해서도 우연히 문서를 읽게 되었습니다.

IBM Knowledge Center에 따르면 11.70.xC1 버전부터 partition header pages의 공간이 부족해지면 secondary header pages가 자동으로 할당된다고 합니다.

https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/com.ibm.perf.doc/ids_prf_309.htm

If you have a table that needs more extents and the database server runs out of space on the
partition header page, the database server automatically allocates extended secondary partition
header pages to accommodate new extent entries.
The database server can allocate up to 32767 extents for any partition, unless the size of a table
dictates a limit to the number of extents.

따라서 11.70 버전부터 extent가 최대 32,767개 까지 할당됩니다. 관리할 점은 줄었네요.

그렇지만 성능을 고려한다면 개수를 적게 유지하는 것이 좋겠죠.

아래와 같이 데이터를 대량으로 입력해서 extent 개수를 많이 늘려보았습니다.

일반적으로는 EXTENT DOUBLING 규칙에 의해 extent 크기가 2배씩 커지지만 작은 페이지 크기로 일정하게 할당되도록 NO_EXTENT_DOUBLING 옵션을 활성화 했습니다.

참고로 NO_EXTENT_DOUBLING 옵션은 11.70.xC9부터 사용가능합니다.

drop table "informix".log_01;
create table "informix".log_01
  (
    col1 varchar(50),
    col2 varchar(32),
    col3 varchar(5),
    col4 varchar(5),
    col5 varchar(10)
  ) in dbs1 extent size 16 next size 16 lock mode page;
  ...

아래의 SQL문장으로 1천만건의 레코드를 입력합니다.

$ nohup dbaccess -e demo /informix/skjeong/insert1.sql > /informix/skjeong/insert1.log 2>&1 &
$ cat insert1.sql
insert into log_01
select 'dolore eu fugiat nulla pariatur. Excepteur sint oc',
       '180e70d7-8684-4674-8d42-3c1e5f3b',
       'labor',
       'eiusm',
       level
from sysmaster:sysdual
connect by level <= 10000000;

데이터 입력중에 oncheck 명령으로 테이블의 extent 수를 확인해보았습니다.

와우! 2356개입니다. EXTENT DOUBLING이 되고 한계가 3만2천개라면 거의 제한이 없는 것으로 봐도 되겠지요.

TBLspace Report for demo:informix.log_01
    Physical Address               2:5
    Creation date                  02/17/2020 13:23:41
    TBLspace Flags                 901        Page Locking
                                              TBLspace contains VARCHARS
                                              TBLspace use 4 bit bit-maps
    Maximum row size               107
    Number of special columns      5
    Number of keys                 0
    Number of extents              2356
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              8
    Next extent size               128
    Number of pages allocated      301056
    Number of pages used           301056
    Number of data pages           300981
    Number of rows                 5422921
    Partition partnum              2097154
    Partition lockid               2097154
    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0         6:5000427           8          8
                    8         6:5000483           8          8
                   16         6:5000611          16         16
                   32         6:5000851          32         32
                   64         6:5001395          64         64
                  128         6:5002675         128        128
                  256         6:5004083         128        128
                  384         6:5005363         128        128
                  512         6:5006515         128        128
                  ...

안녕하세요. IBM Community에서 Informix의 rowid를 참조하는 내용이 있어 공유하고자 합니다.

원래 글의 내용은 Informix page 할당 개수의 한계에 관련된 질문이었는데 파티션된 테이블의 rowid에 대한 내용이 중간에 언급되었습니다.

인포믹스의 파티션된 테이블은 rowid 컬럼이 존재하지 않는데, 파티션 테이블에 rowid를 표시되도록 하려면 아래의 명령으로 숨겨진 ROWID 컬럼을 추가할 수 있습니다.

ALTER TABLE frag1 ADD ROWIDS;

그런데 ifx_row_id라는 숨겨진 컬럼이 있다고 합니다. 이리저리 자료들을 찾아보니 11.50 버전부터 지원되었다고 하는데요. IBM에서 공식적으로 문서화되지 않았습니다.

기존 테이블에 rowid 컬럼을 추가하지 않고도 rowid를 대체하여 사용할 수 있습니다.

보여지는 형식은 <partnum>:<rowid> 입니다.

특징으로는 아래와 같이 SEQUENTIAL SCAN이 발생한다는 점입니다.

QUERY: (OPTIMIZATION TIMESTAMP: 01-02-2020 17:27:54)
------
select ifx_row_id from systables where ifx_row_id='1048900:1301'
Estimated Cost: 7
Estimated # of Rows Returned: 1
  1) informix.systables: SEQUENTIAL SCAN
        Filters: informix.systables.ROWID = '1048900:1301'

반면에 ROWID 컬럼은 INDEX SCAN을 합니다.

QUERY: (OPTIMIZATION TIMESTAMP: 01-02-2020 17:51:25)
------
select * from systables where rowid=2058
Estimated Cost: 1
Estimated # of Rows Returned: 1
  1) informix.systables: INDEX PATH
    (1) Index Name: (ROWID)
        Index Keys: ROWID
        Lower Index Filter: informix.systables.ROWID = 2058

ifx_row_id 컬럼은 일반적인 사용자 업무에 사용되기보다는 마이그레이션등의 작업에 이용할 수 있는 옵션이 될 수 있을 것 같습니다.

아래 APAR를 보면 WITH ROWIDS 옵션을 쓰기보다 일반 컬럼을 primary key로 사용할 것을 권고하고 있습니다.

IC94738: IFX_ROW_ID UNDOCUMENTED FEATURE IS DOING SEQUENTIAL SCAN INSTEAD OF READING DIRECTLY IN THE APPROPRIATE PAGE

Using the WITH ROWIDS Option
Nonfragmented tables contain a hidden column called rowid, but
by default, fragmented tables have no rowid column. You can use
the WITH ROWIDS keywords to add the rowid column to a fragmented
table. Each row is automatically assigned a unique rowid value
that remains stable for the life of the row and that the
database server can use to find the physical location of the
row. Each row requires an additional four bytes to store the
rowid.
Important: This is a deprecated feature. Use primary keys as an
access method rather than the rowid column.

https://www-01.ibm.com/support/docview.wss?uid=swg1IC94738

안녕하세요. 14.10.xC1 과 14.10.xC2 버전의 defect를 알려드립니다. 복구가 안되는 심각한 문제입니다.

Restore with on-Bar using the Veritas™ NetBackup™ storage manager is not possible using Informix database server versions 14.10.xC1 and 14.10xC2.

BSAGetObject: input: bufferLen = 63488, numBytes = 0
...
BSAGetObject: output: bufferLen = 63488, numBytes = 0
BSAGetObject: return 0 (0x00)
...
barGetObjectMain: return 0 (0x00)
barGetObject: BUG!!, numbtes(0) < headerlen(4096).
barGetObject: return 159 (0x9f)

조만간 임시 수정버전이 공개될 것 같습니다. 참고되시길~

https://www.ibm.com/support/pages/node/1074446?myns=swgimgmt&mynp=OCSSGU8G&mync=E&cm_sp=swgimgmt-_-OCSSGU8G-_-E

안녕하세요. 인포믹스 12.10.FC12, 12.10.FC12W1, 14.10.FC1 버전에서 발생하는 문제를 소개드립니다.

문제가 발생한 이후부터는 인포믹스 프로세스가 시작되지 않으므로 매우 치명적인 결함입니다.

문제가 발생할 수 있는 조건은 아래와 같습니다.

1. 해당 테이블에 VARCHAR 컬럼을 추가한다.

2. 추가한 VARCHAR 컬럼의 사이즈를 축소한다.

위와 같은 상황에서 UPDATE 문장을 실행하면 오류가 발생하면서 인포믹스 프로세스가 다운됩니다.

그러나 항상 다운되는 것은 아니고 대상 테이블에 저장된 데이터 길이에 따라 다운될 수도, 다운되지 않을 수도 있습니다.

아래는 인포믹스 12.10.FC12와 14.10.FC1 버전의 환경에서 다운되는 현상을 재현했을 때의 로그 메시지입니다.

## 12.10.FC12W1

11:20:01  Assert Failed: Buffer modified in inconsistent chunk.

11:20:01  IBM Informix Dynamic Server Version 12.10.FC12W1WE

11:20:01   Who: Session(87, informix@pilma01, 39452766, 700000020593858)

                Thread(222, sqlexec, 700000020553068, 10)

                File: rsdebug.c Line: 1047

11:20:01   Results: Chunk 13 is being taken OFFLINE.

11:20:01   Action: Restore space containing this chunk from the archive.

11:20:01  stack trace for pid 36175890 written to /work2/ifx1210fc12w1we/tmp/af.4c6a350

11:20:02   See Also: /work2/ifx1210fc12w1we/tmp/af.4c6a350, shmem.4c6a350.0

11:20:10  Buffer modified in inconsistent chunk.

11:20:11  Assert Failed: INFORMIX-OnLine Must ABORT

        Critical media failure.

11:20:11  IBM Informix Dynamic Server Version 12.10.FC12W1WE

11:20:11   Who: Session(87, informix@pilma01, 39452766, 700000020593858)

                Thread(222, sqlexec, 700000020553068, 10)

                File: rsmirror.c Line: 2080

11:20:11  stack trace for pid 36175890 written to /work2/ifx1210fc12w1we/tmp/af.4c6a350

11:20:12   See Also: /work2/ifx1210fc12w1we/tmp/af.4c6a350

11:20:19  Thread ID 222 will NOT be suspended because

          it is in a critical section.

11:20:19   See Also: /work2/ifx1210fc12w1we/tmp/af.4c6a350

11:20:19  rsmirror.c, line 2080, thread 222, proc id 36175890, INFORMIX-OnLine Must ABORT

        Critical media failure..

11:20:19  Fatal error in ADM VP at mt_fn.c:14593

11:20:19  Unexpected virtual processor termination: pid = 36175890, exit status = 0x1.

11:20:19  PANIC: Attempting to bring system down

## 14.10.FC1DE

12:46:54  Assert Failed: Buffer modified in inconsistent chunk.

12:46:54  IBM Informix Dynamic Server Version 14.10.FC1DE

12:46:54   Who: Session(46, informix@ifxdb1, 62804, 0x4526fbc8)

                Thread(55, sqlexec, 4522d8c8, 1)

                File: rsdebug.c Line: 908

12:46:54   Results: Chunk 1 is being taken OFFLINE.

12:46:54   Action: Restore space containing this chunk from the archive.

12:46:54  stack trace for pid 62571 written to /opt/IBM/Informix_Software_Bundle/tmp/af.41fb7ad

12:46:54   See Also: /opt/IBM/Informix_Software_Bundle/tmp/af.41fb7ad, shmem.41fb7ad.0

12:46:57  Buffer modified in inconsistent chunk.

12:46:58  Assert Failed: INFORMIX-OnLine Must ABORT

        Critical media failure.

12:46:58  IBM Informix Dynamic Server Version 14.10.FC1DE

12:46:58   Who: Session(46, informix@ifxdb1, 62804, 0x4526fbc8)

                Thread(55, sqlexec, 4522d8c8, 1)

                File: rsmirror.c Line: 2062

12:46:58  stack trace for pid 62571 written to /opt/IBM/Informix_Software_Bundle/tmp/af.41fb7ad

12:46:58   See Also: /opt/IBM/Informix_Software_Bundle/tmp/af.41fb7ad

12:47:02  Thread ID 55 will NOT be suspended because

          it is in a critical section.

12:47:02   See Also: /opt/IBM/Informix_Software_Bundle/tmp/af.41fb7ad

12:47:02  Starting crash time check of:

12:47:02  1. memory block headers

12:47:02  2. stacks

12:47:02  Crash time checking found no problems

12:47:02  rsmirror.c, line 2062, thread 55, proc id 62571, INFORMIX-OnLine Must ABORT

        Critical media failure..

12:47:02  The Master Daemon Died

12:47:02  PANIC: Attempting to bring system down

관련 내용으로 IBM에 기술지원을 요청했었습니다.

해당 문제는 해외에서 발생한 사례가 이미 있었다고 하고 재현 시나리오도 구체적이진 않지만 존재했습니다.

관련 문제를 서술한 문서의 링크는 아래입니다.

http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1IT27997

장애 재현시의 메시지 로그에서 보이는 assert failure file 내용을 살펴보니 해당 사용자 스레드의 stack trace를 확인할 수 있었습니다.

0x00000001000af9cc (oninit)afstack

0x00000001000aeb5c (oninit)afhandler

0x00000001000af038 (oninit)affail_interface

0x00000001001b8844 (oninit)buffcheck

0x00000001002371a0 (oninit)buffput

0x0000000100b88640 (oninit)ckpgversion

0x0000000100b87af4 (oninit)rewrecord

0x0000000100b870ec (oninit)rsrewrec

0x000000010071ab00 (oninit)fmrewrec

0x00000001008382a0 (oninit)aud_sqisrewrec

0x0000000100d40a90 (oninit)doupdate

0x0000000100d3ff2c (oninit)chkrowcons

0x000000010114ea04 (oninit)dodmlrow

0x0000000101150eac (oninit)dodelupd

0x000000010083ee30 (oninit)aud_dodelupd

0x0000000100d1ec24 (oninit)excommand

0x00000001008c8590 (oninit)sq_execute

0x00000001008103ac (oninit)sqmain

0x00000001014d6898 (oninit)listen_verify

0x00000001014d530c (oninit)spawn_thread

0x0000000101482ae0 (oninit)th_init_initgls

0x00000001018f86e0 (oninit)startup

defect IT27997에 대해 서술한 내용과 상당히 유사한 stack trace 임을 알 수 있었습니다.

stack trace 중에서 ckpgversion function 부분이 문제인 듯 합니다.

그리고 12.10.FC12 버전부터 In-place alter 기능이 개선되었다고 알려져있는데, 이 개선으로 인해 defect가 발생한 것이 아닌가 생각도 듭니다. 물론 제 추측이기 때문에 정확한 원인으로 보기는 어렵습니다.

IIUG2018의 발표자료중 Jeff McMahon and Nick Geib가 발표한 What’s New in Informix 장표를 살표보면 VARCHAR 에서 VARCHAR 간 (smaller or larger) 사이즈를 변경하는 유형도 In-place alter 방식으로 이루어진다고 나와 있습니다.

아래는 위의 IT27997의 내용을 토대로 작성한 장애재현 스크립트 내용입니다.

테스트용 데이터는 60자 고정길이 필드 한개로 만드시면 재현이 쉽습니다.

drop table test;

create table test (a varchar(60));

load from test.unl insert into test;

alter table test add b int;

alter table test add c varchar(5);

alter table test add d varchar(5);

alter table test add e varchar(5);

alter table test modify c varchar(1);

alter table test modify d varchar(1);

alter table test modify e varchar(1);

update test set a=' qui officia deserunt mollit anim id est laborum.Lorem ip';

해당 문제는 12.10.FC12 및 12.10.FC12W1 에서 발생하는 문제이므로 해당버전을 사용중이라면 IBM에 패치버전을 요청하시거나 12.10.FC11 이하 버전으로 다운그레이드하면 문제가 발생하지 않습니다.

참고가 되시길 바랍니다.

안녕하세요. 2019년 2월자 IIUG insider에 따르면 인포믹스 14 버전이 2019년 상반기에 출시될 예정이라고 합니다.

작년에 IIUG World 컨퍼런스에서도 소개가 된 내용인데 버전 숫자까지 공개된 것은 처음이네요..

라고 생각했지만 웹에서 검색해보니 이미 작년부터 14.10 버전이야기가 나오고 있었네요.

서양에서는 13이라는 숫자를 확실히 싫어하는 걸까요?

곧 IBM에서 EAP(Early Access Program)도 시작하지 않을까 생각했는데,

이미 CURSOR 등의 회사나 고객사에서 베타테스트를 진행하고 있었던 모양입니다.

https://www.cursor-distribution.de/en/community/community-informix

아래 링크에서 인포믹스 14.10 버전의 새로운 기능을 확인하실 수 있습니다.

http://www.iiug.org/en/2019/02/04/iiug-insider-issue-222-january-2019/

Enhances log replay performance of remote secondary servers and OLTP transactions

• • • Up to 5x improvement in replication enables client applications to sustain near zero latency between primary and secondary servers allowing faster recovery time objective in disaster scenarios.
    • Up to 10% faster than 12.10 for standard OLTP transactions

Provides higher security for encryption keys and integrated backup encryption

• • • By supporting remote storage of encryption at rest keys in Amazon Key Manager, an additional layer of security is applied to Informix server encrypted data. Three ciphers AES128, AES192 and AES256 are supported.
    • By supporting remote key management server to generate encryption keys and reducing DBA effort to encrypt Informix data backups, data security in backup media is enhanced without the risk of losing keys. The encryption key is itself encrypted (called Envelop Encryption).
    • Transport Layer Security (TLS) to 1.2 for a higher level of network transport security.

Enhances usability, streamlines administration, and increases uptime

• • • By supporting additional in-place alter operations on tables and data types, database downtime is avoided

Provides a new centralized and graphical administration tool called InformixHQ

• • • InformixHQ is a modern web console for visualizing, monitoring, and managing your Informix server instances. It is purpose built for ease-of-use, scale-out, and optimizing DevOps needs.
    • It provides critical performance management capabilities, monitoring how key performance metrics are changing overtime and tracking how efficiently Informix is running your workload even when you’ve stepped away from your screen. Its monitoring system feeds directly into a customizable alerting system so you can be immediately alerted via email, Twilio, or PagerDuty whenever an issue occurs on one of your Informix database server instances.
    • It is designed to be scalable to efficiently manage and monitor as many Informix database server instances as you run.
    • It enables collaboration between the DBAs, the app developers, the ops engineers, and management and accessed from any desktop, laptop, or mobile device.
    • InformixHQ is the path forward for graphical monitoring, alerting, and administration of your Informix database servers.

Enhances Unicode support to current V11.0

• • • Ability to support storage and processing for text characters that are new over the last 10 years. The new scripts and characters in Version 11.0 add support for lesser-used languages and unique written requirements worldwide.

Enhances time series granularity and spatial projection

• • • Supporting Sub-second timestamps allow for very high speed event generation
    • Support for more geodetic and projections systems enables you to track packages in a shipping depot using your own coordinate system

Increased hardware limits at the Workgroup Edition level

• • • New limits 24 cores / 32GB RAM enable higher performance and scalability for your critical applications providing even higher return on your Informix investment.

Includes storage optimization at the Enterprise Edition level

• • • All Informix Enterprise edition deployments now benefit from the well proven data compression technology already built in Informix.
    • Data compression reduces primary, secondary, backups and log storage while simultaneously enhancing performance by reducing I/O operations.
    • Customers have reported average 4x reductions in database size while experiencing faster I/O operations and faster backups.

Common table ｅxpression

• • • By implementing the SQL standard CTE, application developers improve readability and maintenance of complex queries, able to re-use CTE result-set multiple times in one query, Pipeline aggregations and write powerful recursive queries.
    • Complex queries can be divided into simple, ad hoc, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

Setting up Informix instance/or database level replication using Enterprise Replication

• • • Single command that automates setting up of Enterprise Replication and data migration between two Informix server instances located either on-prem or in the cloud. This command automates tasks such as defining Enterprise Replication domain between the two servers, adding key columns for the tables, creating required storage spaces for the databases, copying database schema from source server to target serve, creating replicate definitions and finally synchronizing data between source and target server instances, all in a transactional, flexibly phased manner, with no downtime.

** 출처

http://www.iiug.org/en/2019/02/04/iiug-insider-issue-222-january-2019/

http://www.hamburg-data.de/en/news/136-soon-informix-14-10

https://www.cursor-distribution.de/en/community/community-informix