pajama

Technote (troubleshooting)

http://www-01.ibm.com/support/docview.wss?uid=swg21567274

AIX 환경의 인포믹스 서버로 클라이언트 접속 지연 현상에 대해서 테스트해보았습니다.

먼저 AIX서버의 /etc/resolv.conf 파일의 내용은 아래와 같이 설정했습니다. (SK브로드밴드 DNS입니다)

nameserver 210.220.163.82

그리고 네임서비스를 위해 설정하는 /etc/netsvc.conf 파일의 내용은 기본적으로 주석만 있었으며

명시적으로 아래와 같이 순서를 지정할 수 있습니다.

hosts = local , bind

여기서 local은 /etc/hosts 파일을, bind는 DNS 서버를 참조하도록 합니다.

테스트를 위해 /etc/netsvc.conf 파일 내용을 수정해서 DB 접속시 오픈하는 파일을 확인해 보았습니다.

## /etc/netsvc.conf, hosts = bind

$ truss -d -o outfile_bind dbaccess sysmaster -e
$ cat outfile_bind | grep open

....

0.4493:        kopen("/etc/resolv.conf", O_RDONLY) = 6
0.4510:        kopen("/etc/netsvc.conf", O_RDONLY) = 6
0.4523:        kopen("/etc/hesiod.conf", O_RDONLY) Err#2  ENOENT
0.4525:        kopen("/etc/irs.conf", O_RDONLY) Err#2  ENOENT
150.5290:        kopen("/work2/INFORMIX/1150FC9/msg/itoxmsg.pam", O_RDONLY) = 6
150.5497:        kopen("/work2/INFORMIX/1150FC9/msg/en_us/0333/sql.iem", O_RDONLY) = 3

## /etc/netsvc.conf, hosts = local

$ truss -d -o outfile_local dbaccess sysmaster -e
$ cat outfile_bind | grep open

....

0.4696:        kopen("/etc/resolv.conf", O_RDONLY) = 6
0.4716:        kopen("/etc/netsvc.conf", O_RDONLY) = 6
0.4733:        kopen("/etc/irs.conf", O_RDONLY) Err#2  ENOENT
0.4779:        kopen("/etc/hosts", O_RDONLY)    = 6
0.4794:        kopen("/etc/hosts", O_RDONLY)    = 6
0.4970:        kopen("/work2/INFORMIX/1150FC9/msg/en_us/0333/sql.iem", O_RDONLY) = 3

/etc/netsvc.conf 파일의 hosts를

bind로 설정했을 때는 /etc/resolv.conf /etc/netsvc.conf, /etc/hesiod.conf, /etc/irs.conf 파일들을 참조하고,

local로 설정하면 /etc/resolv.conf 및 /etc/hosts 파일을 참조하는 것을 확인할 수 있습니다.

http://www-01.ibm.com/support/knowledgecenter/ssw_aix_71/com.ibm.aix.files/netsvc.conf.htm

http://informix-technology.blogspot.kr/2012/01/dns-impact-on-informix-impacto-do-dns.htmlhttp://www-01.ibm.com/support/docview.wss?uid=swg21661279
https://www.ibm.com/developerworks/community/blogs/idsteam/entry/why_dbaccess_always_query_dns_server_while_connecting_to_a_database?lang=en

Technote (troubleshooting)

http://www-01.ibm.com/support/docview.wss?uid=swg21395852

Technote (FAQ)

Question

Maximum number of pages and rows allowed in fragmented and unfragmented tables are related to the ROWID.

Encountered an sql error along with error 136, no more extents.

Answer

Limits in unfragmented tables and indexes

Every row in an unfragmented table has a number called a ROWID that uniquely identifies the row within the table and also indicates where in the table the row is stored. The ROWID for a row is represented by a 4 byte integer, the most significant 3 bytes of that integer indicate the page where the row is stored and the least significant byte indicates the slot on that page where the row is stored.

Tip: If you represent the ROWID as a hexidecimal number the leftmost 6 digits are the page number and the rightmost 2 digits are the slot number.

Example: 

A row with a ROWID of 0x4F0033 is located in slot 0x33 (51) of page 0x4F00 (20224).

3 bytes are used to store the page number a maximum of 16,777,215 pages can be used in one table. 

1 byte is used to store the slot number, a single page can have at most 255 slots/rows. 

The maximum number of rows in a table or fragment is 4,278,189,825. 

These are all theoretical limits. The practical limits are lower because of the following: 

• Pages are also used for purposes other than rows of data. Examples include attached indexes, and bitmap pages.
  
• Some pages are used as remainder pages.
  
• To fit the maximum number of rows in a data page the rows must be very small. Most of the time fewer rows are stored on a page, reducing the maximum number of rows that the table can hold.

Limits for fragmented tables and indexes

If a table is fragmented then the ROWID for each row is only guaranteed to be unique within its fragment. So to determine the limits in a fragmented table multiply the limits for an unfragmented table times the number of fragments used by your table. (The number of rows per page remains the same, of course) 

OnLine and Informix Dynamic Server (IDS) engines allow a maximum of 2047 fragments in a single table. IBM Informix Extended Parallel Server (XPS) allows 32766. 

http://www-01.ibm.com/support/docview.wss?uid=swg21105506

인포믹스 11.5버전에서 sysmaster DB 통계갱신을 MEDIUM/HIGH 모드로 실행했을 때 오류가 발생합니다.

10버전에서는 아래와 같이 오류가 발생했었습니다.

$ dbaccess sysmaster -

Database selected.

> update statistics high;

  243: Could not position within a table (informix.syscrtadt).

  158: ISAM error: Operation disallowed on SMI pseudo table

11.5버전에서는 아래와 같이 오류가 발생합니다.

$ echo "update statistics high " | dbaccess sysmaster

Database selected.

  244: Could not do a physical-order read to fetch next row.

  101: ISAM error:  file is not open.

low 모드로 통계갱신을 수행하면 이상이 없습니다.

medium, high 모드로는 오류가 발생하는 것이 정상으로 보입니다.

IC91526: UPDATE STATISTICS MEDIUM OR HIGH on SYSMASTER DATABASE PRODUCES LOTS OF ERROR MESSAGES IN MESSAGE LOG AND FAILS WITH ERROR 158

http://www-01.ibm.com/support/docview.wss?uid=swg1IC91526

IC70263: SELECT FROM REMOTE TABLES RETURNS ERROR 245 AND 101 AFTER UPDATESTATISTICS MEDIUM OR HIGH.

https://www.ibm.com/support/pages/apar/IC70263

Technote (FAQ)

http://www-01.ibm.com/support/docview.wss?uid=swg21444237

I believe that the answer you need is a user-defined aggregate, similar to this one:

CREATE FUNCTION gc_init(dummy VARCHAR(255)) RETURNING LVARCHAR;
    RETURN '';
END FUNCTION;
CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))
    RETURNING LVARCHAR;
    IF result = '' THEN
        RETURN TRIM(value);
    ELSE
        RETURN result || ',' || TRIM(value);
    END IF;
END FUNCTION;
CREATE FUNCTION gc_comb(partial1 LVARCHAR, partial2 LVARCHAR)
    RETURNING LVARCHAR;
    RETURN partial1 || ',' || partial2;
END FUNCTION;
CREATE FUNCTION gc_fini(final LVARCHAR) RETURNING LVARCHAR;
    RETURN final;
END FUNCTION;
CREATE AGGREGATE group_concat
    WITH (INIT = gc_init, ITER = gc_iter,
          COMBINE = gc_comb, FINAL = gc_fini);

Given a table of elements (called elements) with a column called name containing (funnily enough) the element name, and another column called atomic_number, this query produces this result:

SELECT group_concat(name) FROM elements WHERE atomic_number < 10;
Hydrogen,Helium,Lithium,Beryllium,Boron,Carbon,Nitrogen,Oxygen,Fluorine

Applied to the question, you should obtain the answer you need from:

SELECT id, group_concat(codes)
    FROM anonymous_table
    GROUP BY id;

CREATE TEMP TABLE anonymous_table
(
    id      INTEGER NOT NULL,
    codes   CHAR(4) NOT NULL,
    PRIMARY KEY (id, codes)
);
INSERT INTO anonymous_table VALUES(63592, 'PELL');
INSERT INTO anonymous_table VALUES(58640, 'SUBL');
INSERT INTO anonymous_table VALUES(58640, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'PELL');
INSERT INTO anonymous_table VALUES(73571, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'SUBL');
INSERT INTO anonymous_table VALUES(73572, 'USBL');
INSERT INTO anonymous_table VALUES(73572, 'PELL');
INSERT INTO anonymous_table VALUES(73572, 'SUBL');
SELECT id, group_concat(codes)
    FROM anonymous_table
    GROUP BY id
    ORDER BY id;

The output from that is:

58640 SUBL,USBL
63592 PELL
73571 PELL,SUBL,USBL
73572 PELL,SUBL,USBL

The extra set of data was added to test whether insert sequence affected the result; it appears not to do so (the codes are in sorted order; I'm not sure whether there's a way to alter - reverse - that order).

Note that this aggregate should be usable for any type that can be converted to VARCHAR(255), which means any numeric or temporal type. Long CHAR columns and blob types (BYTE, TEXT, BLOB, CLOB) are not handled.

http://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate?answertab=votes#tab-top

http://stackoverflow.com/questions/489081/group-concat-in-informix

data studio를 통해 인포믹스 액세스 플랜을 조회할 때 몇가지 시행착오에 대해 정리했습니다.

추적 파일 옵션을 켜고 파일 내용을 살펴본 것이 도움이 되었습니다.

1. 먼저 인포믹스 서버에 DRDA 프로토콜 연결 엔트리를 작성합니다. (drsoctop)

DRDA 프로토콜 연결이 아닌경우, 액세스 플랜을 조회하면 아래와 유사한 오류가 발생합니다.

[jcc][t4][2030][11211][3.68.61] 연결의 기본 소켓, 소켓 입력 스트림 또는 소켓 출력 스트림에 대한 조작 중에 통신 오류가 

발생했습니다. 오류 위치: Reply.fill() - insufficient data (-1). 메시지: 충분하지 않은 데이터. ERRORCODE=-4499, SQLSTATE=08001

2. Smart blob dbspace가 필요합니다.

원격에서 액세스 플랜을 조회할 때 XML형식의 데이터를 출력하는데 이를 위해 smart blob dbspace가 필요한 모양입니다. 

Invalid default sbspace name (sbspace).. SQLCODE=-9814, SQLSTATE=IX000, DRIVER=3.68.61

IDS_SBPSACE_NOT_CREATED: no message found for the key

3. Data Studio가 한글 환경일때 EXTDIRECTIVES, OPTIMLEVEL 등의 변수값을 영어로 수정해야 합니다.

한글 환경에서는 액세스 플랜 출력에 필요한 일부 변수값이 한글인 경우가 있습니다.

드롭다운 메뉴이기는 하지만 직접 입력하여 수정이 가능합니다.

COLLATION : en_us.8859-1 (한글의 경우 ko_kr.ksc, ko_kr.cp949, ko_kr.utf8)

EXTDIRECTIVES : on/OFF

OPTIMLEVEL : LOW/HIGH

변수가 한글 또는 잘못된 값인 경우 아래와 유사한 오류가 발생합니다.

Invalid values specified for the OPTIMLEVEL environment variable. SQLCODE: -26041, SQLSTATE: IX000

1. IBM Informix Developer’s Handbook (redbook)

2. Use EXPLAIN_SQL with any tool (Fernando Nunes)

3. http://informix-technology.blogspot.kr/2012/12/execution-plans-on-client-planos-de.html

Question

How do you rebuild sysadmin database manually without having to restart IDS?

Answer

http://www-01.ibm.com/support/docview.wss?uid=swg21420189

Technote (troubleshooting)

http://www-01.ibm.com/support/docview.wss?uid=swg21408223