'Informix' 카테고리의 글 목록 (7 Page)

Technote (FAQ)

Question

A checkpoint is taking too long. A checkpoint is stalled. Checkpoint performance is down.
How do you setup or enable checkpoint tracing?

Answer

1. Set the environment variable TRACECKPT to 1.

export TRACECKPT=1;

setenv TRACECKPT 1;

2. Restart the database server.

3. Look for checkpoint tracing information in the message log ( online.log ). Here are several examples of what you could see in the message log:

First example:

15:58:58 2040 buffers dirty

15:58:58 oldest lsn loguniq 1, logpos 0xf8

15:58:58 Reallocated 1500 dskptrs.

15:58:58 Reallocated 2000 dskptrs.

15:58:58 Reallocated 2500 dskptrs.

15:58:58 2040 dirty pages are to be flushed

15:59:02 dskflush() took 4 seconds

15:59:02 wait4critex() took 0 seconds

15:59:02 N = 2500, Avg = 1260, D_avg = 5040, Dirty = 2040

15:59:02 Reallocated 2000 dskptrs.

15:59:02 0 buffers dirty

15:59:02 oldest lsn loguniq 1, logpos 0xf8

15:59:02 safe_dskflush() took 0 seconds

15:59:02 Checkpoint Completed: duration was 4 seconds.

Second example:

15:59:56 1 buffers dirty

15:59:56 oldest lsn loguniq 4, logpos 0x187018

15:59:56 1 dirty pages are to be flushed

15:59:56 dskflush() took 0 seconds

15:59:56 wait4critex() took 0 seconds

15:59:56 N = 2000, Avg = 945, D_avg = 3781, Dirty = 1

15:59:56 Reallocated 1500 dskptrs.

15:59:56 0 buffers dirty

15:59:56 oldest lsn loguniq 4, logpos 0x187018

15:59:56 safe_dskflush() took 0 seconds

15:59:56 Checkpoint Completed: duration was 0 seconds.

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

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

syssessions 테이블에서 CURRENT 세션 정보가 표시되지 않는 문제 (0)	2019.02.11
Dropping or disabling a trigger fails with error -242 / -106 (0)	2018.10.08
깨진 문자열 UNLOAD 하기위한 설정 (0)	2018.08.01
non-exclusive lock 확인, 해제 방법 (0)	2017.07.19
Collect information when a recovery failure occurs with Informix Dynamic Server (0)	2015.09.08

인포믹스에 깨진 (일반적으로는 코드셋이 맞지 않은 경우) 문자열이 입력된 경우가 드물게 있습니다.

10버전 이하에서는 코드셋 확인이 느슨했기 때문에 코드셋이 맞지 않아도 데이터가 입력되는 경우가 있었던 것 같습니다.

또한 TEXT/CLOB 같은 문자열을 저장하기 위한 타입은 데이터베이스 코드셋에 없는 문자열 입력이 가능하므로 이러한 상황이 발생할 여지가 있습니다.

어쨌든 결론적으로 IFX_UNLOAD_EILSEQ_MODE 라는 환경변수를 설정함으로써 코드셋이 맞지 않은 데이터를 UNLOAD할 수 있습니다.

아래 IBM 문서 내용을 참고해주세요.

Use the IFX_UNLOAD_EILSEQ_MODE environment variable to help migrate databases from Informix® Version 10 to Version 11.50 or 11.70, where character data might be encoded with a codeset that is different than the codeset used to create the Version 10 database.

In earlier versions of Informix, it was possible to load character data into a database that did not match the locale and codeset of the database. For example you could load Chinese data into a database created with the DB_LOCALE=en_US.8859-1 codeset. In newer versions of Informix, to insert Chinese data you would need a database created with the Chinese (DB_LOCALE=zh_tw.big5 locale and codeset.

ImportantFor databases created with Version 10 and Client SDK 2.4, when you attempt to unload the invalid character data an error occurs unless you have set this environment variable. The IFX_UNLOAD_EILSEQ_MODE environment variable enables DB-Access, dbexport, and High Performance Loader (HPL) to unload character and bypass the GLS validation that normally occurs when you unload data by using the Version 11.50 and 11.70 tools.

To use this environment variable, set it to any non-empty string.


>>-setenv--IFX_UNLOAD_EILSEQ_MODE--value-----------------------><

value: Any alpha or numeric value. For example: yes, true, or 1.

This environment variable takes effect when character data is being fetched or retrieved from the database.

setenv IFX_UNLOAD_EILSEQ_MODE 1
setenv IFX_UNLOAD_EILSEQ_MODE yes
setenv IFX_UNLOAD_EILSEQ_MODE on

This environment variable is similar to setting the EILSEQ_COMPAT_MODE configuration parameter in the onCONFIG file. The configuration parameter affects character data that is inserted into the database, whereas the IFX_UNLOAD_EILSEQ_MODE environment variable affects character data that is unloaded from the database.

출처

https://www.ibm.com/support/knowledgecenter/ko/SSGU8G_11.70.0/com.ibm.sqlr.doc/ids_sqr_416.htm

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

Dropping or disabling a trigger fails with error -242 / -106 (0)	2018.10.08
How to setup or enable tracing on a Checkpoint (0)	2018.08.09
non-exclusive lock 확인, 해제 방법 (0)	2017.07.19
Collect information when a recovery failure occurs with Informix Dynamic Server (0)	2015.09.08
인포믹스 12.10 설치시 오류 (Windows 2012 R2) (0)	2015.08.05

Technote (FAQ)

Question

How to determine which tables require dummy updates in order to eliminate outstanding in-place alters before reversion to an earlier version?

Cause

In IBM Informix 11.70 there is a requirement for migration reversion: "In-place ALTER TABLE statements performed in the new version of the server must not be outstanding against any table."

Answer

1) Run 'oncheck -pt' command to identify which tables were altered in the current version. Qualifying tables must have tblspace flag 0x800000 set. For example:

# oncheck -pt tdb:tab1

TBLspace Report for tdb:dmitriyr.tab1

Table fragment partition par1 in DBspace dbs1

Physical Address 2:5

Creation date 06/25/2012 11:58:16

TBLspace Flags 800802 Row Locking

TBLspace use 4 bit bit-maps

<...>

As an alternative to 'oncheck -pt' you may run the following query against the Informix sysmaster database to find all qualifying tables:

SELECT ta.dbsname, ta.tabname, pt.partnum, HEX(pt.flags) flag

FROM sysmaster:systabnames ta, sysmaster:sysptnhdr pt

WHERE ta.partnum = pt.partnum

AND BIT_AND(HEX(pt.flags),'00800000'::BINARYVAR) = '00800000';

Sample result:

dbsname tdb

tabname tab1

partnum 2097154

flag 0x00800802

In above examples tblspace flag value is 0x800802. It is clear that value 0x800000 is set here.

2) Run 'oncheck -pT <database>:<table>' against those tables identified in p.1 to find which tables have outstanding in-place alters.

You must look at "Home Data Page Version Summary" section. For example:

<...>

Home Data Page Version Summary

Version Count

0 (oldest) 25

1 (current) 15

<...>

In the above example there are 25 pages to be altered to a new version. That means that a dummy update is required to be executed against that table. The steps to perform a dummy update is explained in detail within the migration guide:

http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.mig.doc/ids_mig_274.htm

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

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

Informix Dynamic Server and Linux -- Up and running (0)	2018.11.21
informix server中经常看到memory线程，它是做什么用的？ (0)	2018.10.12
Identifying and resolving outstanding in-place alters in version 11 and later. (0)	2018.06.28
Identifying and resolving outstanding or incomplete in-place alters in versions 7 to 10 (0)	2018.06.28
백업 오류 재현 테스트 (0)	2018.04.06

Technote (FAQ)

Question

How do you identify outstanding in-place alters in order to resolve them?

These are sometimes call incomplete, or "unrealised" in-place alters.

This document is specifically for IDS version 11 and later. For IDS versions 7 to 10,
see http://www-01.ibm.com/support/docview.wss?uid=swg21226410

Cause

Since version 7.24, some ALTER TABLE statements have been done as "fast alters", where rows are only altered to fit the new schema when the data page they are on is next written to. If there are many alters done, this can lead to the data pages for a table being a variety of versions. When migrating to a later version (or just for the performance benefit) it is a good idea to "complete" the outstanding in-place alters.

Answer

The first step is to work out which tables have data pages at old versions:

There are several documented ways to determine altered tables, including:

The migration guide has SQL to identify all tables with in place alters (chapter 3, page 52). However, some versions of the manual have SQL like this:
which shows all tables that ever had an in-place or fast alter, and does not exclude those even where every data page has been rewritten (and therefore updated) since the ALTER TABLE was run.

The oncheck -pT utility prints a table showing all current versions on data pages, and page counts for each version, but it runs serially, locking the whole table, and takes a long while to run because it reports on many other things as well.

As these methods have drawbacks, the following steps can be used to obtain a more specific list of outstanding in-place alters:

1. Set OPTCOMPIND to 0 in the onCONFIG configuration file and restart the instance.

2. Set isolation to dirty read.

3. Find every partnum in the tblspace tbspace of each non-temp dbspace where pg_next (pointer to extended partition page) is set to something other than zero. These tablespaces have been altered, and pg_next points to the pnvers (partition version) page.

select (p.pg_partnum + p.pg_pagenum - 1) as partn, a.name as dbspace
from sysmaster:syspaghdr p, sysmaster:sysdbspaces a
where p.pg_partnum = 1048576 * a.dbsnum + 1
and a.is_temp = 0 and a.is_blobspace = 0 and a.is_sbspace = 0
and p.pg_next != 0
into temp altpts with no log ;

4. Get the database and table names, minimum and maximum possible page versions, plus row and data page numbers for all those partnums.

select b.dbsname database, b.tabname table, a.dbspace dbspace,
hex(a.partn) partnum, i.ti_nrows nrows, i.ti_npdata datapages,
p.pta_oldvers as oldvers, p.pta_newvers as newvers,
-1 as mindver, 999 as maxdver
from altpts a, outer sysmaster:sysactptnhdr p,
outer sysmaster:systabnames b, outer sysmaster:systabinfo i
where a.partn = b.partnum
and a.partn = i.ti_partnum
and a.partn = p.partnum
into temp tabvers with no log ;

5. Scan all non-empty home data pages for each altered tablespace and get the
minimum and maximum versions:

update tabvers set mindver =
( select (min(pg_next)) / 16777216
from sysmaster:syspaghdr p, altpts a
where p.pg_partnum = a.partn
and tabvers.partnum = a.partn
and sysmaster:bitval(pg_flags, '0x1') = 1
and sysmaster:bitval(pg_flags, '0x8') <> 1
and pg_frcnt < (2020 - 4*pg_nslots)
)
where 1 = 1 ;

update tabvers set maxdver =
( select (max(pg_next)) / 16777216
from sysmaster:syspaghdr p, altpts a
where p.pg_partnum = a.partn
and tabvers.partnum = a.partn
and sysmaster:bitval(pg_flags, '0x1') = 1
and sysmaster:bitval(pg_flags, '0x8') <> 1
and pg_frcnt < (2020 - 4*pg_nslots)
)
where 1 = 1 ;

6. Display the tablespaces which require updating to complete the in-place alters:

select database, table, dbspace, nrows, datapages,
oldvers as oldestv, mindver, maxdver, newvers as newestv
from tabvers
where newvers > mindver
order by 1, 2;

The second step is to do a dummy update on at least one row on each of those pages not updated to the current version.

For all tables which have been altered, but are not yet "complete" , you can run a "dummy update" statement, setting the primary key column to itself (or other "change" that changes nothing). This will bring all of the pages to the most current version:

update TABNAME set PKEY = PKEY where 1 = 1 ;

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

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

informix server中经常看到memory线程，它是做什么用的？ (0)	2018.10.12
Identifying outstanding in-place alters (0)	2018.06.28
Identifying and resolving outstanding or incomplete in-place alters in versions 7 to 10 (0)	2018.06.28
백업 오류 재현 테스트 (0)	2018.04.06
SUBSTR, LPAD 등을 활용한 정규식 패턴 변경 방법 (0)	2018.02.22

Technote (FAQ)

Question

How do you identify outstanding in-place alters in order to resolve them?

This document is specifically for IDS versions 7 to 10. For IDS version 11 and later,
see http://www-01.ibm.com/support/docview.wss?uid=swg21389693

Cause

Since version 7.24, some ALTER TABLE statements have been done as "fast alters", where rows are only altered to fit the new schema when the data page they are on is next written to. If there are many alters done, this can lead to the data pages for a table being a variety of versions. When migrating to a later version (or just for the performance benefit) it is a good idea to "complete" the outstanding in-place alters.

Answer

The first step is to work out which tables have data pages at old versions:

There are several documented ways to determine altered tables, including:

The 9.4 migration guide has SQL to identify all tables with in place alters (chapter 5, page 5):
first set OPTCOMPIND to 0 in onconfig and restart instance set isolation to dirty read; select pg_partnum + pg_pagenum - 1 partn from sysmaster:syspaghdr, sysmaster:sysdbspaces a where pg_partnum = 1048576 * a.dbsnum + 1 and pg_next != 0 into temp pp with no log ; select b.dbsname database, b.tabname table from sysmaster:systabnames b, pp where partn = partnum ;
Unfortunately, this shows all tables that ever had an in-place or fast alter, and does not exclude those even where every data page has been rewritten (and therefore updated) since the ALTER TABLE was run.
The oncheck -pT utility prints a table showing all current versions on data pages, and page counts for each version, but it runs serially, locks the whole table, takes a long while to run, and reports on many other things as well.

This SQL will report the minimum and maximum version page version for a table (or by default, all tables) with incomplete alters. Dirty reads mean no locks, and it reports which dbspaces are involved too. It runs faster than oncheck -pT.

As these methods have drawbacks, the following steps can be used to obtain a more specific list of outstanding in-place alters:

1. Set OPTCOMPIND to 0 in the onCONFIG configuration file and restart the instance.

2. Set isolation to dirty read.

3. Find every partnum in the tblspace tbspace of each non-temp dbspace where pg_next (pointer to extended partition page) is set to something other than zero. These tablespaces have been altered, and pg_next points to the pnvers (partition version) page.

select (p.pg_partnum + p.pg_pagenum - 1) as partn, a.name as dbspace
from sysmaster:syspaghdr p, sysmaster:sysdbspaces a
where p.pg_partnum = 1048576 * a.dbsnum + 1
-- Tblspace tblspace partnums have format 0xnnn00001
and a.is_temp = 0 and a.is_blobspace = 0 and a.is_sbspace = 0
-- exclude tempspaces, blobspaces and smart blob spaces
and p.pg_next != 0
-- non zero = altered
into temp altpts with no log ;

4. Get the database and table names, plus row and data page numbers for all those partnums.

select b.dbsname database, b.tabname table,
a.dbspace dbspace, hex(a.partn) partnum,
i.ti_nrows nrows, i.ti_npdata datapages,
-1 as minver, 999 as maxver
from altpts a, outer sysmaster:systabnames b,
outer sysmaster:systabinfo i
where a.partn = b.partnum and a.partn = i.ti_partnum
into temp tabvers with no log ;

5. Scan all non-empty home data pages for each altered tablespace and get
the minimum and maximum versions:

update tabvers set minver = (
select (min(pg_next)) / 16777216
from sysmaster:syspaghdr p, altpts a
where p.pg_partnum = a.partn
and tabvers.partnum = a.partn
and sysmaster:bitval(pg_flags, '0x1') = 1 -- data page
and sysmaster:bitval(pg_flags, '0x8') <> 1 -- not remainder page
and pg_frcnt < (2020 - 4*pg_nslots) -- with some data on it.
) where 1 = 1 ;

update tabvers set maxver = ( select (max(pg_next)) / 16777216
from sysmaster:syspaghdr p, altpts a
where p.pg_partnum = a.partn
and tabvers.partnum = a.partn
and sysmaster:bitval(pg_flags, '0x1') = 1
and sysmaster:bitval(pg_flags, '0x8') <> 1
and pg_frcnt < (2020 - 4*pg_nslots)
) where 1 = 1 ;

Note the use of 2020 above is based on a dbspace with a 2KB pagesize, less the 24 byte header and 4 byte timestamp. For a 4KB pagesize use 4096 - 28 = 4078, for 8KB pagesize use 8192 - 28 = 8164, and so on.

6. Display the tablespaces which require updating to complete the in-place alters:

select database, table, dbspace, nrows, datapages, minver, maxver,
decode(maxver - minver, 0, "Maybe Needs Update", "Definitely Needs Update")
from tabvers
order by 1, 2;

The output of this SQL provides a list of which tablespaces (tables or fragments of tables) where in-place alters exist. If there are some data pages at a lower version from other data pages, then a dummy update is definitely required. If minver = maxver, then if there was an alter table statement run and no data pages have been written to since then (i.e. no inserts, updates, or deletes), then a dummy update may still be required. Prior to IDS version 11, it is not possible to determine from the sysmaster database what the potential maximum version of a data page is for a table. In version 11 there is a new sysmaster table called sysactptnhdr that does give this information.

The second step is to do a dummy update on at least one row on each of those pages not updated to the current version.

For all tables which have been altered, but are not yet "complete" , you can run a "dummy update" statement, setting the primary key column to itself (or other "change" that changes nothing). This will bring all of the pages to the most current version:

update TABNAME set PKEY = PKEY where 1 = 1 ;

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

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

Identifying outstanding in-place alters (0)	2018.06.28
Identifying and resolving outstanding in-place alters in version 11 and later. (0)	2018.06.28
백업 오류 재현 테스트 (0)	2018.04.06
SUBSTR, LPAD 등을 활용한 정규식 패턴 변경 방법 (0)	2018.02.22
정규식 패턴 변경 함수 REGEX_REPLACE (0)	2018.02.21

인포믹스 데이터 파일 내용을 조작하여 백업 테스트를 수행해보았습니다.

## 테스트 환경

1. 테스트서버 : AIX 6.1

2. DBSPACE 현황 (청크개수) : rootdbs (1), datadbs (3)

3. onstat -d 결과

IBM Informix Dynamic Server Version 7.31.UD9 -- Quiescent -- Up 00:29:45 -- 18112 Kbytes

Dbspaces

address number flags fchunk nchunks flags owner name

4005a150 1 1 1 1 N informix rootdbs

4005a888 2 1 2 1 N informix plogdbs

4005a948 3 1 3 1 N informix llogdbs

4005aa08 4 1 4 3 N informix datadbs

4 active, 2047 maximum

Chunks

address chk/dbs offset size free bpages flags pathname

4005a210 1 1 0 5000 3009 PO- /work2/INFORMIX/ids731/dbspaces/rootdbs

4005a388 2 2 0 250000 249947 PO- /work2/INFORMIX/ids731/dbspaces/plogdbs

4005a488 3 3 0 250000 249707 PO- /work2/INFORMIX/ids731/dbspaces/llogdbs

4005a588 4 4 0 250000 0 PO- /work2/INFORMIX/ids731/dbspaces/datadbs

4005a688 5 4 0 25000 0 PO- /work2/INFORMIX/ids731/dbspaces/datadbs1

4005a788 6 4 0 25000 0 PO- /work2/INFORMIX/ids731/dbspaces/datadbs2

6 active, 2047 maximum

## 테스트 절차

1. 데이터 파일 조작 (6번 청크 파일)

dd if=/dev/random seek=10 bs=1000 count=10 of=/work2/INFORMIX/ids731/dbspaces/datadbs2 conv=notrunc

2. ontape 풀 백업

ontape -s -L 0

3. 백업 후 online.log 파일 내용 확인

17:37:39 Assert Failed: Archive detects that page 0x600003 is corrupt.

17:37:39 IBM Informix Dynamic Server Version 7.31.UD9

17:37:39 Who: Session(23, informix@pilma01, 23134372, 1074292452)

Thread(60, arcbackup1, 4005e334, 1)

File: rsarcbu.c Line: 2600

17:37:39 stack trace for pid 23396402 written to /tmp/af.424e052

17:37:39 See Also: /tmp/af.424e052

17:37:39 Archive on rootdbs, datadbs Completed with 2 corrupted pages detected.

백업중 발생한 오류 메시지에서 나타난 페이지 번호(0x600003)는 DBSPace 번호가 아닌 Chunk 번호를 의미하는 것으로 보입니다.

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

Identifying and resolving outstanding in-place alters in version 11 and later. (0)	2018.06.28
Identifying and resolving outstanding or incomplete in-place alters in versions 7 to 10 (0)	2018.06.28
SUBSTR, LPAD 등을 활용한 정규식 패턴 변경 방법 (0)	2018.02.22
정규식 패턴 변경 함수 REGEX_REPLACE (0)	2018.02.21
LOB 타입 데이터 크기 계산 (0)	2017.08.22

안녕하세요. 바로 아래 글에서 인포믹스에서 정규식 패턴 변경 함수인 REGEX_REPLACE 를 테스트했습니다.

대신 REGEX_REPLACE 함수는 인포믹스 12버전에서만 지원되기에 질문자 분이 이하 버전에서 가능한 방법을 다시 질문하셨더군요.

마농님, 정상규님 댓글 감사합니다.

마농님이 올려주신 오라클에서의 쿼리는 적용이 되는데.

인포믹스에서는 12버전이 아니라 적용이 안되네요.

regex_replace 를 사용하지 않고 인포믹스에서도 가능할까요??

해당 쿼리를 다시 데이터베이스 사랑넷의 '마농'님께서 기본적인 문자열 함수를 사용해서 쿼리를 재작성하신 것으로 테스트해봤습니다.

오라클 기본적인 문자함수들로만 구성해 봤습니다.
인포믹스에 맞게 고쳐 보세요.

WITH t AS
(
SELECT '문자001' v FROM dual
UNION ALL SELECT '문자열01'    FROM dual
UNION ALL SELECT '01문자001'   FROM dual
UNION ALL SELECT '121문자열01' FROM dual
UNION ALL SELECT '문자002'     FROM dual
UNION ALL SELECT '문자003'     FROM dual
UNION ALL SELECT '문자1'       FROM dual
UNION ALL SELECT '문자'        FROM dual
UNION ALL SELECT '문자11A03'   FROM dual
)
SELECT v
     , SUBSTR(v, 1, x-y) || LPAD(SUBSTR(v, x-y+1, 4), 4, '0') z
FROM (SELECT v
             , x
             , CASE WHEN SUBSTR(v, x-0, 1) BETWEEN '0' AND '9' THEN 1
             + CASE WHEN SUBSTR(v, x-1, 1) BETWEEN '0' AND '9' THEN 1
             + CASE WHEN SUBSTR(v, x-2, 1) BETWEEN '0' AND '9' THEN 1
             + CASE WHEN SUBSTR(v, x-3, 1) BETWEEN '0' AND '9' THEN 1
               ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END AS y
          FROM (SELECT v, LENGTH(v) x FROM t) a
        ) a
;

인포믹스 11버전에서 그대로 사용해도 잘 수행되었습니다.

참고로 WITH문은 인포믹스에서 사용할 수 없습니다.

정말 멋지네요. 알려주신 '마농'님께 감사한 마음이 드는군요.

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

Identifying and resolving outstanding or incomplete in-place alters in versions 7 to 10 (0)	2018.06.28
백업 오류 재현 테스트 (0)	2018.04.06
정규식 패턴 변경 함수 REGEX_REPLACE (0)	2018.02.21
LOB 타입 데이터 크기 계산 (0)	2017.08.22
인포믹스 Growth or Workgroup 에디션의 병렬 , 파티션 기능 활성화 (0)	2017.02.15

안녕하세요. 인포믹스에는 조건절에서 정규식을 사용해서 검색할 수 있는 MATCHES 키워드가 있습니다.

정규식으로 검색한 패턴을 변경하는 오라클의 REGEXP_REPLACE에 해당하는 함수는 12버전부터 지원합니다.

데이터베이스 사랑넷에서 패턴 변경에 대한 문의 내용을 발견하여 인포믹스 환경에서 테스트 해봤습니다.

아래는 질문하신 분이 올리신 글 내용입니다.

컬럼 데이터가

'문자001'

'문자열01'

'01문자001'

'121문자열01'

'문자002'

'문자003'

이런식으로... 복합접으로 들어가 있는 컬럼이 있는데요..

이것을

뒤에 001이라고 되어있는 숫자만.. 0001 로..자릿수를 맞춰야 합니다

그래서 결과가

'문자0001'

문자열0001'

01문자0001'

121문자열0001'

'문자0002'

'문자0003'

이렇게 뒷자리 숫자들이 공통적으로 4자리로 표현되어야 하는데요..

제생각으로는 문자랑 숫자(뒤쪽에 일련된 숫자)를 분리한후 뒤쪽 숫자를 0000으로 lpad해야 할거 같은데.

이게 오라클이랑 인포믹스에서 가능한가요??

출처 : 데이터베이스 사랑넷(http://database.sarang.net/?criteria=oracle&subcrit=qna&inc=read&aid=41526)

데이터베이스 사랑넷의 고수님인 '마농'님께서 답글로 아래 쿼리문을 올려주신 덕분에 쉽게 테스트 할 수 있었네요.

WITH t AS
(
SELECT '문자001' v FROM dual
UNION ALL SELECT '문자열01'    FROM dual
UNION ALL SELECT '01문자001'   FROM dual
UNION ALL SELECT '121문자열01' FROM dual
UNION ALL SELECT '문자002'     FROM dual
UNION ALL SELECT '문자003'     FROM dual
)
-- Oracle 정규식 --
SELECT v
     , REGEXP_REPLACE(REGEXP_REPLACE(v
       , '([0-9]+)$', '000\1')
       , '0+([0-9]{4})$', '\1')
       AS x
  FROM t
;

dbaccess에서 아래 내용을 수행해봤습니다. 수행 결과를 보기 좋게 하기 위해서 varchar(20)으로 형변환했습니다.

-- temp table 생성 및 데이터 입력 --
create temp table t (v varchar(20));
insert into t values ('문자001');
insert into t values ('문자열01');
insert into t values ('01문자001');
insert into t values ('121문자열01');
insert into t values ('문자002');
insert into t values ('문자003');
-- REGEX_REPLACE 함수 사용 결과 --
SELECT v::varchar(20)
       AS v
       , REGEX_REPLACE(REGEX_REPLACE(v
       , '([0-9]+)$', '000\1')
       , '0+([0-9]{4})$', '\1')::varchar(20)
       AS x
  FROM t
;
v                    x
문자001              문자0001
문자열01             문자열0001
01문자001            01문자0001
121문자열01          121문자열0001
문자002              문자0002
문자003              문자0003
6 row(s) retrieved.

REGEX_REPLACE는 인포믹스의 extension 기능으로 처음 호출할 때 해당 함수가 설치됩니다.

외장 함수의 형태로 생성되며, 설치되지 않은 상태에서 실행하면 자동으로 설치되는 것 같습니다.

인포믹스 메시지 로그와 데이터베이스 스키마에서 관련 내용을 확인 할 수 있습니다.

$ onstat -m

...

15:39:53 Logical Log 420 Complete, timestamp: 0xe75412.
15:40:09 INFO (autoregexe 13) (EXECUTE FUNCTION sysbldprepare ('ifxregex.*', 'create')) << 외장 함수가 설치되었음을 알림

$ dbschema -d kscdb -f all

...

create function "informix".regex_replace (lvarchar,lvarchar,lvarchar,integer default null,integer default null,integer default null)
returns lvarchar
with (parallelizable, handlesnulls, not variant, percall_cost=1000)
external name '$INFORMIXDIR/extend/ifxregex.1.00/ifxregex.bld(ifxregex_replace_char)' language c;

참고하셔서 사용하시면 유용할 것 같습니다.

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

백업 오류 재현 테스트 (0)	2018.04.06
SUBSTR, LPAD 등을 활용한 정규식 패턴 변경 방법 (0)	2018.02.22
LOB 타입 데이터 크기 계산 (0)	2017.08.22
인포믹스 Growth or Workgroup 에디션의 병렬 , 파티션 기능 활성화 (0)	2017.02.15
단일 쿼리로 테이블 카운트하기 (0)	2017.02.08

안녕하세요. 인포믹스 LOB(Large Object) 타입의 데이터 크기를 계산하는 방법을 테스트해봤습니다.

LOB 타입 데이터를 처리할 수 있는 함수가 없는 것으로 알고 있어서, 아래와 같이 blademgr의 번들된 모듈을 설치해서 사용했습니다.

1. 먼저 테스트용 데이터베이스 stores_demo를 아래와 같이 만듭니다.

$ dbaccessdemo -log -dbspace datadbs1

2. blademgr에서 LOB 타입 데이터를 처리하기 위한 모듈을 아래와 같이 설치합니다.

$ blademgr

ol_informix1210_1>show databases

Databases on server:

stores_demo

sysadmin

sysuser

ol_informix1210_1>list stores_demo

DataBlade modules registered in database stores_demo:

TimeSeries.6.00.FC7 ifxrltree.2.00

spatial.8.22.FC2

ol_informix1210_1>show modules

16 DataBlade modules installed on server ol_informix1210_1:

LLD.1.20.FC2 Node.2.0 c

TSAFuncs.1.00.FC1 TSPIndex.1.00.FC1

TSPMatch.2.00.FC1 TimeSeries.6.00.FC7

binaryudt.1.0 bts.3.10

excompat.1.0 ifxbuiltins.1.1

ifxregex.1.00 ifxrltree.2.00

mqblade.2.0 spatial.8.22.FC2

sts.2.00.FC1 wfs.1.00.FC1

A 'c' indicates DataBlade module has client files.

If a module does not show up, check the prepare log.

ol_informix1210_1>register excompat.1.0 stores_demo

Register module excompat.1.0 into database stores_demo? [Y/n]y

Registering DataBlade module... (may take a while).

DataBlade excompat.1.0 was successfully registered in database stores_demo.

3. 위와 같은 절차로 설치하고 나면 dbms 관련 함수와 프로시저가 생성된 것을 확인할 수 있습니다.

$ dbschema -d stores_demo -f all | egrep 'create function.*dbms_|create procedure.*dbms'

create function "informix".dbms_lob_getlength (blob)

create function "informix".dbms_lob_getlength (clob)

create function "informix".dbms_lob_compare (blob,blob,integer default 2147483647,integer default 1,integer default 1)

create function "informix".dbms_lob_substr (clob,integer default 32767,integer default 1)

create function "informix".dbms_lob_instr (clob,lvarchar,integer default 1,integer default 1)

create function "informix".dbms_lob_new_clob (lvarchar)

create function "informix".dbms_lob_compare (clob,clob,integer default 2147483647,integer default 1,integer default 1)

create function "informix".dbms_random_jrand48 (inout bigint)

create procedure "informix".dbms_output_enable (integer default 20000)

create procedure "informix".dbms_output_disable ()

create procedure "informix".dbms_output_put (lvarchar)

...

4. 인포믹스 데모 프로그램의 샘플 이미지 파일을 입력하기전에 사이즈를 확인합니다.

$ ls -l demo/esqlc/cn*.gif

-rw-r--r-- 1 informix informix 15736 Apr 19 18:08 demo/esqlc/cn_10001.gif

-rw-r--r-- 1 informix informix 21454 Apr 19 18:08 demo/esqlc/cn_10027.gif

-rw-r--r-- 1 informix informix 11751 Apr 19 18:08 demo/esqlc/cn_10031.gif

-rw-r--r-- 1 informix informix 25610 Apr 19 18:08 demo/esqlc/cn_10046.gif

-rw-r--r-- 1 informix informix 7937 Apr 19 18:08 demo/esqlc/cn_10049.gif

5. 입력할 테이블을 만들고 LOB 데이터 크기를 계산합니다.

$ dbaccess stores_demo -

> create table test (filename varchar(30), img blob);

> insert into test values ('cn_10001.gif', filetoblob('./demo/esqlc/cn_10001.gif','client'));

> insert into test values ('cn_10027.gif', filetoblob('./demo/esqlc/cn_10027.gif','client'));

> insert into test values ('cn_10031.gif', filetoblob('./demo/esqlc/cn_10031.gif','client'));

> insert into test values ('cn_10046.gif', filetoblob('./demo/esqlc/cn_10046.gif','client'));

> insert into test values ('cn_10049.gif', filetoblob('./demo/esqlc/cn_10049.gif','client'));

> select filename, dbms_lob_getlength (img) from test;

filename (ｅxpression)

cn_10001.gif 15736

cn_10027.gif 21454

cn_10031.gif 11751

cn_10046.gif 25610

cn_10049.gif 7937

5 row(s) retrieved.

Knowledge Center를 보면 DBMS_LOB_LENGTH라고 문서화가 되어있지 않아 혼동이 있을 것 같네요.

참고가 되시기를 바랍니다.

** 참고

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

https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.dbext.doc/ids_r0055123.htm

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

SUBSTR, LPAD 등을 활용한 정규식 패턴 변경 방법 (0)	2018.02.22
정규식 패턴 변경 함수 REGEX_REPLACE (0)	2018.02.21
인포믹스 Growth or Workgroup 에디션의 병렬 , 파티션 기능 활성화 (0)	2017.02.15
단일 쿼리로 테이블 카운트하기 (0)	2017.02.08
여러 열의 데이터를 한 행으로 표시하기 (0)	2016.09.08

Problem(Abstract)

You are trying to run an ALTER TABLE command on a table but you see these errors:

-242 SQL error: Could not open database table
-106 ISAM error: non-exclusive access

There are no locks on the table. This is happening although you possibly have one or both of these set:

SET LOCK MODE TO WAIT
LOCK TABLE tab1 IN EXCLUSIVE MODE

Cause

You do not have exclusive access to the table. This means there is a lock on the table or there is a select running on the table. An ALTER TABLE operation has a condition that only the session running the ALTER TABLE is accessing the table or it will fail immediately. Having LOCK MODE set to WAIT or having an exclusive lock on the table will not change the behavior.

Diagnosing the problem

1. Determine the partnum or tblsnum of the table you are trying to alter. Run this select in the database where your table resides:

select hex(partnum) from systables where tabname = "your_table_name"

For this example the value returned is 0x00100045

2. Look for a lock on the table. You will search for table's partnum in the onstat -k output. The onstat -k output does not display the 0x or leading 0. This means 0x00100045 in the onstat -k output would look like 100045 Run this command:

onstat -k | grep 100045

If nothing is returned, then there are no locks on the table.

3. Check to see if the table is open ( most likely has one or more selects running on it ). Run this command:

onstat -g opn | grep 0x00100045

If nothing is returned, then the table is not being accessed and you can run your ALTER TABLE command. If you do find that the table is open, then proceed to Resolving the problem.

Resolving the problem

If you are running ALTER FRAGMENT on TABLE then starting in v11.50 and above you can set FORCE_DDL_EXEC to on in your environment then rerun the ALTER FRAGMENT. Here are a couple links that provide detailed description of FORCE_DDL_EXEC:

FORCE_DDL_EXEC Environment Option
Forcing out transactions when altering table fragments
Wait for users to leave the table then rerun your ALTER TABLE. You can run LOCK TABLE tab1 IN EXCLUSIVE MODE so new users cannot lock the table. Users can access the exclusively locked table if their isolation level is set to dirty read. A dirty read select counts as a user accessing the table and will cause the ALTER TABLE to fail.
Run onstat -g sql 0. This will give you a list of all the sql running on the database server. You can find all sqls running on the table. The output will also give you the session id. You can then run onmode -z session_id on each session accessing the table. onmode -z will destroy an active session so use it with caution. Run the ALTER TABLE.
Run this select statement in sysmaster database to get a list of all sessions with an sql referencing your table:

select scs_sessionid from syssqlcurses where scs_sqlstatement like "%your_table%"

The first row returned is the session id for the session running this select. You can then run onmode -z on the sessions accessing the table. onmode -z will destroy an active session so use it with caution.
Set IFX_DIRTY_WAIT. For details click this link:
http://www.ibm.com/support/docview.wss?rs=630&uid=swg21174239
Restart the database server and run the ALTER TABLE immediately.

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

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

How to setup or enable tracing on a Checkpoint (0)	2018.08.09
깨진 문자열 UNLOAD 하기위한 설정 (0)	2018.08.01
Collect information when a recovery failure occurs with Informix Dynamic Server (0)	2015.09.08
인포믹스 12.10 설치시 오류 (Windows 2012 R2) (0)	2015.08.05
Errors 244 and 197 when running a write sql on a table (0)	2015.07.16

Informix

Technote (FAQ)

Question

Answer

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

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

Technote (FAQ)

Question

Cause

Answer

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

Technote (FAQ)

Question

Cause

Answer

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

Technote (FAQ)

Question

Cause

Answer

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

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

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

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

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

Problem(Abstract)

Cause

Diagnosing the problem

Resolving the problem

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

티스토리툴바