pajama

AIX용 Informix C-ISAM을 기본 설치 디렉토리인 /usr에 설치할 때 발생하는 오류입니다.

스크립트상 오류인지 /usr 디렉토리에 라이브러리를 설치하지 못하도록 한 것인지는 정확히 모르겠습니다만 

다른 디렉토리로는 잘 설치됩니다.

현재까지 확인한 결과로는 7.26.FC2, 7.26.FC3 버전에서 문제가 있었습니다.

./installisam (설치 스크립트 실행)

....

You may specify a target location for these files other than "/usr"

at this time if you so desire.  Do you wish to change the target

location for the directories?

Enter Y to change target directory or RETURN to continue.

(여기서 Y 옵션을 선택하면 디렉토리를 지정하여 설치할 수 있습니다. 엔터를 입력하면 /usr에 설치합니다.)

The default target directory of /usr will be used.

Moving Binaries to /usr/bin

./installisam[190]: test: 0403-004 Specify a parameter with this command.

./installisam[194]: test: 0403-004 Specify a parameter with this command.

Making

Usage: mkdir [-p] [-m mode] Directory ...

Usage: chmod [-R] [-f] [-h] {u|g|o|a ...} {+|-|=} {r|w|x|X|s|t ...} File ...

        chmod [-R] [-f] [-h] OctalNumber File ...

        Changes the permission codes for files or directories.

Moving Libraries to

Usage: mv [-I] [-i | -f] [-E{force|ignore|warn}] [--] src target

   or: mv [-I] [-i | -f] [-E{force|ignore|warn}] [--] src1 ... srcN directory

chmod: *lib*isam*.*: A file or directory in the path name does not exist.

chmod: llib-lisam: A file or directory in the path name does not exist.

chmod: *libifisam*.s*: A file or directory in the path name does not exist.

ar: 0707-100 /*lib*isam*.a does not exist.

ranlib: 0654-601 Execution of ar failed

Usage: ranlib [-t] [-X {32|64|32_64}] [--] file ...

ar: 0707-100 /libisamnls.a does not exist.

ranlib: 0654-601 Execution of ar failed

Usage: ranlib [-t] [-X {32|64|32_64}] [--] file ...

Moving Headers to /usr/include

Moving Localization files to /usr/gls

C-ISAM Installation Complete.

installisam 스크립트를 살펴보면 라이브러리 경로가 지정되어 있지 않습니다.

BIN='bcheck dblog glfiles'

BINDIR='/usr/bin'

LIB='*lib*isam*.* llib-lisam'

SHLIB='*libifisam*.s*'

LIBDIR=''

INCL='isam.h decimal.h'

INCLDIR='/usr/include'

GLS='gls'

GLSDIR='/usr'

기본적으로 Informix C-ISAM을 설치하면 다음 네 개의 디렉토리에 파일이 설치가 됩니다.

bin

lib

include

gls

설치시 지정한 디렉토리가 만약에 /home/informix/cisam 이라고 한다면 각각 다음 위치에 설치 될 것입니다.

/home/informix/cisam/bin

/home/informix/cisam/lib

/home/informix/cisam/include

/home/informix/cisam/gls

해결책 ① 설치 스크립트인 installisam을 다음과 같이 수정하는 방법이 있습니다.

LIBDIR='' → LIBDIR='/usr/lib'

해결책 ② 설치 수행 중에 설치 디렉토리를 /usr로 지정하는 방법이 있습니다.

You may specify a target location for these files other than "/usr"

at this time if you so desire.  Do you wish to change the target

location for the directories?

Enter Y to change target directory or RETURN to continue.

Y (디렉토리를 변경하려면 Y를 입력합니다.)

Enter new target directory.

/usr (설치할 디렉토리 위치 /usr을 지정합니다.)

You have selected /usr as the new target directory.

If this is correct enter Y.

Y (최종 확인합니다.)

Moving Binaries to /usr/bin

Moving Libraries to /usr/lib

Moving Headers to /usr/include

Moving Localization files to /usr/gls

C-ISAM Installation Complete.

③ 라이브러리 파일을 직접 복사합니다.

압축을 해제하면 다음 파일이 있습니다. 직접 /usr/lib 디렉토리에 복사,이동해도 됩니다.

libifisam.a

libifisam.so

libifisamx.so

libisam.a

libisamnls.a

llib-lisam

내용을 가져가실때는 출처를 명시해주시기 바랍니다.

Problem(Abstract)

KAIO is not enabled when chunks are created on raw devices. OS and related environment are no problems.

Cause

Resolving the problem

http://www-01.ibm.com/support/docview.wss?uid=swg21646086&myns=swgimgmt&mynp=OCSSGU8G&mync=E

DB2에는 복구 히스토리 파일(db2rhist.asc)이 있는데 주로 테이블스페이스 생성, 삭제, 로그 아카이빙 등과 같은 유틸리티성 작업이 기록된다. 이 파일이 일정 크기 이상으로 커지면 Commit이 완료되는데 오래 걸리는 현상이 발생한다. 

아래 포스트를 보면 DPF환경에서 히스토리 파일이 약 240MB인 경우에 트랜잭션 행(hang)현상이 발생했다고 한다.

The history file in DB2 is a critical file that is usually overlooked.

Earlier this week I work with a customer that had been experiencing hangs on certain partitions (Yes this is DPF) daily since upgrading to a later fixpack, and intermittently on the earlier fixpack.

We found that DB2 was stuck in uninteruptable kernel code. As this was Linux, the usual tools to get a kernel trace were not available. Looking at the db2support provided by the customer, I saw that the db2rhist.asc (aka history file) was > 240MB.

The customer was advised to prune the history file, so that it only contained 1o days of history. This resulted in the db2rhist.asc only being 8 MB (which is still reasonable big). Since the pruning of the history file no further hangs have been experienced to date.

There are numerous cases where a large history file causes a slow down and even hang like symptoms. In a majority of cases where there is high load activity the history file can grow quickly and there are DB2 internal options so that Loads are not logged in the history file - see DB2 support for details.

It is good practice to keep the history file as small as possible.

좀 심한 경우에는 600MB까지 늘어난 경우도 있다.

Dear all,
I wrote some weeks ago about a problem with all the committing transactions blocked for many seconds in a Commit Active state. Well, finally I solved it and I just would like to share this information.

 As I suspected there was a lock. I suspected log writing due to thecommit active state and I was right. Using truss on the db2loggw process I found that when the db2 was blocked it was waiting on a Unix semaphore. I didn't find a way to find on AIX a command that gives you the pid of the semaphore locking process (you have the last operation process pid, that was the db2loggw itself). This semaphore lock appear just after a statx on
the db2rhist.asc file, so I tried to understand what was happening with that file.

Oh well, the file was pretty big: 600MB. I suspected the db2logmgr and infact it was accessing the file during the lock. once again, truss helped me. It was reading/seeking into the file... when it finished it did a semop on the same semaphore (an unlock obviously) and the db2loggw started to write again and everything was unblocked and working correctly.

I removed the db2rhist.asc and the db2rhist.bak and reduced the REC_HIS_RETENTN variable to 7 days (we keep backup for less than a week) while the default is 366...

Anyway I have some questions for any DB2 folk around here. First of all anything happened just when there was an asnapply or asncap committing transaction on the database (the replication processes). Second is that I do not understand while the db2logmgr must read so many data from the db2rhist.asc: is it responsible of the history rotation process or whatever?

Thanks a lot to everybody and bye.

Matteo Gelosa
I.NET S.p.A.

어쩄든 History FIle 에 액세스하는 동안 Commit이 느려진다는 점은 IBM문서에서 나와있으나, 어느정도 크기 이상이 되었을 때 성능에 문제가 있다던지 하는 기준은 없다. 가능하면 작게 유지하는 것이 좋다는 게 공식적인 입장이다.

다만 최근에 출시된 10.1 버전 Fixpack 2 부터는 History FIle이 커져도 성능에 영향을 주지 않도록 변경된 것으로 보인다.

http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.wn.doc/doc/c0060645.html

http://veryuglyboxer.blogspot.kr/2010/01/history-file.html

http://www.dbforums.com/db2/1652165-applicaiton-staying-long-time-commit-active-state.html

http://database.ittoolbox.com/groups/technical-functional/db2-l/application-in-commit-active-status-4428765

http://dbaspot.com/ibm-db2/195591-commit-active-problem-solved-db2rhist-asc.html

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

Problem(Abstract)

A very large db2rhist.asc file can cause performance degradation to DML that modifies tables.

Symptom

Cause

Diagnosing the problem

Resolving the problem

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

http://dbaspot.com/ibm-db2/195591-commit-active-problem-solved-db2rhist-asc.html

Problem(Abstract)

The following query is to identify the 10 slowest SQL queries from syssqltrace. 

Note : SQLTRACE in $ONCONFIG need to be enabled in order to trace the SQL Queries.

Resolving the problem

* SP_AUTOEXPAND 가 0일때 (onstat -d에서 DBSPACE 다섯번쨰 플래그 없음)

1. chunk extendable (onstat -d에서 청크 다섯번째 플래그 'E') 상태이더라도 자동으로 청크가 확장되지 않음

2. 수동으로 chunk extend 명령을 수행할 수 있음

e.g. execute function task("modify chunk extend",4,"10000");

* SP_AUTOEXPAND 가 1일때,(onstat -d에서 DBSPACE 다섯번쨰 플래그 'A') 

1. chunk extendable (onstat -d에서 청크 다섯번째 플래그 'E') 상태일 때 자동으로 청크가 확장됨

2. 기본적으로 sysmater:sysdbstab의 extend_size 값에 따라 증가하는 것으로 보임 (기본값 create_size 10, extend_size 10000)

3. 대체로 기본값인 10MB 씩 증가하지만 꼭 10MB씩 일정하게 증가하는 것은 아님,

extend_size를 10000으로 설정하고 100MB 데이터를 지속적으로 로드했을 때, 

10,000KB - 16,384KB - 32,768KB - 65,536KB 순으로 증가하는 크기가 늘어남

(다른 사이트의 예나 인포센터를 참조했을 때 create_size, extend_size는 최소 증가 사이즈.

더 크게 증가하는 경우는 내부적으로나 매뉴얼에 명시된 계산식이 있는 듯함)

http://www.ibm.com/developerworks/data/library/techarticle/dm-1103storageprovisioning/#expanding

https://www.ibm.com/developerworks/community/blogs/informix_admins_blog/entry/expandable_chunks_in_informix_11_701?lang=en

Question

You are using an IBM Informix Server. You want to determine the edition of product that you have installed.

Answer

http://www-01.ibm.com/support/docview.wss?uid=swg21642781&myns=swgimgmt&mynp=OCSSGU8G&mync=E

#lsps -a
Page Space  PV     VG      Size   %Used  Active  Auto  Type
hd6         hdisk0 rootvg  128MB   45    yes     yes   lv

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

Technote (FAQ)

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

데이터 정합성이 맞지않거나 통계정보 부정확등의 이유에 인해 dbexport로 추출된 데이터 건수가 실제 내려받은 데이터 건수와 다른 경우에 발생하는 오류 케이스입니다.

It might happen that the number of rows in the unload file does not match the number of rows in the
comment. In such cases the dbimport will fail.

dbimport often runs several hours and it is painful when it aborts after hours leaving a corrupted database.
Customers rely that a successful dbexport is usable for dbimport.
Example:
mstab00100.unl contains 5 rows instead of 3

(여기 예제에서는 실제로 데이터는 5건을 내려받았으나 메타정보로는 3건으로 표시하고 있습니다.)

{ TABLE "informix".mstab row size = 14 number of columns = 2
index size = 0 }
{ unload file name = mstab00100.unl number of rows = 3 }
create table "informix".mstab
  (
    id serial not null ,
    c1 char(10)
  );
revoke all on "informix".mstab from "public" as "informix";
Import data is corrupted! 

이 메시지는 실제로 데이터가 로케일이 다르거나 깨진 데이터로 인해 발생할 수도 있습니다.
또한 PK(Primary Key)에 의한 데이터 중복이 원인일 수 있습니다.
위의 결과는 데이터에 문제가 없더라도 데이터 건수 차이로 인한 오류메시지가 발생한 예 입니다.

Do you want to abort?y
This import is now invalid.
Calling dbimport with -c or continue although the number of rows is wrong is not an option

because the imported data might not be consistent.

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