728x90

db2에서 DECIMAL 타입을 CHAR 타입으로 변환할 때 9.7부터 출력형태가 바뀌었습니다.


다음은 db2 인포센터를 보면 CHAR 함수에 대한 주석입니다.


Decimal to character and leading zeros: In versions previous to version 9.7, the result for decimal input to this function includes leading zeros and a trailing decimal character. The database configuration parameter dec_to_char_fmt can be set to "V95" to have this function return the version 9.5 result for decimal input. The default value of dec_to_char_fmt for new databases is "NEW", which has this function return results which match the SQL standard casting rules and is consistent with results from the VARCHAR function.



DECIMAL 타입에 자리수를 지정하고 CHAR 타입으로 변환할 때 9.7 이전에는 숫자 앞뒤로 0을 채웠으나

9.7부터는 0을 채우지 않는 것이 기본 설정으로 변경되었습니다. 관련 파라미터는 DEC_TO_CHAR_FMT 입니다.


인포센터의 예제를 옮겨보겠습니다.


예 1
다음 테이블 및 데이터를 작성한다고 가정하십시오.
CREATE TABLE MY_TAB(C1 DEC(31,2)
INSERT INTO MY_TAB VALUES 0.20, 0.02, 1.20, 333.44
다음 명령문을 발행하는 경우
SELECT CHAR(C1)FROM MY_TAB
이전 릴리스에서는 다음 결과 세트가 리턴됩니다.
1--------------------------------- 
00000000000000000000000000000.20  
00000000000000000000000000000.02  
00000000000000000000000000001.20  
00000000000000000000000000333.44
버전 9.7에서는 다음 결과 세트가 리턴됩니다.
1---------------------------------
.20
.02
1.20
333.44
예 2
다음 테이블 및 데이터를 작성한다고 가정하십시오.
CREATE TABLE MY_TAB(C1 DEC(5,0))
INSERT INTO MY_TAB VALUES 1, 4.0  
SELECT CHAR(C1)FROM MY_TAB
다음 명령문을 발행하는 경우
SELECT CHAR(C1)FROM MY_TAB
이전 릴리스에서는 다음 결과 세트가 리턴됩니다.
1-----
0001.
0004.
버전 9.7에서는 다음 결과 세트가 리턴됩니다.
1-----
14




참고:


CHAR scalar function -

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000777.html


dec_to_char_fmt - 10진수를 문자 값으로 변환 함수 구성 매개변수 -

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.config.doc/doc/r0054719.html


CHAR(decimal-ｅxpression) 스칼라 함수 리턴 동작 변경 -

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

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

'Db2 > Db2 reference' 카테고리의 다른 글

TABLE, INDEX 생성시 PCTFREE 값 설정  (0) 2013.05.21
Moving data using the CURSOR file type  (0) 2013.05.20
아카이브 로그 관리  (0) 2012.08.24
Restoring a table space level backup with redirect  (0) 2012.08.12
db2pd 명령을 사용하여 모니터링 및 문제점 해결 (9.7)  (0) 2012.08.09
728x90


Problem(Abstract)

Optim server on AIX 6.1 can't find the 32 bit Oracle 11.x cleint

Symptom

Error from Optim Server trace file:

ENVW32IF ENVIntLoadSharedLib (4373) EXCP 09/23/2011 14:41:29 000023 00094036 0000004A
LoadLibraryEx for 'pn0dsqr3' Failed. GetLastError returns 126, Msg "The specified module could not be found. "

ENVW32IF ENVIntLoadSharedLib (4381) EXCP 09/23/2011 14:41:29 000024 00094036 0000004A 
LoadLibraryEx Failed on Unix. errno is 8, dlerror() gives "(null)"

ENVCCOMP ENV_LoadSpgm (1067) EXCP 09/23/2011 14:41:29 000025 00094036 0000004A 
Cid=5 CType=0, Spgm=PN0DSQR3 ECode=231

ENVCSERV ERR_SysErr (0000) SYSE 09/23/2011 14:41:29 000026 00094036 0000004A
System Error: Load Service Pgm
Aux Data: PN0DSQR3
Date/Time: 09/23/2011 14:41:29
Recursion: 0 
RetCode: PST_FAILED(-00001) General logic error
ExtCode: ENVERR_BADSPGMLOAD(00231) Load of PST SPGM or ExtDLL failed
Token1: PN0DSQR3
Token2: PN0DSQR3

The Optim Workstation will also report the following upon selecting the Optim Server:

Unable to communicate with server <server name>.
Optim directory <directory name> is unknown
Unexpected response from internal component, RC:-1

Cause

The AIX 11.x 32 bit Oracle client no longer uses the <Oracle_home>/lib32 directory and has moved to just <Oracle_home>/lib directory. The Optim server cannot find the correct Oracle client library files to use the Oracle client, thus reporting that it can't find the Optim directory

Environment

AIX 6.1 and Oracle 11.x

Resolving the problem

For Optim to operate correctly, the following symbolic links are required:

Go to $ORACLE_HOME/lib: 
ln -s libclient11.a libclient10.a

Go to $ORACLE_HOME: 
ln -s lib lib32



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

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

'optim' 카테고리의 다른 글

Optim 11.3 버전에서 DB2 11.1 , SQL Server 2016 연결하기  (0) 2017.04.10
Optim Connect Server returns "[C012] Invalid username/password" on AIX  (0) 2014.06.19
Receiving challenge module error in Optim exit  (0) 2012.11.28
Right Oracle Client version for Optim on Linux x64  (0) 2012.11.18
Optim Build Numbers and Versions  (0) 2012.11.18
728x90

Windows 7 64bit에서 오라클 11gR2 클라이언트를 설치할 때 필요 조건 검사 수행후 화면이 사라지는 경우가 있습니다.

이는 시스템에 jre가 32비트로 설치되었기 때문일 수 있습니다. 

설치시 64비트의 jre나 jdk의 경로를 지정하여 해결할 수 있습니다. 또는 32비트의 클라이언트를 설치할 수도 있겠지요.

 

Linux/Unix의 경우:

setup -jreloc 

 

Windows의 경우:

setup.exe -jreloc C:\Oracle\Middleware\jdk160_18\jre

 

 

참조: 

http://blog.naver.com/PostList.nhn?blogId=lsv400

https://forums.oracle.com/forums/thread.jspa?threadID=2190825

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

'Oracle > oracle' 카테고리의 다른 글

오라클에서 access 파일 조회하기  (0) 2019.08.01
hex string 을 blob 에 입력하기  (0) 2016.11.22
hash_value VS sql_id  (0) 2012.07.17
TRANSLATE 함수 사용법  (0) 2012.01.04
over(partition by) 예제  (0) 2011.09.09
728x90

Global Transaction이 정상적으로 종료되지 않았을 때 해결방법에 관한 포스트입니다. 사례가 상세해 소개해 드립니다.

(출처: comp.databases.informix newsgroup)


On my Informix instance I have two global transactions holding some locks. See onstat -G and onstat -x output below.
How can I get rid of them?
Also after a server reboot they are still there.
onmode -Z <address> or onmode -H <address> do not work (see below).

IBM Informix Dynamic Server Version 11.50.UC7IE on Solaris SPARC 10.

Global Transaction Identifiers
address flags isol timeout fID gtl bql data
56656bd8 -LX-G LC 0 131075 30 28 312D2D35336530656466663A613139383.....
56656e28 -LX-G LC 0 131075 30 28 312D2D35336530656466663A613139383.....
2 active, 128 total

Transactions

                                                                          est.

address  flags userthread locks  begin_logpos      current logpos isol    rb_time  retrys coord

56655018 A---- 56623018   0      -                 -              COMMIT  -        0

56655268 A---- 56623638   0      -                 -              COMMIT  -        0

566554b8 A---- 56623c58   0      -                 -              COMMIT  -        0

56655708 A---- 56624278   0      -                 -              COMMIT  -        0

56655958 A---- 56624898   0      -                 -              COMMIT  -        0

56655ba8 A---- 56624eb8   0      -                 -              COMMIT  -        0

56655df8 A---- 566254d8   0      -                 -              COMMIT  -        0

56656048 A---- 56625af8   0      -                 -              COMMIT  -        0

56656298 A---- 56626118   0      -                 -              COMMIT  -        0

566564e8 A---- 56626738   0      -                 -              COMMIT  -        0

56656738 A---- 56626d58   0      -                 -              COMMIT  -        0

56656988 A---- 56627378   0      -                 -              COMMIT  -        0

56656bd8 -LX-G 0          2      logid 303         303:0x64b2622c COMMIT  2:58     0

56656e28 -LX-G 0          2      logid 303         303:0x64b2922c COMMIT  2:58     0

56657078 A---- 56627fb8   0      -                 -              COMMIT  -        0

566572c8 A---- 56627998   0      -                 -              COMMIT  -        0

56657e58 A---- 56629218   0      -                 -              COMMIT  -        0

위의 예와 같이 flags 값이 '-LX-G'인 트랜잭션에는 userthread 정보가 없습니다. 이러한 트랜잭션을 orphaned transaction이라고 하는데 userthread가 트랜잭션에서 떨어져나가 멈춰있는 상태입니다. 만약에 이러한 트랜잭션이 Logical Log를 사용중인 채로 멈춰버리면 Long Transaction 상태에 빠질 수도 있습니다.


> onmode -Z 1449487912
onmode: Cannot kill transaction 0x56656e28.
Only I-STAR subordinates that are PREPARE'd or HEURISTICally ABORT'd
may be heuristically completed.

> onmode -H 1449487912
onmode -H may only be used to kill heuristically completed transactions.


여기서는 address를 십진수로 명시해서 onmode를 실행했습니다. onmode -Z (또는 -H) 0x56656e28 같은 형식으로 실행해도 됩니다.

포스트 스레드를 읽다보면 다음과 같은 내용이 있습니다.


Try onmode -l until you have forced a long transaction, this usually
help us. We have requested a way to get rid of these, but it seems
that the view of  IBM that the TM should handle this i all cases.
Problem is that the TM fails sometimes, and then you are stuck.

Regards

Ulf

처음 질문했던 Frank의 경우에는 로그 스위칭이 자연스럽게 발생하면서 Long Transaction이 유발되었습니다. 이는 LTXHWM 구성변수 설정값에 따라 달라지는데 기본값은 70입니다. 만약 Logical Log가 10개라면, 한 트랜잭션이 7번의 로그 스위칭이 일어나는 동안 완료되지 않으면 일반적으로 Long Transaction이 유발됩니다.

Today, the transactions were rolled back by the server:

23:42:09  Aborting Long Transaction: tx: 0x56656bd8 no user info due to XA or distributed (2-phase commit) transaction
23:42:09  Aborting Long Transaction: tx: 0x56656e28 no user info due to XA or distributed (2-phase commit) transaction
23:42:13  Checkpoint Completed:  duration was 4 seconds.
23:42:13  Thu Jul  1 - loguniq 306, logpos 0x3fff33b4, timestamp: 0xdedfaa19 Interval: 5616

23:42:13  Maximum server connections 15
23:42:13  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 1, Plog used 83432, Llog used 182271

23:42:13  Session completed abnormally. Rolling back tx id 24, flags 0x108463b
23:42:13  Session completed abnormally. Rolling back tx id 32, flags 0x108463b
23:42:13  Long Transaction 0x56656e28 Aborted. Rollback Duration: 0 Seconds
23:42:14  Long Transaction 0x56656bd8 Aborted. Rollback Duration: 0 Seconds

onmode -l로 수동으로 로그 스위칭을 발생시키거나 로그가 쌓이면서 LTXHWM 한계치를 넘을 경우 위와 같이 처리됩니다. 일반적으로는 위와 같이 처리되었을 때 인스턴스나 다른 트랜잭션에는 영향이 없습니다.

onmode -H에 대해서는 공식적인 문서를 찾기는 어렵습니다. 다만 에러메시지로 유추해보건데 (onmode -H may only be used to kill heuristically completed transactions.) 휴리스틱 트랜잭션에만 적용되는 것으로 보입니다.

제 경험상으로는 onstat -G에서 flags 값이 다음과 같은 경우에 사용합니다.

700000379440490  -TH-G 0                0      108002:0xaf7a018  108072:0xd068     DIRTY   26:56    0

70000038ad89b48  -TH-G 0                0      107898:0x4524018  107968:0x6a018    COMMIT  0:00     0

70000038c7ccfa0  -TH-G 0                0      107993:0x14781050 108063:0x9404     DIRTY   32:58    0

70000038c7e1160  -TH-G 0                0      108002:0x2dea018  108072:0x27678    DIRTY   27:10    0

70000038d69e178  -TH-G 0                0      108002:0x39e018   108072:0xe118     DIRTY   27:14    0


flags 값이 '-TH-G' 상태인 경우 LTXHWM 한계치를 넘어도 트랜잭션이 제거되지 않습니다. 이러한 경우에는 부득이 -H 옵션으로 제거할 수는 있으나 Infocenter나 onmode 명령에 -H 옵션에 대한 설명이 없으므로 사용에 주의해야 합니다.



http://www.iiug.org/forums/ids/index.cgi/noframes/read/2975

https://groups.google.com/forum/?hl=en&fromgroups=#!topic/comp.databases.informix/egoEcVkTmbY

https://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.admin.doc/ids_admin_1066.htm

728x90

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

Unloading around table corruption  (0) 2013.03.08
Is there an alternate way to install IBM Informix Dynamic Server?  (0) 2013.03.02
Error "log_put( OLDRSAM:66, 35052): log record too long"  (0) 2013.02.13
slow ontape backup or archive of Informix database server instance  (0) 2013.01.16
Corrupt Page During an Archive  (0) 2013.01.03
728x90

Error description

xa_recover does not work for MSDTC transactions although they are XA_PREPARED or heuristically completed.

onstat -x looks like that


Transactions


address flags userthread locks beginlg curlog logposit isol retrys coord

...

1e23dc88 -TH-G 0 0 136 151 0x103c COMMIT 0

1e23de90 -TX-G 0 0 145 145 0x229044 COMMIT 0


It should be possible to remove such transactions using an external tool


Problem summary

****************************************************************
USERS AFFECTED:
9.30 and above
****************************************************************
PROBLEM DESCRIPTION:
XA tightly-coupled transaction (via MSDTC), multiple
transaction branches:
-> first branch starts and does some SQL work
-> second branch starts and does some SQL work
-> second branch is prepared and rolled back
-> first branch exits without ending the branch.
an orphaned transaction branch exists.
****************************************************************
RECOMMENDATION:
Request special build or upgrade to higher IDS release such as
10.00 or 11.50 latest fixpack.
****************************************************************



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

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

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


Problem(Abstract)

IBM® Informix Server™ may Assert Failed showing messages such as "log_put( OLDRSAM:66, 35052): log record too long" in the with Assert Failed File and/or "Checkpoint log record may not fit into the logical log buffer. Recommended minimum value for LOGBUFF is 36" in the online.log file.

Resolving the problem

PROBLEM

There is an undocumented limitation in IBM® Informix Server™ that sets the maximum number of transactions opened at checkpoint time. If this limit is exceeded, the instance will stop

If the number of open transactions open at checkpoint time exceeds certain number (This number is dependant of the Logical Log Buffer Size), the instance will stop. You can check that the Assert Failure File will show this message:

    14:51:48  log_put( OLDRSAM:66, 35052): log record too long
    logrec
    Bc87a2044: 000088ec 00000042 00000010 00000000 .......B ........
    Bc87a2054: 00000001 01c72018 09bae247 00000000 ...... . ...G....

Also you will notice some warnings in the online.log before the instance stops with the following messages:

    15:24:55  Checkpoint log record may not fit into the logical log buffer.
    Recommended minimum value for LOGBUFF is 36.


CAUSE

Informix Server uses the Logical Log Buffer (LLB) to write logical log records (LLR) to the Logical Log File. A logical Log record cannot be bigger than the LLB, if such a situation is found, the engine will abort to prevent an incomplete write to the Logical Log File.

Usually, LLR are very small compared with a 32KB buffer size (The default LLB size), however the size of a checkpoint record depends on the number of transactions opened at checkpoint time. In version 9.4 the space required by a Checkpoint Logical Log Record will be :

    36 + 24xN bytes

where N is the number of transactions open at that moment.

A 32KB Logical Log Buffer size will allow for only 1363 open transactions at checkpoint time. If that number is exceeded, the engine will crash.


SOLUTION

Increase the Logical Log Buffer to a higher value. The formula given above is for version 9.4 but can be used for any version as the sizes of the structures do not change that much.



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

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

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

Is there an alternate way to install IBM Informix Dynamic Server?  (0) 2013.03.02
how to terminate global transactions?  (0) 2013.02.13
slow ontape backup or archive of Informix database server instance  (0) 2013.01.16
Corrupt Page During an Archive  (0) 2013.01.03
Database Server Blocked in Checkpoint (Blocked:CKPT) due to disabled dbspace  (0) 2012.07.05
728x90

Question

A sample ESQL/C program to insert data into a BLOB (TEXT) column using ESQL/C.

Answer

The attached program can be used as a reference for understanding the locator structure and how one can insert blob data via an ESQL/C program. This program is meant to be used only as a reference and no error checking has been taken into consideration. For example, the program will not check if the specified col1 number is valid or not. It is up to the programmer to put error checks into the code.

The Locator Structure
In an ESQL/C program, a locator structure is used to access simple-large-object values. The locator structure is the host variable for TEXT and BYTE columns when they are stored in or retrieved from the database. This structure describes the location of a simple-large-object value for the following two database operations:

  • When the program inserts the simple large object into the database, the locator structure identifies the source of the simple-large-object data to insert.
  • When the program selects the simple large object from the database, the locator structure identifies the destination of the simple-large-object data.

The locator.h header file defines the locator structure, called loc_t. ESQL/C does not automatically include the locator.h header file in an ESQL/C program. The locator.h header file must be explicitly included in any ESQL/C program that defines simple-large-object variables.

The loc_loctype identifies the location of the simple large object data. When you setloc_bufsize to -1, ESQL/C allocates the memory buffer on a fetch or select. ESQL/C uses themalloc() system call to allocate the memory buffer to hold a single simple-large-object value.

Compilation
    1. Save the attached program as getblob.ec.
    2. Compile using the following syntax:
      esql putblob.ec -o putblob

Sample Output
    > putblob
    Enter a Catalog number :1
    Enter the description :
    Test insert
    Successful insert
Disclaimer: This code has been tested on Solaris only. Behavior on any other operating system is not guaranteed.



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

putblob.ec.txt
0.0MB
728x90
저작자표시 비영리 변경금지

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

Determining how many CPUs you have under AIX  (0) 2013.04.02
(작성중)  (0) 2013.02.20
How to determine the number of concurrent connection/users on Informix server?  (0) 2013.01.29
Using Informix Dynamic Server backup and restore filters  (0) 2013.01.15
How to check the real memory used by locks in different Informix Server versions  (0) 2012.11.18
728x90


Question

This article will explain why a DB2 FORCE APPLICATION command may not work even though you get a successful return code from the execution of the command.

Cause

There is a delay being experienced when executing this command.

Answer

A DB2 FORCE APPLICATION command is an interrupt request.

When an agent executes a force, it will set a force flag for the application that it will force and depending on what the coordinator agent is doing, it will react accordingly by either waiting or sending him an interrupt.

In some cases, we will be in a wait mode. This is due to a encountering a higher priority.

When an interrupt is provided, each interrupt has priorities. The general rule of thumb DB2 will only interrupt the target if the target is working on a lower priority.

For example: If an application is in the middle of a rollback and if the user executes a DB2 FORCE APPLICATION command to force it, the database can go into a inconsistent state and therefore, we do not want that to happen. So DB2 will not interrupt the rollback. This control is implemented by the interrupt priority

In this example, just prior to the rollback, the priority will be increased so a lower priority agent in this case the force application will not interrupt him. Also, the force operation is an asynchronous operation. So if the application is in rollback processing, the agent doing the force will only mark the force flag of the application, but will not interrupt the application immediately. After the coordinator agent finishes the rollback, DB2 restores to the lower interrupt level (likely back to 0), and on various places in the code path, the agent checks for interrupt or force, so at this time, DB2 will notice the force and then react to it.

In conclusion, the delay is due to DB2 evaluating priorities prior to executing.


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

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

'Db2 > Db2 troubleshooting' 카테고리의 다른 글

LIC1449N error applying DB2 Enterprise Server Edition license on a 32-bit Linux system  (0) 2013.04.04
DB생성시 SQL1005N 오류  (0) 2013.03.14
FORCE APPLICATION will not force sub-agents that are handling external routine requests.  (0) 2013.02.01
db2icrt fails with DBI1959N after computer name change on windows server  (0) 2013.01.03
Load 수행시 주의사항 (SQL0290N)  (0) 2012.12.28
728x90


Problem(Abstract)

FORCE APPLICATION will not force sub-agents that are handling external routine requests even though tried to force them using FORCE APPLICATION command.

FORCE APPLICATION command will return successful but applications which are hang will still remain in the system.

Symptom

procstack output may show something like below:


    0x0900000000251e80 semop(??, ??, ??) + 0xc0 
    0x090000001535c9d4 semop@glue11(??, ??, ??) + 0x78 
    0x090000001535c85c sqloSSemP__fdpr_3(??, ??, ??, ??) + 0x24 
    0x09000000152981b0 sqlccipcrecv(SQLCC_COMHANDLE_T*,SQLCC_COND_T*)(??, ??) + 0xd8 
    0x0900000014f579e0 sqlccrecv__fdpr_1(0x700000040000370, 
    0x780000007078780, 0xf0000000f000, 0x1, 0x9000000188bcc38, 
    0x8d0000000000008d, 0x0, 0x0) + 0x370 
    0x0900000015a931b4sqljsDrdaAsNonCoordSPDrive(db2UCconHandle*,sqlerFmpCommInfo*)(??,
    ??) + 0x1b0

Cause

Some sub-agents still remain in database. These can't be forced. This is a current designed behavior of DB2 V9.5 and DB2 V9.7.


Note : DB2 V10.1 has design enhancements which will allow to force such sub-agents using the command.


Resolving the problem

Either of the following can be used to force the hanging application.


1) Recycle the instance. i.e Issue db2stop and db2start. Incase, recycling the instance is not possible then,

2) Terminate the active FMP processes which will wake up sub-agents.

For Example : Issue db2pd -fmp to find the active FMP processes. You can see output as shown below:

db2pd -fmp shows:

    Database Partition 203 -- Active -- Up 3 days 23:55:01 -- Date 2012-09-27-11.34.33.831054

    FMP:
    Pool Size: 0
    Max Pool Size: 200
    Keep FMP: YES
    Initialized: YES
    Trusted Path: /db2home/db2inst1/sqllib/function/unfenced
    Fenced User: bifenc
    Shared Memory: 0x0780000000290420
    IPC Pool: 0x0780000000290480

    FMP Process:
      Address FmpPid Bit Flags ActiveThrd PooledThrd ForcedThrd Active IPCList
    0x07800000077BD000 38732016 64 0x00000002 2 1 0 Yes 0x0780000006FD1600
      Active Threads:
        Address FmpPid EduPid ThreadId
      0x0780000006D3FDC0 38732016 13369 1029
      0x0780000006FD0A80 38732016 20027 1286

      Pooled Threads:
        Address FmpPid ThreadId
      0x07800000077BDA80 38732016 772

      Forced Threads:
      Address FmpPid ThreadId
      No forced threads.

The active FMP threads should each have an associated edu. In the above example they are 13369 and 20027. Make sure these two edu's are indeed hanging. If yes, then FMP id to be terminated would be 38732016

db2pd -apinfo 옵션등을 통해 해당 어플리케이션의 EduPid를 추적, 이후에 db2fmpterm 명령으로 FMP 종료

Once you find the FMP id to be terminated, issue "db2fmpterm <fmp_id>" command to terminate the active FMP.

For Example : db2fmpterm 38732016

This way you can avoid recycling your instance to kill such hang applications


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

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

'Db2 > Db2 troubleshooting' 카테고리의 다른 글

DB생성시 SQL1005N 오류  (0) 2013.03.14
Why does DB2 FORCE APPLICATION not work sometimes?  (0) 2013.02.01
db2icrt fails with DBI1959N after computer name change on windows server  (0) 2013.01.03
Load 수행시 주의사항 (SQL0290N)  (0) 2012.12.28
DPF 환경에서 시스템 시간을 과거로 변경했을 때의 오류 (SQL0903N)  (0) 2012.12.03
728x90

Question

How to determine the number of concurrent users/sessions on Informix server?

Answer

Run this SQL command:

select sh_curmaxcons, sh_ovlmaxcons from sysmaster:sysshmvals;

sh_curmaxcons - updated as new connections are made, reset after a bounce. This means that the value will reset for each shut down and start up of the server. 

sh_ovlmaxcons - updated at each checkpoint, persists over bounces, and upgrades. The ovlmaxcons value is initialized the first time you INSTALL the server. Thereafter, the maximum # of connections is recorded in the sysmaster database. Even after an upgrade from 9.4 to 10.0, that value will not be reset or lost. 


위 쿼리는 인포믹스 9.4 버전 이상에서 적용된다.

인포믹스 포럼을 살펴보니 현재 세션 개수를 확인하는 쿼리는 다음과 같다. 모든 버전에서 확인 가능하다.

select count(*) concurrentsessions
from sysmaster:sysrstcb
where sysmaster:bitval(flags,1)>0 { only count busy threads }
and sysmaster:bitval(flags,'0x80000')!=0 { only count primary threads }
and sysmaster:bitval(flags,'0x200')==0 { remove daemon threads}
and sysmaster:bitval(flags,'0x40')==0 { remove log backup users }
and sysmaster:bitval(flags,'0x80')==0 { remove onmonitor users }
and sysmaster:bitval(flags,'0x2000')==0 { remove page cleaners}
and sysmaster:bitval(flags,'0x00000400')==0 { remove archive threads}
and sysmaster:bitval(flags,'0x00004000')==0 { remove recovery threads}
and sysmaster:bitval(flags,'0x00100000')==0 { remove idx blder }
and sysmaster:bitval(flags,'0x00200000')==0 { remove btree scanner }
and sysmaster:bitval(flags,'0x04000000')==0 { remove main loop}
and flags < '0x20000000';

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

https://www.ibm.com/developerworks/forums/thread.jspa?messageID=14696424

http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14697676


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

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

(작성중)  (0) 2013.02.20
Inserting Data into a BLOB Column (TEXT) using ESQL/C  (0) 2013.02.04
Using Informix Dynamic Server backup and restore filters  (0) 2013.01.15
How to check the real memory used by locks in different Informix Server versions  (0) 2012.11.18
How to isolate High CPU usage from for Informix  (0) 2012.11.18

+ Recent posts

티스토리툴바