728x90


Problem(Abstract)

Tivoli Storage Manager crash

Symptom

Tivoli Storage Manager does not start after crash


Cause

Disk failure on DB2 database files

Diagnosing the problem

During Tivoli Storage Manager start up, the following errors are reported:

ANR0172I rdbdb.c(1465): Error encountered performing action 
ActivateDatabase. 
ANR0162W Supplemental database diagnostic information: -1224:**No State
Information Available (128)**:-1224 (**No Other Information Available 
(206)**). 
ANR0172I rdbdb.c(1009): Error encountered performing action 
InstanceStop. 
ANR0162W Supplemental database diagnostic information: -1032:**No State
Information Available (89)**:-1032 (**No Other Information Available 
(72)**). 

The db2diag.log reports: 
. 
2010-02-19-15.29.08.326780+060 I289570185E438 LEVEL: Warning 
... 
MESSAGE : Crash Recovery is needed. 
2010-02-19-15.30.13.948634+060 I289572079E2547 LEVEL: Severe 
PID : 8492 TID : 47197773097280PROC : db2sysc 0 
INSTANCE: tsmb1 NODE : 000 DB : TSMDB1 
APPHDL : 0-7 APPID: *LOCAL.tsmb1.100219142908 
AUTHID : TSMB1 
EDUID : 65 EDUNAME: db2redow (TSMDB1) 0 
FUNCTION: DB2 UDB, buffer pool services, sqlbVerifyCBITS, probe:1110 
MESSAGE : ZRC=0x86020019=-2046689255=SQLB_CSUM "Bad Page, Checksum 
Error" 
DIA8426C A invalid page checksum was found for page "". 
DATA #1 : String, 64 bytes 
Error encountered trying to read a page - information follows : 
DATA #2 : String, 97 bytes 
CBIT verification error 
bitExpected is 0, userByte is 181, sector 63 (from head of page, 0 
based) 
DATA #3 : Page ID, PD_TYPE_SQLB_PAGE_ID, 4 bytes 
1196993 
DATA #4 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 72 bytes 
Obj: {pool:6;obj:5;type:1} Parent={5;5} 
... 


Resolving the problem

The db2diag messages indicate that the table ID 5 has corruption (see last message line: " {pool:6;obj:5;type:1} ".


You can do two different recoveries.

1) Restore the database to a point in time before the disk corruption occurred,

Or

2) Follow DB2 steps to determine if the corruption is only in the index tables and do a DB2 reorg.

Follow these steps to start the DB2 data collection:
$ db2stop 

SQL1064N DB2STOP processing was successful. 

Now run db2dart command for tablespaceid 6 or run for whole database. 
$ db2dart TSMDB1 /DB 
The requested DB2DART processing has completed successfully! 
Complete DB2DART report found in: 
/home/tsminst1/sqllib/db2dump/DART0000/TSMDB1.RPT 

Messages in RPT file:
Warning: The database state is not consistent.

Warning: Errors reported about reorg rows may be due to the inconsistent state of the database. 

Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2311036p,1008,x6000050319000000 prev:2311036p,1008,x5100AC5E180000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2249254p,344,x0D00071D19000000 prev:2249254p,344,x5700AF5E18000000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2248938p,951,x4C00FF7419000000 prev:2248938p,951,x4100BC5E18000000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2223926p,957,x55002C6A19000000 prev:2223926p,957,x2A00B95E18000000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2223926p,958,x81002C6A19000000 prev:2223926p,958,x2900B95E18000000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2223926p,959,x17002C6A19000000 prev:2223926p,959,x2800B95E18000000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2223926p,960,x18002C6A19000000 prev:2223926p,960,x2700B95E18000000

Index page corruption can be corrected by running the following command below :

Get the table name (This example is using the message output provided in the message: "{pool:6;obj:5;type:1} "):
db2 "select tabname from syscat.tables where tableid=5 and tbspaceid=6" 

Get the schema name:
db2 list tables 

Run db2 reorg command ( substituting value of schema name. tablename ) 

db2 " REORG INDEXES ALL FOR TABLE schemaname. table_ame ALLOW NO ACCESS"

If there is still an issue, DB2 support will need the full db2dart output for further analysis.


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

http://www.ibm.com/developerworks/data/library/techarticle/dm-1208corruptiondb2/

728x90
저작자표시 비영리 변경금지 (새창열림)

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

Commit delay may be encountered during DB2 log archive  (0) 2013.08.08
Collecting data for index corruption and/or index or data inconsistency issues  (0) 2013.05.31
SQL30082N Security processing failed with reason “26″  (0) 2013.05.13
"Detected client termination" in db2diag.log file.  (0) 2013.05.03
LIC1449N error applying DB2 Enterprise Server Edition license on a 32-bit Linux system  (0) 2013.04.04
728x90
Test scenario:
CREATE TABLE aaa.test( x integer not null, y integer not null, z integer not null )
DB20000I  The SQL command completed successfully.

alter table aaa.test pctfree 10 (테이블은 alter 작업필요)
DB20000I  The SQL command completed successfully.
create index aaa.ind on aaa.test(x,z) pctfree 40 (인덱스는 생성시 지정가능함)
DB20000I  The SQL command completed successfully.
select pctfree from syscat.indexes where indschema='AAA' and indname='IND'
PCTFREE
-------                      <<<<<<<<<<
     40
  1 record(s) selected.
insert into aaa.test with tmp(x,y,z) as ( values (1,1,1) union all select x+1, y+1, z+1 from tmp where x < 100 ) select * from tmp
DB20000I  The SQL command completed successfully.
runstats on table aaa.test with distribution and detailed indexes all
DB20000I  The RUNSTATS command completed successfully.
select pctfree from syscat.tables where tabschema='aaa' and tabname='TEST'
PCTFREE
-------
     10                      <<<<<<<<<<<<<
  1 record(s) selected.


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

728x90
저작자표시 비영리 변경금지 (새창열림)

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

How to calculate the DB2 transaction used log size without locking issue  (0) 2013.11.12
Recommended AIX Virtual Memory Manager settings for DB2 database product  (0) 2013.06.19
Moving data using the CURSOR file type  (0) 2013.05.20
9.7부터 변경된 CHAR 스칼라 함수 동작 (dec_to_char_fmt)  (0) 2013.02.18
아카이브 로그 관리  (0) 2012.08.24
728x90

By specifying the CURSOR file type when using the LOAD command, you can load the results of an SQL query directly into a target table without creating an intermediate exported file.

Additionally, you can load data from another database by referencing a nickname within the SQL query, by using the DATABASE option within the DECLARE CURSOR statement, or by using the sqlu_remotefetch_entry media entry when using the API interface.

There are three approaches for moving data using the CURSOR file type. The first approach uses the Command Line Processor (CLP), the second the API, and the third uses the ADMIN_CMD procedure. The key differences between the CLP and the ADMIN_CMD procedure are outlined in the following table.

Table 1. Differences between the CLP and ADMIN_CMD procedure.
DifferencesCLPADMIN_CMD_procedure
SyntaxThe query statement as well as the source database used by the cursor are defined outside of the LOAD command using a DECLARE CURSOR statement.The query statement as well as the source database used by the cursor is defined within the LOAD command using the LOAD from (DATABASE database-alias query-statement)
User authorization for accessing a different databaseIf the data is in a different database than the one you currently connect to, the DATABASE keyword must be used in the DECLARE CURSOR statement. You can specify the user id and password in the same statement as well. If the user id and password are not specified in the DECLARE CURSOR statement, the user id and password explicitly specified for the source database connection are used to access the target database.If the data is in a different database than the one you are currently connected to, the DATABASE keyword must be used in the LOAD command before the query statement. The user id and password explicitly specified for the source database connection are required to access the target database. You cannot specify a userid or password for the source database. Therefore, if no userid and password were specified when the connection to the target database was made, or the userid and password specified cannot be used to authenticate against the source database, the ADMIN_CMD procedure cannot be used to perform the load.

To execute a LOAD FROM CURSOR operation from the CLP, a cursor must first be declared against an SQL query. once this is declared, you can issue the LOAD command using the declared cursor's name as the cursorname and CURSOR as the file type.

For example:

  1. Suppose a source and target table both reside in the same database with the following definitions:
    Table ABC.TABLE1 has 3 columns:
    • ONE INT
    • TWO CHAR(10)
    • THREE DATE
    Table ABC.TABLE2 has 3 columns:
    • ONE VARCHAR
    • TWO INT
    • THREE DATE
    Executing the following CLP commands will load all the data from ABC.TABLE1 into ABC.TABLE2:
    DECLARE mycurs CURSOR FOR SELECT TWO, onE, THREE FROM abc.table1
   LOAD FROM mycurs OF cursor INSERT INTO abc.table2
    Note: The above example shows how to load from an SQL query through the CLP. However, loading from an SQL query can also be accomplished through the db2Load API. Define the piSourceList of the sqlu_media_list structure to use the sqlu_statement_entry structure and SQLU_SQL_STMT media type and define the piFileType value as SQL_CURSOR.
  2. Suppose the source and target tables reside in different databases with the following definitions:
Table ABC.TABLE1 in database 'dbsource' has 3 columns:
  • ONE INT
  • TWO CHAR(10)
  • THREE DATE
Table ABC.TABLE2 in database 'dbtarget' has 3 columns:
  • ONE VARCHAR
  • TWO INT
  • THREE DATE
Provided that you have enabled federation and cataloged the data source ('dsdbsource'), you can declare a nickname against the source database, then declare a cursor against this nickname, and invoke the LOAD command with the FROM CURSOR option, as demonstrated in the following example:
CREATE NICKNAME myschema1.table1 FOR dsdbsource.abc.table1 
DECLARE mycurs CURSOR FOR SELECT TWO,ONE,THREE FROM myschema1.table1 
LOAD FROM mycurs OF cursor INSERT INTO abc.table2
Or, you can use the DATABASE option of the DECLARE CURSOR statement, as demonstrated in the following example:
DECLARE mycurs CURSOR DATABASE dbsource USER dsciaraf USING mypasswd 
FOR SELECT TWO,ONE,THREE FROM abc.table1 
LOAD FROM mycurs OF cursor INSERT INTO abc.table2

Using the DATABASE option of the DECLARE CURSOR statement (also known as the remotefetch media type when using the Load API) has some benefits over the nickname approach:

Performance

Fetching of data using the remotefetch media type is tightly integrated within a load operation. There are fewer layers of transition to fetch a record compared to the nickname approach. Additionally, when source and target tables are distributed identically in a multi-partition database, the load utility can parallelize the fetching of data, which can further improve performance.

Ease of use

There is no need to enable federation, define a remote datasource, or declare a nickname. Specifying the DATABASE option (and the USER andUSING options if necessary) is all that is required.

While this method can be used with cataloged databases, the use of nicknames provides a robust facility for fetching from various data sources which cannot simply be cataloged.

To support this remotefetch functionality, the load utility makes use of infrastructure which supports the SOURCEUSEREXIT facility. The load utility spawns a process which executes as an application to manage the connection to the source database and perform the fetch. This application is associated with its own transaction and is not associated with the transaction under which the load utility is running.

Note:
  1. The previous example shows how to load from an SQL query against a cataloged database through the CLP using the DATABASE option of the DECLARE CURSOR statement. However, loading from an SQL query against a cataloged database can also be done through the db2LoadAPI, by defining the piSourceList and piFileTypevalues of the db2LoadStruct structure to use the sqlu_remotefetch_entry media entry and SQLU_REMOTEFETCH media type respectively.
  2. As demonstrated in the previous example, the source column types of the SQL query do not need to be identical to their target column types, although they do have to be compatible.

Restrictions

When loading from a cursor defined using the DATABASE option (or equivalently when using the sqlu_remotefetch_entry media entry with thedb2Load API), the following restrictions apply:
  1. The SOURCEUSEREXIT option cannot be specified concurrently.
  2. The METHOD N option is not supported.
  3. The usedefaults file type modifier is not supported.



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

728x90
저작자표시 비영리 변경금지 (새창열림)

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

Recommended AIX Virtual Memory Manager settings for DB2 database product  (0) 2013.06.19
TABLE, INDEX 생성시 PCTFREE 값 설정  (0) 2013.05.21
9.7부터 변경된 CHAR 스칼라 함수 동작 (dec_to_char_fmt)  (0) 2013.02.18
아카이브 로그 관리  (0) 2012.08.24
Restoring a table space level backup with redirect  (0) 2012.08.12
728x90

SQL30082N Security processing failed with reason “26″


SQL Error [28000]: [jcc][t4][207][11243][3.59.81] Connection authorization failure occurred.  Reason: GSSAPI non-retryable error. ERRORCODE=-4214, SQLSTATE=28000


DB2 JDBC및 Native Client 연결 관련하여 다음과 같은 오류가 발생하여, 검색한 결과 유사한 내용을 찾을 수 있었습니다.

linux에서 설정하는 패스워드 알고리즘 관련 문제였습니다. 참고하시기 바랍니다.


If you are getting

SQL30082N Security processing failed with reason „15“ („PROCESSING FAILURE“)

when connecting to the db2 database using db2 CONNECT TO dbname USER username or

Connection authorization failure occurred. Reason: Local security service non-retryable error. ERRORCODE=-4214, SQLSTATE=28000

when connecting remotely (via JDBC driver) read on. I finally found what causes this error and a solution!

[ad]

Environment

Problem

Tho whole problem is in the format of the passwords in/etc/shadow. DB2 doesn't seem to like the passwords generated when changing password using the standard passwd command. In Fedora Core 10 the password is hashed using sha-512 and the entry for user username looks like:

username:$6$ef­WWOYRY$z5DNL1kLQ­U4AmPkFBqbQh6LOh­0Qjxq654dS9jE46iP­Np8Zces8I4bP8GLZ3G3RWLo/­6o.LYOV5neYSKxXbL­.M1:14375:0:99999:7:::

Solution

DB2 works fine with passwords hashed with standard crypt function. Password in the desired format can be obtained by callingopenssl passwd desiredPassword. The output of openssl can be passed to usermod --password The complete command to change user's password then look like:

usermod --password `openssl passwd desiredPassword`usename

Bigger image

I tried to hand-edit /etc/shadow and insert salted MD5 password, which can be obtained by openssl passwd -1 desiredPasswordand the connection was sucessfuly established. To sum it up, the whole problem in the end seems to be that DB2 doesn't like SHA-512 hashes in /etc/shadow. on the other hand, it works fine with hashes generated by crypt and MD5.

Changing default algorithm in Fedora Core

Fedora Core contains a nice tool authconfig. To change the default hashing algorithm to MD5 run

authconfig –passalgo md5 –update

All the passwords inserted in the password database will be stored in MD5 from now on. It will, of course, not change the hashes of the current passwords.

Ubuntu

Ubuntu ships with sha512 as default hash algorithm for passwords in /etc/shadow. The easiest way to change the default algorithm is to edit the file /etc/pam.d/common-password and change the line that reads

password [success=1 default=ignore] pam_unix.so obscure sha512

to

password [success=1 default=ignore] pam_unix.so obscure md5

The information about the algorith is also included in the file/etc/login.defs, which is used by chpasswd for example. Therefore change the line

ENCRYPT_METHOD SHA512

to

ENCRYPT_METHOD MD5



http://blog.stastnarodina.com/honza-en/spot/db2-sql30082n-security-processing-failed-with-reason-15/

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

728x90

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

Collecting data for index corruption and/or index or data inconsistency issues  (0) 2013.05.31
DB2 database corruption causing Tivoli Storage Manager crash  (0) 2013.05.28
"Detected client termination" in db2diag.log file.  (0) 2013.05.03
LIC1449N error applying DB2 Enterprise Server Edition license on a 32-bit Linux system  (0) 2013.04.04
DB생성시 SQL1005N 오류  (0) 2013.03.14
728x90

Problem(Abstract)

db2diag.log file may get filled with "Detected client termination" error messages.

Symptom

Following errors may be seen in db2diag.log file.

2012-10-07-03.31.44.562773+480 I559988E520 LEVEL: Error

    PID : 2160 TID : 140574011156224PROC : db2sysc 
    INSTANCE: db2inst1 NODE : 000 DB : SAMPLE 
    APPHDL : 0-4470 APPID: 202.27.30.1.2101.121006192554 
    AUTHID : PSOGALAG 
    EDUID : 568 EDUNAME: db2agent (SAMPLE) 
    FUNCTION: DB2 UDB, common communicati on, sqlcctest, probe:50 
    MESSAGE : sqlcctest RC 
    DATA #1 : Hexdump, 2 bytes 
    0x00007FD9EFFF6780 : 3600 

    2012-10-07-03.31.44.543149+480 I560509E538 LEVEL: Error 
    PID : 2160 TID : 140574002767616PROC : db2sysc 
    INSTANCE: db2inst1 NODE : 000 DB : SAMPLE 
    APPHDL : 0-4510 APPID: 202.27.30.1.2286.121006192634 
    AUTHID : PSOGALAD 
    EDUID : 570 EDUNAME: db2agent (SAMPLE) 
    FUNCTION: DB2 UDB, common communication, sqlcctcptest, probe:11 
    MESSAGE : Detected client termination 
    DATA #1 : Hexdump, 2 bytes 
    0x00007FD9EF7F6748 : 3600 


Resolving the problem

Function sqlcctcptest() tests if the client connection is active and function sqlcctest() tests whether the connection is still valid.


If you don't want such messages to be logged into db2diag.log file, you can set DB2CHECKCLIENTINTERVAL to zero.

For Example : Issue following:

    1) db2set DB2CHECKCLIENTINTERVAL=0 
    2) db2stop 
    3) db2start

Note : This variable specifies the frequency of TCP/IP client connection verifications during an active transaction. It permits early detection of client termination, instead of waiting until after the completion of the query. If this variable is set to 0, no verification is performed.


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

728x90
저작자표시 비영리 변경금지 (새창열림)

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

DB2 database corruption causing Tivoli Storage Manager crash  (0) 2013.05.28
SQL30082N Security processing failed with reason “26″  (0) 2013.05.13
LIC1449N error applying DB2 Enterprise Server Edition license on a 32-bit Linux system  (0) 2013.04.04
DB생성시 SQL1005N 오류  (0) 2013.03.14
Why does DB2 FORCE APPLICATION not work sometimes?  (0) 2013.02.01
728x90

Problem(Abstract)

I receive the below error when applying the DB2 Enterprise Server Edition license on a 32-bit Linux system:

LIC1449N The license was not installed due to a platform restriction. 

Explanation: 
This DB2 product is only supported in trial mode, also known as "Try and Buy" mode, on this platform. 

User response: 
Continue to use this product in trial mode, or install one which is fully supported on this platform. 

Cause

DB2 Enterprise Server Edition v9.5 and higher is only supported in Test and Development mode on 32-bit Linux systems.

Resolving the problem

A DB2 Enterprise Server Edition permanent license may only be applied on a 64-bit Linux system.

Here are your options: 

  • Upgrade the OS to 64-bit Linux and then install Enterprise Server Edition and license.
  • Purchase another DB2 edition such as DB2 Workgroup Server Edition or DB2 Express Edition. You will need to also obtain and apply the corresponding permanent license.
  • Purchase DB2 Developer's Edition for 32-bit Linux. The Developer's Edition license certificate (db2dede.lic) can be applied on top of a Enterprise Server Edition install without any problems.


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

728x90
저작자표시 비영리 변경금지 (새창열림)

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

SQL30082N Security processing failed with reason “26″  (0) 2013.05.13
"Detected client termination" in db2diag.log file.  (0) 2013.05.03
DB생성시 SQL1005N 오류  (0) 2013.03.14
Why does DB2 FORCE APPLICATION not work sometimes?  (0) 2013.02.01
FORCE APPLICATION will not force sub-agents that are handling external routine requests.  (0) 2013.02.01
728x90

Question

Installing TWS the following error is encountered, SQL1005N The database alias "TWS" already exists in either the local database directory or system database directory.

Cause

This is caused when a previous installation of TWS was made and it created the default database, TWS.

Answer

If you are attempting a reinstallation using the same database name then you should perform the following :-

1. db2 catalog database tws 
2. db2 drop database tws


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

728x90
저작자표시 비영리 변경금지 (새창열림)

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

"Detected client termination" in db2diag.log file.  (0) 2013.05.03
LIC1449N error applying DB2 Enterprise Server Edition license on a 32-bit Linux system  (0) 2013.04.04
Why does DB2 FORCE APPLICATION not work sometimes?  (0) 2013.02.01
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
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


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

+ Recent posts

티스토리툴바