728x90

안녕하세요. IBM 커뮤니티에서 인포믹스의 통합 백업 암호화(Integrated Backup Encryption) 기능에 대한 질문이 있어서 소개하려고 합니다.

질문의 요지는 암호화된 백업 파일의 압축이 잘 되는가 하는 것인데, PGP로 암호화된 파일은 압축이 잘 안되었던 모양입니다. 그래서 몇가지 상황을 가정하고 백업 암호화 기능의 작동 방식을 테스트 해보았습니다.

 

인포믹스의 통합 백업 암호화 기능은 14.10 버전부터 제공됩니다.

매뉴얼에서는 통합 백업 암호화 기능을 사용한다면 원격지의 키서버를 통해 백업 암호화 키를 생성할 것을 권장하고 있으나 여기서는 편의상 로컬 암호화 키를 사용하겠습니다. 로컬 암호화 키를 생성하는 방법은 IBM Knowledge Center의 아래 링크를 참고했습니다.

https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.bar.doc/ids_bar_local_encryption_key.htm

-- 1. 통합 백업 암호화 기능을 설정하지 않고 ontape 백업 수행
[informix@db2 backups]$ onstat -c | egrep '^BACKUP_FILTER|^RESTORE_FILTER|^BAR_ENC'
BACKUP_FILTER
RESTORE_FILTER
BAR_ENCRYPTION
[informix@db2 backups]$ ontape -s -L 0
10 percent done.
20 percent done.
30 percent done.
40 percent done.
100 percent done.
File created: /work1/informix/ids1410fc3/backups/db2_0_L0
Please label this tape as number 1 in the arc tape sequence.
This tape contains the following logical logs:
 247
Program over.
[informix@db2 backups]$ ls -la
total 266788
drwxrwxr-x.  3 informix informix        33 Mar 31 09:54 .
drwxr-xr-x. 28 informix informix      4096 Mar 21 22:30 ..
-rw-rw----.  1 informix informix 273186816 Mar 31 09:54 db2_0_L0
drwxrwxr-x.  2 informix informix       182 Mar 31 09:51 tmp
-- 2. 통합 백업 암호화 기능을 설정하고 ontape 백업 수행
[informix@db2 backups]$ openssl rand -base64 24 > /work1/informix/ids1410fc3/etc/l_key192
[informix@db2 backups]$ onstat -c | egrep '^BACKUP_FILTER|^RESTORE_FILTER|^BAR_ENC'
BACKUP_FILTER
RESTORE_FILTER
BAR_ENCRYPTION keyfile=/work1/informix/ids1410fc3/etc/l_key192,cipher=aes192
[informix@db2 backups]$ ontape -s -L 0
The backup volume will be encrypted.
10 percent done.
20 percent done.
30 percent done.
40 percent done.
100 percent done.
File created: /work1/informix/ids1410fc3/backups/db2_0_L0
Please label this tape as number 1 in the arc tape sequence.
This tape contains the following logical logs:
 247
Program over.
[informix@db2 backups]$ ls -la
total 533572
drwxrwxr-x.  3 informix informix        65 Mar 31 09:55 .
drwxr-xr-x. 28 informix informix      4096 Mar 21 22:30 ..
-rw-rw----.  1 informix informix 273186816 Mar 31 09:54 db2_0_20200331_095432_L0
-rw-rw----.  1 informix informix 273186816 Mar 31 09:55 db2_0_L0
drwxrwxr-x.  2 informix informix       182 Mar 31 09:51 tmp
-- 3. 백업 파일 내용 확인
[informix@db2 backups]$ strings db2_0_20200331_095432_L0 | head -30
Archive Backup TapeIBM Informix Dynamic Server Version 14.10.FC3DETue Mar 31 09:54:32 2020informix/dev/pts/0
/work1/informix/ids1410fc3/backups/
rootdbs                                                                                                                         plog                                                                                                                            llog                                                                                                                            datadbs1                                                                                                                        datadbs2                                                                                                                        datadbs3                                                                                                                        data8dbs1                                                                                                                       data8dbs2                                                                                                                       data8dbs3                                                                                                                       sbspace1
IBM Informix Dynamic Server Copyright 2001, 2019  IBM Corporation
14.10.xC2
        F"01-2020
ROOTNAME rootdbs
ROOTPATH /work1/informix/ids1410fc3/storage/rootdbs
ROOTOFFSET 0
ROOTSIZE 157696
MIRROR 0
MIRRORPATH /work1/informix/ids1410fc3/tmp/demo_on.root_mirror
MIRROROFFSET 0
DBSERVERNAME ol_informix1410
SERVERNUM 0
MSGPATH /work1/informix/ids1410fc3/ol_informix1410.log
TAPEDEV /work1/informix/ids1410fc3/backups
TAPESIZE 0
TAPEBLK 32
LTAPEDEV /dev/null
LTAPESIZE 0
LTAPEBLK 32
PHYSFILE 65430
PHYSBUFF 512
LOGFILES 20
LOGSIZE 6144
LOGBUFF 256
DYNAMIC_LOGS 2
LTXHWM 70
LTXEHWM 80
[informix@db2 backups]$ strings db2_0_L0 | head -30
Archive Backup TapeIBM Informix Dynamic Server Version 14.10.FC3DETue Mar 31 09:55:07 2020informix/dev/pts/0
/work1/informix/ids1410fc3/backups/
aes192
DQZQ
QX9p
a5b(
H%*g
wecI
)rO0"
z\$b
#_t;
azXX
aW`g
$9s~f
{Rus
T<.u
(;*&
[!wS
{'dT
g9c%
k#|~
G)9)1
>X2B
CnSq
]SJ~
kmt9
ol!n
>JMsy
b!9~`
[C4R
-- 4. 통합 백업 암호화 기능 및 BACKUP_FILTER, RESTORE_FILTER를 설정하고 ontape 백업 수행
[informix@db2 backups]$ vi $INFORMIXDIR/etc/$ONCONFIG
[informix@db2 backups]$ onstat -c | egrep '^BACKUP_FILTER|^RESTORE_FILTER|^BAR_ENC'
BACKUP_FILTER /bin/gzip
RESTORE_FILTER /bin/gunzip
BAR_ENCRYPTION keyfile=/work1/informix/ids1410fc3/etc/l_key192,cipher=aes192
[informix@db2 backups]$ ontape -s -L 0
The backup volume will be encrypted.
Using the backup and restore filter /bin/gzip.
10 percent done.
20 percent done.
30 percent done.
40 percent done.
100 percent done.
File created: /work1/informix/ids1410fc3/backups/db2_0_L0
Please label this tape as number 1 in the arc tape sequence.
This tape contains the following logical logs:
 247
Program over.
[informix@db2 backups]$ ls -la
total 548100
drwxrwxr-x.  3 informix informix        97 Mar 31 09:56 .
drwxr-xr-x. 28 informix informix      4096 Mar 21 22:30 ..
-rw-rw----.  1 informix informix 273186816 Mar 31 09:54 db2_0_20200331_095432_L0
-rw-rw----.  1 informix informix 273186816 Mar 31 09:55 db2_0_20200331_095507_L0
-rw-rw----.  1 informix informix  14876672 Mar 31 09:56 db2_0_L0
drwxrwxr-x.  2 informix informix       182 Mar 31 09:51 tmp
-- 5. 통합 백업 암호화 기능 및 BACKUP_FILTER, RESTORE_FILTER를 설정하고 ontape 리스토어 수행
[informix@db2 backups]$ onmode -ky
[informix@db2 backups]$ onstat -c | egrep '^BACKUP_FILTER|^RESTORE_FILTER|^BAR_ENC'
BACKUP_FILTER /bin/gzip
RESTORE_FILTER /bin/gunzip
BAR_ENCRYPTION keyfile=/work1/informix/ids1410fc3/etc/l_key192,cipher=aes192
[informix@db2 backups]$ ontape -r
Restore will use level 0 archive file /work1/informix/ids1410fc3/backups/db2_0_L0. Press Return to continue ...
The volume to restore is encrypted.
Using the backup and restore filter /bin/gunzip.
Archive Tape Information
Tape type:      Archive Backup Tape
Online version: IBM Informix Dynamic Server Version 14.10.FC3DE
Archive date:   Tue Mar 31 09:56:06 2020
User id:        informix
Terminal id:    /dev/pts/0
Archive level:  0
Tape device:    /work1/informix/ids1410fc3/backups/
Tape blocksize (in k): 32
Tape size (in k): system defined for directory
Tape number in series: 1
Backup filter:  /bin/gzip
Spaces to restore:1 [rootdbs                                                                                                                         ]
2 [plog                                                                                                                            ]
3 [llog                                                                                                                            ]
4 [datadbs1                                                                                                                        ]
5 [datadbs2                                                                                                                        ]
6 [datadbs3                                                                                                                        ]
7 [data8dbs1                                                                                                                       ]
8 [data8dbs2                                                                                                                       ]
9 [data8dbs3                                                                                                                       ]
10 [sbspace1                                                                                                                        ]
Archive Information
IBM Informix Dynamic Server Copyright 2001, 2019  IBM Corporation
Initialization Time       01/08/2020 22:26:54
System Page Size          2048
Version                   31
Index Page Logging        OFF
Archive CheckPoint Time   03/31/2020 09:56:05
Dbspaces
number   flags    fchunk   nchunks  flags    owner                            name
1        10000001 1        2        N   AE   informix                         rootdbs                                                                                                        
2        11000001 2        1        N P AE   informix                         plog                                                                                                           
3        10000001 3        1        N   AE   informix                         llog                                                                                                           
4        10000001 4        1        N   AE   informix                         datadbs1                                                                                                       
5        10000001 5        1        N   AE   informix                         datadbs2                                                                                                       
6        10000001 6        1        N   AE   informix                         datadbs3                                                                                                       
7        10000001 7        1        N   AE   informix                         data8dbs1                                                                                                      
8        10000001 8        1        N   AE   informix                         data8dbs2                                                                                                      
9        10000001 9        1        N   AE   informix                         data8dbs3                                                                                                      
10       10002001 10       1        N T AE   informix                         tmpdbspace                                                                                                     
11       10008001 11       1        N S AE   informix                         sbspace1                                                                                                       
12       1000a001 12       1        N U AE   informix                         tmpsbspace                                                                                                     
Chunks
chk/dbs offset   size     free     bpages   flags pathname
1   1   0        78848    9236              PO--- /work1/informix/ids1410fc3/storage/rootdbs
2   2   0        32768    0                 PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_plog_p_1
3   3   0        70244    4791              PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_llog_p_1
4   4   0        32768    21236             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_datadbs1_p_1
5   5   0        32768    32715             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_datadbs2_p_1
6   6   0        32768    32715             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_datadbs3_p_1
7   7   0        32768    32556             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_data8dbs1_p_1
8   8   0        32768    32556             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_data8dbs2_p_1
9   9   0        32768    32556             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_data8dbs3_p_1
10  10  0        429812   429376            PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_tmpdbspace_p_1
11  11  0        32768    8466              POS-- /work1/informix/ids1410fc3/storage/ol_informix1410_2_sbspace1_p_1
12  12  0        32768    8466              POS-- /work1/informix/ids1410fc3/storage/ol_informix1410_2_tmpsbspace_p_1
13  1   0        60314    72                PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_rootdbs_p_1
Continue restore? (y/n)y
Do you want to back up the logs? (y/n)n
The volume to restore is encrypted.
Using the backup and restore filter /bin/gunzip.
Warning: Parameter's user-configured value was adjusted. (DS_MAX_SCANS)
Warning: Parameter's user-configured value was adjusted. (ONLIDX_MAXMEM)
Restore a level 1 archive (y/n) n
Do you want to restore log tapes? (y/n)n
/work1/informix/ids1410fc3/bin/onmode -sy
Program over.
[informix@db2 backups]$ onstat -m
IBM Informix Dynamic Server Version 14.10.FC3DE -- Quiescent -- Up 00:00:26 -- 566920 Kbytes
Message Log File: /work1/informix/ids1410fc3/ol_informix1410.log
10:11:58  Physical Recovery Complete: 0 Pages Examined, 0 Pages Restored.
10:11:58  Clearing encrypted primary chunk 10 before initialization...
10:11:58  Clearing encrypted primary chunk 12 before initialization...
10:11:58  Logical Recovery Started.
10:11:58  72 recovery worker threads will be started.
10:12:00  Logical Recovery has reached the transaction cleanup phase.
10:12:00  Logical Recovery Complete.
          0 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks
10:12:01  Bringing system to Quiescent Mode with no Logical Restore.
10:12:02  Quiescent Mode
10:12:02  Checkpoint Completed:  duration was 0 seconds.
10:12:02  Tue Mar 31 - loguniq 247, logpos 0x1ee018, timestamp: 0xeb6d094 Interval: 264
10:12:02  Maximum server connections 0
10:12:02  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 0, Llog used 1
10:12:04  Defragmenter cleaner thread now running
10:12:04  Defragmenter cleaner thread cleaned:0 partitions

테스트 결과로 보면 암호화된 백업본이라도 압축이 잘 되는 것을 확인할 수 있습니다.

 

참고 사이트 :

https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.bar.doc/ids_bar_integrated_encryption.htm

 

728x90
저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

Informix 12.10.xC14의 CHAR_LENGTH 함수 오류  (0) 2020.04.14
프로시저로 UNLOAD 실행하기  (0) 2020.04.09
rlwrap으로 dbaccess 편하게 사용하기  (0) 2020.03.27
Informix 12.10.xC14의 LPAD/RPAD 함수 오류  (0) 2020.03.20
OLAP함수로 가장 자주 나타나는 문자열 검색  (0) 2020.03.19
728x90

안녕하세요. IBM 커뮤니티에서 DBACCESS_COLUMNS 환경변수에 대한 질문을 올렸다가

Benjamin Thompson의 답변을 통해 rlwrap (readline wrapper) 이라는 유틸리티를 알게 되었습니다.

사실 알려진지는 꽤 된 것 같습니다만 저는 뒤늦게 알게되었네요 ^^;

리눅스나 유닉스용으로 설치패키지가 제공되네요. 리눅스의 경우는 yum 같은 툴을 이용해 설치하실 수 있습니다.

 

아래는 rlwrap을 이용해서 dbaccess를 실행한 과정입니다.

[informix@db2 ids1410fc3]$ alias dbaccess='DBACCESS_COLUMNS=1024 rlwrap dbaccess'
[informix@db2 ids1410fc3]$ dbaccess stores_demo -
Database selected.
> select * from customer;
customer_num fname           lname           company              address1             address2             city            state zipcode phone
         101 Ludwig          Pauli           All Sports Supplies  213 Erstwild Court                        Sunnyvale       CA    94086   408-789-8075
         102 Carole          Sadler          Sports Spot          785 Geary St                              San Francisco   CA    94117   415-822-1289
         103 Philip          Currie          Phil's Sports        654 Poplar           P. O. Box 3498       Palo Alto       CA    94303   415-328-4543
         104 Anthony         Higgins         Play Ball!           East Shopping Cntr.  422 Bay Road         Redwood City    CA    94026   415-368-1100
         105 Raymond         Vector          Los Altos Sports     1899 La Loma Drive                        Los Altos       CA    94022   415-776-3249
         106 George          Watson          Watson & Son         1143 Carver Place                         Mountain View   CA    94063   415-389-8789
         107 Charles         Ream            Athletic Supplies    41 Jordan Avenue                          Palo Alto       CA    94304   415-356-9876
         108 Donald          Quinn           Quinn's Sports       587 Alvarado                              Redwood City    CA    94063   415-544-8729
         109 Jane            Miller          Sport Stuff          Mayfair Mart         7345 Ross Blvd.      Sunnyvale       CA    94086   408-723-8789
         110 Roy             Jaeger          AA Athletics         520 Topaz Way                             Redwood City    CA    94062   415-743-3611
         111 Frances         Keyes           Sports Center        3199 Sterling Court                       Sunnyvale       CA    94085   408-277-7245
         112 Margaret        Lawson          Runners & Others     234 Wyandotte Way                         Los Altos       CA    94022   415-887-7235
         113 Lana            Beatty          Sportstown           654 Oak Grove                             Menlo Park      CA    94025   415-356-9982
         114 Frank           Albertson       Sporting Place       947 Waverly Place                         Redwood City    CA    94062   415-886-6677
         115 Alfred          Grant           Gold Medal Sports    776 Gary Avenue                           Menlo Park      CA    94025   415-356-1123
         116 Jean            Parmelee        Olympic City         1104 Spinosa Drive                        Mountain View   CA    94040   415-534-8822
         117 Arnold          Sipes           Kids Korner          850 Lytton Court                          Redwood City    CA    94063   415-245-4578
         118 Dick            Baxter          Blue Ribbon Sports   5427 College                              Oakland         CA    94609   415-655-0011
         119 Bob             Shorter         The Triathletes Club 2405 Kings Highway                        Cherry Hill     NJ    08002   609-663-6079
         120 Fred            Jewell          Century Pro Shop     6627 N. 17th Way                          Phoenix         AZ    85016   602-265-8754
         121 Jason           Wallack         City Sports          Lake Biltmore Mall   350 W. 23rd Street   Wilmington      DE    19898   302-366-7511
         122 Cathy           O'Brian         The Sporting Life    543 Nassau Street                         Princeton       NJ    08540   609-342-0054
         123 Marvin          Hanlon          Bay Sports           10100 Bay Meadows Ro Suite 1020           Jacksonville    FL    32256   904-823-4239
         124 Chris           Putnum          Putnum's Putters     4715 S.E. Adams Blvd Suite 909C           Bartlesville    OK    74006   918-355-2074
         125 James           Henry           Total Fitness Sports 1450 Commonwealth Av                      Brighton        MA    02135   617-232-4159
         126 Eileen          Neelie          Neelie's Discount Sp 2539 South Utica Str                      Denver          CO    80219   303-936-7731
         127 Kim             Satifer         Big Blue Bike Shop   Blue Island Square   12222 Gregory Street Blue Island     NY    60406   312-944-5691
         128 Frank           Lessor          Phoenix University   Athletic Department  1817 N. Thomas Road  Phoenix         AZ    85008   602-533-1817
28 row(s) retrieved.
> select * from customer;

rlwrap을 통해 dbaccess를 실행하면 프롬프트에서 화살표키를 사용해 이전에 수행한 문장을 불러올 수 있습니다.

 

또한 12.10.xC9 버전부터 dbaccess의 라인이 80자 이상 출력이 가능하도록 DBACCESS_COLUMNS라는 환경변수가 추가되었습니다. 이 기능을 dbaccess를 실행할 때 같이 응용하면 좋을 것 같네요.

 

728x90
저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

프로시저로 UNLOAD 실행하기  (0) 2020.04.09
인포믹스의 통합 백업 암호화 기능  (0) 2020.03.31
Informix 12.10.xC14의 LPAD/RPAD 함수 오류  (0) 2020.03.20
OLAP함수로 가장 자주 나타나는 문자열 검색  (0) 2020.03.19
USE_FALLOCATE 설정으로 Chunk 빠르게 만들기  (0) 2020.03.10
728x90

인포믹스 12.10.FC14버전에서 발견된 defect를 공유드립니다.

경우에 따라서는 심각한 문제가 될 것 같은데요. LPAD와 RPAD 함수 사용시 발생하는 문제입니다.

 

오류가 발생하는 조건은 아래와 같습니다.

1) multi-byte character set으로 구성된 데이터베이스 (en_us.utf8, ko_kr.ksc, ko_kr.949등)

2) source string이 varchar 또는 lvarchar 타입일 경우

3) source string이 empty string('')인 경우

 

상당히 오류가 발생할 가능성이 높은 조건입니다. 아래는 오류를 재현한 과정입니다.

/work2/INFORMIX/1210FC14]export DB_LOCALE=en_us.utf8
/work2/INFORMIX/1210FC14]export CLIENT_LOCALE=en_us.utf8
/work2/INFORMIX/1210FC14]echo "create database tdb with log"  | dbaccess -
Database created.
Database closed.
/work2/INFORMIX/1210FC14]dbaccess tdb -
Database selected.
> select lpad ( '', 2, '0' ) from sysmaster:sysdual;
  202: An illegal character has been found in the statement.
Error in line 1
Near character position 48
> select rpad ( '', 2, '0' ) from sysmaster:sysdual;
  202: An illegal character has been found in the statement.
Error in line 1
Near character position 48
>

IBM Community에 문의한 결과 이번 문제는 APAR IT32236 LPAD() and RPAD() on empty string getting -202 when used in multi-byte character set database로 확정되었습니다.

 

12.10.xC14 Fix Pack이 공개된지 얼마되지 않았는데 안타깝네요.

기존에는 문제없이 작동하는 기능이었기 때문에 패치적용시 우선적으로 고려해야 할 사항입니다.

 

추가로 확인된 바로는 12.10.xC15에서 수정될 예정입니다. 2020년 2분기에 공개될 예정입니다.

728x90
저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

인포믹스의 통합 백업 암호화 기능  (0) 2020.03.31
rlwrap으로 dbaccess 편하게 사용하기  (0) 2020.03.27
OLAP함수로 가장 자주 나타나는 문자열 검색  (0) 2020.03.19
USE_FALLOCATE 설정으로 Chunk 빠르게 만들기  (0) 2020.03.10
프로시저의 PDQ 값 확인하기  (0) 2020.02.27
728x90

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

 

최빈값(最頻-), 모드(mode)는 통계학 용어로, 가장 많이 관측되는 수, 즉 주어진 값 중에서 가장 자주 나오는 값이다. 예를 들어, {1, 3, 6, 6, 6, 7, 7, 12, 12, 17}의 최빈값은 6이다. 최빈값은 산술 평균과 달리 유일한 값이 아닐 수도 있다.

또한 주어진 자료나 관측치의 값이 모두 다른 경우에는 존재하지 않는다.

주어진 자료에서 평균이나 중앙값을 구하기 어려운 경우에 특히 유용하다.

 

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

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

 

<샘플데이터>

Name dob score
JESSE 1992/10/27 10
JESSE 1992/10/27 20
JESSE 1992/11/06 30
JESSE 1992/11/11 40
JESSICA 1992/03/11 50
JESSICA 1992/11/03 60
JESSICA 1992/10/29 70
JESSICA 1992/11/10 80
JESSICA 1992/11/30 90
JESSICA 1992/11/12 10
JESSICA 1992/12/07 20
JESSICA 1992/12/09 30

 

<결과세트>

Name dob avg(score)
JESSE 1992/12/07  
JESSICA 1992/11/??  

 

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

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

728x90
저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

rlwrap으로 dbaccess 편하게 사용하기  (0) 2020.03.27
Informix 12.10.xC14의 LPAD/RPAD 함수 오류  (0) 2020.03.20
USE_FALLOCATE 설정으로 Chunk 빠르게 만들기  (0) 2020.03.10
프로시저의 PDQ 값 확인하기  (0) 2020.02.27
Extent 최대 개수 제한 증가  (0) 2020.02.17
728x90

안녕하세요. 인포믹스 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)이 활성화 된 상태에서는 이 옵션이 적용되지 않는 것 같습니다.

 

728x90
저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

Informix 12.10.xC14의 LPAD/RPAD 함수 오류  (0) 2020.03.20
OLAP함수로 가장 자주 나타나는 문자열 검색  (0) 2020.03.19
프로시저의 PDQ 값 확인하기  (0) 2020.02.27
Extent 최대 개수 제한 증가  (0) 2020.02.17
Index의 페이지 수 제한 증가  (0) 2020.02.16
728x90

Informix는 PDQ 값에 따라 가용한 스레드 수만큼 작업이 병렬로 수행됩니다.

PDQ 값은 세션에서 SET PDQPRIORITY 문장으로 설정할 수 있는데요. 프로시저가 컴파일될때 이 값이 인코딩되면서 카탈로그 테이블에 저장된다고 합니다.

Fernando 의 블로그에서 루틴의 PDQ값을 참조할 수 있는 자세한 설명을 보실 수 있습니다.

http://informix-technology.blogspot.com/2011/01/stored-procedure-pdq-pdq-dos.html

 

Stored procedure PDQ / PDQ dos procedimentos

This article is written in English and Portuguese Este artigo está escrito em Inglês e Português English version: A few years ago I needed...

informix-technology.blogspot.com

프로시저 내부 작업들이 병렬로 수행되는 것은 참 좋아보입니다만 시스템 자원을 과도하게 사용할 가능성도 생깁니다.

따라서 PDQ 값을 0또는 작은 값으로 설정하고 프로시저를 컴파일하는 것이 좋겠네요.

Informix의 각 데이터베이스 별로 sysprocplan과 sysprocedures 테이블이 존재합니다. 아래는 카탈로그 테이블들의 각 컬럼 값을 참조하여 PDQ값을 확인하는 사용자 정의 함수 코드입니다.

CREATE FUNCTION get_proc_pdq_value(v_proc_name VARCHAR(128))
RETURNING SMALLINT;
DEFINE v_ret_pdq_value SMALLINT;
SELECT
 CASE data[1,3]
     WHEN "AAA" THEN 0
     WHEN "AQA" THEN 1
     WHEN "AAE" THEN 1
     WHEN "AgA" THEN 2
     WHEN "AAI" THEN 2
     WHEN "AwA" THEN 3
     WHEN "AAM" THEN 3
     WHEN "BAA" THEN 4
     WHEN "AAQ" THEN 4
     WHEN "BQA" THEN 5
     WHEN "AAU" THEN 5
     WHEN "BgA" THEN 6
     WHEN "AAY" THEN 6
     WHEN "BwA" THEN 7
     WHEN "AAc" THEN 7
     WHEN "CAA" THEN 8
     WHEN "AAg" THEN 8
     WHEN "CQA" THEN 9
     WHEN "AAk" THEN 9
     WHEN "CgA" THEN 10
     WHEN "AAo" THEN 10
     WHEN "CwA" THEN 11
     WHEN "AAs" THEN 11
     WHEN "DAA" THEN 12
     WHEN "AAw" THEN 12
     WHEN "DQA" THEN 13
     WHEN "AA0" THEN 13
     WHEN "DgA" THEN 14
     WHEN "AA4" THEN 14
     WHEN "DwA" THEN 15
     WHEN "AA8" THEN 15
     WHEN "EAA" THEN 16
     WHEN "ABA" THEN 16
     WHEN "EQA" THEN 17
     WHEN "ABE" THEN 17
     WHEN "EgA" THEN 18
     WHEN "ABI" THEN 18
     WHEN "EwA" THEN 19
     WHEN "ABM" THEN 19
     WHEN "FAA" THEN 20
     WHEN "ABQ" THEN 20
     WHEN "FQA" THEN 21
     WHEN "ABU" THEN 21
     WHEN "FgA" THEN 22
     WHEN "ABY" THEN 22
     WHEN "FwA" THEN 23
     WHEN "ABc" THEN 23
     WHEN "GAA" THEN 24
     WHEN "ABg" THEN 24
     WHEN "GQA" THEN 25
     WHEN "ABk" THEN 25
     WHEN "GgA" THEN 26
     WHEN "ABo" THEN 26
     WHEN "GwA" THEN 27
     WHEN "ABs" THEN 27
     WHEN "HAA" THEN 28
     WHEN "ABw" THEN 28
     WHEN "HQA" THEN 29
     WHEN "AB0" THEN 29
     WHEN "HgA" THEN 30
     WHEN "AB4" THEN 30
     WHEN "HwA" THEN 31
     WHEN "AB8" THEN 31
     WHEN "IAA" THEN 32
     WHEN "ACA" THEN 32
     WHEN "IQA" THEN 33
     WHEN "ACE" THEN 33
     WHEN "IgA" THEN 34
     WHEN "ACI" THEN 34
     WHEN "IwA" THEN 35
     WHEN "ACM" THEN 35
     WHEN "JAA" THEN 36
     WHEN "ACQ" THEN 36
     WHEN "JQA" THEN 37
     WHEN "ACU" THEN 37
     WHEN "JgA" THEN 38
     WHEN "ACY" THEN 38
     WHEN "JwA" THEN 39
     WHEN "ACc" THEN 39
     WHEN "KAA" THEN 40
     WHEN "ACg" THEN 40
     WHEN "KQA" THEN 41
     WHEN "ACk" THEN 41
     WHEN "KgA" THEN 42
     WHEN "ACo" THEN 42
     WHEN "KwA" THEN 43
     WHEN "ACs" THEN 43
     WHEN "LAA" THEN 44
     WHEN "ACw" THEN 44
     WHEN "LQA" THEN 45
     WHEN "AC0" THEN 45
     WHEN "LgA" THEN 46
     WHEN "AC4" THEN 46
     WHEN "LwA" THEN 47
     WHEN "AC8" THEN 47
     WHEN "MAA" THEN 48
     WHEN "ADA" THEN 48
     WHEN "MQA" THEN 49
     WHEN "ADE" THEN 49
     WHEN "MgA" THEN 50
     WHEN "ADI" THEN 50
     WHEN "MwA" THEN 51
     WHEN "ADM" THEN 51
     WHEN "NAA" THEN 52
     WHEN "ADQ" THEN 52
     WHEN "NQA" THEN 53
     WHEN "ADU" THEN 53
     WHEN "NgA" THEN 54
     WHEN "ADY" THEN 54
     WHEN "NwA" THEN 55
     WHEN "ADc" THEN 55
     WHEN "OAA" THEN 56
     WHEN "ADg" THEN 56
     WHEN "OQA" THEN 57
     WHEN "ADk" THEN 57
     WHEN "OgA" THEN 58
     WHEN "ADo" THEN 58
     WHEN "OwA" THEN 59
     WHEN "ADs" THEN 59
     WHEN "PAA" THEN 60
     WHEN "ADw" THEN 60
     WHEN "PQA" THEN 61
     WHEN "AD0" THEN 61
     WHEN "PgA" THEN 62
     WHEN "AD4" THEN 62
     WHEN "PwA" THEN 63
     WHEN "AD8" THEN 63
     WHEN "QAA" THEN 64
     WHEN "AEA" THEN 64
     WHEN "QQA" THEN 65
     WHEN "AEE" THEN 65
     WHEN "QgA" THEN 66
     WHEN "AEI" THEN 66
     WHEN "QwA" THEN 67
     WHEN "AEM" THEN 67
     WHEN "RAA" THEN 68
     WHEN "AEQ" THEN 68
     WHEN "RQA" THEN 69
     WHEN "AEU" THEN 69
     WHEN "RgA" THEN 70
     WHEN "AEY" THEN 70
     WHEN "RwA" THEN 71
     WHEN "AEc" THEN 71
     WHEN "SAA" THEN 72
     WHEN "AEg" THEN 72
     WHEN "SQA" THEN 73
     WHEN "AEk" THEN 73
     WHEN "SgA" THEN 74
     WHEN "AEo" THEN 74
     WHEN "SwA" THEN 75
     WHEN "AEs" THEN 75
     WHEN "TAA" THEN 76
     WHEN "AEw" THEN 76
     WHEN "TQA" THEN 77
     WHEN "AE0" THEN 77
     WHEN "TgA" THEN 78
     WHEN "AE4" THEN 78
     WHEN "TwA" THEN 79
     WHEN "AE8" THEN 79
     WHEN "UAA" THEN 80
     WHEN "AFA" THEN 80
     WHEN "UQA" THEN 81
     WHEN "AFE" THEN 81
     WHEN "UgA" THEN 82
     WHEN "AFI" THEN 82
     WHEN "UwA" THEN 83
     WHEN "AFM" THEN 83
     WHEN "VAA" THEN 84
     WHEN "AFQ" THEN 84
     WHEN "VQA" THEN 85
     WHEN "AFU" THEN 85
     WHEN "VgA" THEN 86
     WHEN "AFY" THEN 86
     WHEN "VwA" THEN 87
     WHEN "AFc" THEN 87
     WHEN "WAA" THEN 88
     WHEN "AFg" THEN 88
     WHEN "WQA" THEN 89
     WHEN "AFk" THEN 89
     WHEN "WgA" THEN 90
     WHEN "AFo" THEN 90
     WHEN "WwA" THEN 91
     WHEN "AFs" THEN 91
     WHEN "XAA" THEN 92
     WHEN "AFw" THEN 92
     WHEN "XQA" THEN 93
     WHEN "AF0" THEN 93
     WHEN "XgA" THEN 94
     WHEN "AF4" THEN 94
     WHEN "XwA" THEN 95
     WHEN "AF8" THEN 95
     WHEN "YAA" THEN 96
     WHEN "AGA" THEN 96
     WHEN "YQA" THEN 97
     WHEN "AGE" THEN 97
     WHEN "YgA" THEN 98
     WHEN "AGI" THEN 98
     WHEN "YwA" THEN 99
     WHEN "AGM" THEN 99
     WHEN "ZAA" THEN 100
     WHEN "AGQ" THEN 100
 ELSE
     -1
 END pdq_value
INTO
 v_ret_pdq_value
FROM
 sysprocplan p, sysprocedures f
WHERE
 p.planid = -2 AND
 f.procid = p.procid AND
 f.procname = v_proc_name;
IF v_ret_pdq_value = -1
THEN
 RAISE EXCEPTION -746,0,'Could not decode PDQ value. Please check query';
ELSE
 RETURN v_ret_pdq_value;
END IF
END FUNCTION;

과연 일반 사용자들은 짐작조차 할 수 없겠군요. 어쨌든 이 함수를 사용해 각 함수나 프로시저의 PDQ값을 참조하여 재컴파일 등을 고려해볼 필요도 있겠습니다.

제가 프로시저를 재컴파일한 테스트 결과를 공유드립니다.

$ dbaccess testdb -
Database selected.
> select get_proc_pdq_value('temp_test') from systables where tabid = 1;
(expression)
          80
1 row(s) retrieved.
> set pdqpriority 100;
PDQ Priority set.
> update statistics for routine;
Routine Statistics updated.
> select get_proc_pdq_value('temp_test') from systables where tabid = 1;
(expression)
         100
1 row(s) retrieved.
> set pdqpriority 0;
PDQ Priority set.
> update statistics for routine;
Routine Statistics updated.
> select get_proc_pdq_value('temp_test') from systables where tabid = 1;
(expression)
           0
1 row(s) retrieved.
728x90
저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

OLAP함수로 가장 자주 나타나는 문자열 검색  (0) 2020.03.19
USE_FALLOCATE 설정으로 Chunk 빠르게 만들기  (0) 2020.03.10
Extent 최대 개수 제한 증가  (0) 2020.02.17
Index의 페이지 수 제한 증가  (0) 2020.02.16
문자열의 특수문자 제거하기  (0) 2020.01.17
728x90

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
                  ...
728x90
저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

USE_FALLOCATE 설정으로 Chunk 빠르게 만들기  (0) 2020.03.10
프로시저의 PDQ 값 확인하기  (0) 2020.02.27
Index의 페이지 수 제한 증가  (0) 2020.02.16
문자열의 특수문자 제거하기  (0) 2020.01.17
Informix의 파티션 테이블에서 rowid 참조하기  (0) 2020.01.03
728x90

예전에 Ben Thompson의 블로그에서 Index fragment의 page limit에 대한 글을 읽었습니다.

2014년의 글인데 Index의 페이지 수가 12.1버전부터 2^31 (2,147,483,647)개로 증가했다는 것이지요.

IBM Knowledge Center에도 문서화되어 있습니다.

https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_adr_0718.htm#ids_adr_0718__table

 

블로그의 저자인 Ben은 11.7 버전에서도 테스트해보았다는 내용이 있습니다. 그러나 IBM 문서에는 해당 내용이 없어서, IBM Community에 자문을 구하고자 질문을 올렸고 Art와 Ben이 몇가지 조언을 주어서 직접 테스트를 하게 되었습니다.

 

테스트 환경은 인포믹스 11.50.FC6과 11.70.FC5이고, 테스트 시나리오를 위해 고객사의 테이블 스키마를 사용했습니다.

고객은 인포믹스 11.7 버전을 사용하고 있고, 매달 생성하는 테이블이 있는데 최근 몇달간의 데이터를 보니 약 4억4천만건의 레코드가 저장되고 있더군요.

문제는 테이블은 라운드 로빈(round robin) 형태인데 primary key에 해당하는 unique index는 분할되지 않아 기존에 알고 있던 한계치인 2^24 (16,775,134) 개에 육박하고 있다는 점입니다.

그런데 막상 IBM Knowledge Center의 11.7 버전의 문서에는 인덱스 페이지 수의 제한은 나와있지 않고..

 

이런 상황을 가정하고 아래의 테이블을 만들었습니다.

create table log_12
  (
    col1 varchar (50),
    col2 varchar (32),
    col3 varchar (5),
    col4 varchar (5),
    col5 varchar (10)
   .....
  ) fragment by round robin in dbs1, dbs2, dbs3
extent size 16000000 next size 10000000;​

데이터 입력 시간을 절약하기 위해, 더미 데이터를 약 5억건 입력하고 아래 인덱스를 만들었습니다.

create unique index log_12_pk on log_12 (col1, col2, col3, col4, col5) in idx1;​

11.5에서는 인덱스의 페이지 수가 2^24 (16,775,134)개에 도달하자 인덱스를 생성하는 트랜잭션은 롤백되면서 online.log에는 아래와 같은 메시지가 출력되었습니다.

15:22:42 partition 'demo: informix.log_12_pk': no more pages

11.7에서는 인덱스 생성이 완료되었고, 인덱스에 35,999,944개의 페이지가 할당되었습니다.

아래는 oncheck -pt의 결과물의 일부입니다.

                  Index log_12_pk fragment partition idx1 in DBspace idx1
    Physical Address               5:5
    Creation date                  02/14/2020 16:47:53
    TBLspace Flags                 801        Page Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               107
    Number of special columns      0
    Number of keys                 1
    Number of extents              2
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              8672897
    Next extent size               5420560
    Number of pages allocated      35999944
    Number of pages used           35705525
    Number of data pages           0
    Number of rows                 0
    Partition partnum              5242882
    Partition lockid               2097217
    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0              5:53    23999947   23999947
             23999947               9:3    11999997   11999997

인포믹스 11.7 이상이라면 인덱스 페이지 수 제한을 걱정하지 않아도 되겠군요.

 

추가로 IBM에 공식적으로 문의한 결과, 11.7 문서화에 명시되지 않았을 뿐 인덱스 페이지 수 제한은 12.1 버전과 마찬가지로 2^31 (2,147,483,647)개임을 확인했습니다.

728x90
저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

프로시저의 PDQ 값 확인하기  (0) 2020.02.27
Extent 최대 개수 제한 증가  (0) 2020.02.17
문자열의 특수문자 제거하기  (0) 2020.01.17
Informix의 파티션 테이블에서 rowid 참조하기  (0) 2020.01.03
Informix 무료 버전 14.10.xC2의 제한 사항 완화  (0) 2019.11.29
728x90

문자열에서 특수문자를 제거하는 방법을 찾아보았습니다. 엔터나 탭같은 보이지 않는 문자를 'nonprintable character'라고 부르기도 하는데요. 인포믹스에서는 이런 문자열을 확인하려면 데이터를 파일로 내려받습니다.

> insert into test values ('a'||chr(10)||'bcd'||chr(9)||'efg');
1 row(s) inserted.
> select * from test;
a  a
bcd     efg
$ echo "unload to test select * from test" | dbaccess -X kr_iis
Database selected.
1 row(s) unloaded.
Database closed
$ cat test
a\0abcd\09efg|

dbaccess 유틸리티에서 -X 옵션을 사용하면 nonprintable character를 16진수 형식으로 보여줍니다.

화면에는 표시되지않고 UNLOAD를 사용하여 파일로 내려받았을 때 보입니다.

 

REPLACE를 사용해서 일일이 특수문자를 치환할 수도 있지만, REGEX_REPLACE 함수를 사용해서 아래처럼 간단하게 사용할 수도 있습니다. 오라클의 REGEXP_REPLACE와 사용법은 동일합니다. ( chr(1) 부터 chr(31) 까지 )

> SELECT REGEX_REPLACE (A, '['||CHR(1)||'-'||CHR(31)||']','') FROM TEST;
(expression)  abcdefg

 

범위를 세분화하려면 아래와 같이 사용할 수도 있습니다. ( chr(1) 부터 chr(9), chr(11) 부터 chr(31) )

> SELECT REGEX_REPLACE (A, '['||CHR(1)||'-'||CHR(9)||']|['||CHR(11)||'-'||CHR(31)||']','') FROM TEST;
(expression)  a
bcdefg

 

사용방법은 아래 사이트를 참고했습니다.

https://www.ascii-code.com/

http://mrrame.blogspot.com/2010/05/oracle-how-to-replace-non-printable.html

728x90

'Informix > informix reference' 카테고리의 다른 글

Extent 최대 개수 제한 증가  (0) 2020.02.17
Index의 페이지 수 제한 증가  (0) 2020.02.16
Informix의 파티션 테이블에서 rowid 참조하기  (0) 2020.01.03
Informix 무료 버전 14.10.xC2의 제한 사항 완화  (0) 2019.11.29
인포믹스에서 문자열을 구분자로 나누기  (0) 2019.08.02
728x90

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

728x90

'Informix > informix reference' 카테고리의 다른 글

Index의 페이지 수 제한 증가  (0) 2020.02.16
문자열의 특수문자 제거하기  (0) 2020.01.17
Informix 무료 버전 14.10.xC2의 제한 사항 완화  (0) 2019.11.29
인포믹스에서 문자열을 구분자로 나누기  (0) 2019.08.02
How to enable JDBC tracing starting with JDBC version 3.0.x  (0) 2019.05.19

+ Recent posts

티스토리툴바