in0de

증상
   
LV를 resize한 후 특정 테이블에 access하면 dump를 발생하며 db가 restart되고
crash recovery를 반복적으로 수행함
"db2start admin mode"에서도 해결할 방도가 없음
해당 table 및 table이 포함된 tablespace를 alter, drop할 수 없음

db2diag.log에 발생하는 로그

2008-02-15-14.51.54.366915+540 E17958A453         LEVEL: Error (OS)
PID     : 6643876              TID  : 1           PROC : db2agent (DBTA) 0
INSTANCE: dbtainst             NODE : 000         DB   : DBTA
APPHDL  : 0-2634               APPID: GAFF6E24.FE3E.0402F4213409
FUNCTION: DB2 UDB, oper system services, sqloReadBlocks, probe:60
CALLED  : OS, -, unspecified_system_function
OSERR   : ENXIO (6) "존재하지 않는 장치나 주소에 대한 요청이 있습니다."

2008-02-15-14.51.54.377898+540 I18412A810         LEVEL: Error
PID     : 6643876              TID  : 1           PROC : db2agent (DBTA) 0
INSTANCE: dbtainst             NODE : 000         DB   : DBTA
APPHDL  : 0-2634               APPID: GAFF6E24.FE3E.0402F4213409
FUNCTION: DB2 UDB, oper system services, sqloReadBlocks, probe:200
MESSAGE : ZRC=0x860F0004=-2045837308=SQLO_DGFL "general failure (DD)"
          DIA8402C A disk error has occurred.
DATA #1 : File handle, PD_TYPE_SQO_FILE_HDL, 8 bytes
0x0FFFFFFFFFFED5C0 : 0000 0031 0000 0000                        ...1....
DATA #2 : unsigned integer, 4 bytes
891841
DATA #3 : unsigned integer, 8 bytes
1
DATA #4 : unsigned integer, 4 bytes
13
DATA #5 : File Offset, 8 bytes
8192
DATA #6 : File Offset, 8 bytes
7305961472
DATA #7 : unsigned integer, 8 bytes
0

2008-02-15-14.51.54.406948+540 I19223A458         LEVEL: Error
PID     : 6643876              TID  : 1           PROC : db2agent (DBTA) 0
INSTANCE: dbtainst             NODE : 000         DB   : DBTA
APPHDL  : 0-2634               APPID: GAFF6E24.FE3E.0402F4213409
FUNCTION: DB2 UDB, buffer pool services, sqlbReadPageFromContainer, probe:20
RETCODE : ZRC=0x860F0004=-2045837308=SQLO_DGFL "general failure (DD)"
          DIA8402C A disk error has occurred.

2008-02-15-14.51.54.420293+540 I19682A476         LEVEL: Error
PID     : 6643876              TID  : 1           PROC : db2agent (DBTA) 0
INSTANCE: dbtainst             NODE : 000         DB   : DBTA
APPHDL  : 0-2634               APPID: GAFF6E24.FE3E.0402F4213409
FUNCTION: DB2 UDB, buffer pool services, sqlbReadPageFromContainer, probe:20
MESSAGE :  Obj={pool:95;obj:108;type:1} State=x27 Parent={19;108}, EM=2389984,
          PP0=2390000 Cont=0 Offset=891841 BlkSize=13

2008-02-15-14.51.54.420993+540 I21321A496         LEVEL: Error
PID     : 6643876              TID  : 1           PROC : db2agent (DBTA) 0
INSTANCE: dbtainst             NODE : 000         DB   : DBTA
APPHDL  : 0-2634               APPID: GAFF6E24.FE3E.0402F4213409
FUNCTION: DB2 UDB, buffer pool services, sqlbrdpg, probe:1110
DATA #1 : String, 140 bytes
 Obj={pool:95;obj:108;type:1} State=x27 Parent={19;108}, EM=2389984, PP0=2390000 Page=2675473 Cont=0 Offset=891841 BlkSize=13
Unexpected EOF

2008-02-15-14.51.54.586485+540 I22536A411         LEVEL: Severe
PID     : 6643876              TID  : 1           PROC : db2agent (DBTA) 0
INSTANCE: dbtainst             NODE : 000         DB   : DBTA
APPHDL  : 0-2634               APPID: GAFF6E24.FE3E.0402F4213409
FUNCTION: DB2 UDB, Query Gateway, sqlqg_signal_handler, probe:10
MESSAGE : DIA0505I Execution of a component signal handling function has begun.

2008-02-15-14.51.54.604024+540 I22948A144         LEVEL: Severe
PID:6643876 TID:1 NODE:000 Title: QG SQLCA
Dump File:/dbta/db2dump/66438761.000

2008-02-15-14.51.54.606961+540 I23093A145         LEVEL: Severe
PID:6643876 TID:1 NODE:000 Title: QG DJDBCB
Dump File:/dbta/db2dump/66438761.000

2008-02-15-14.51.54.607172+540 I23239A144         LEVEL: Severe
PID:6643876 TID:1 NODE:000 Title: QG DJACB
Dump File:/dbta/db2dump/66438761.000

2008-02-15-14.51.54.607373+540 I23384A146         LEVEL: Severe
PID:6643876 TID:1 NODE:000 Title: QG APPDJCB
Dump File:/dbta/db2dump/66438761.000

2008-02-15-14.51.54.607571+540 I23531A147         LEVEL: Severe
PID:6643876 TID:1 NODE:000 Title: QG TRANDJCB
Dump File:/dbta/db2dump/66438761.000

2008-02-15-14.51.54.857356+540 I38845A432         LEVEL: Severe
PID     : 6643876              TID  : 1           PROC : db2agent (DBTA) 0
INSTANCE: dbtainst             NODE : 000         DB   : DBTA
APPHDL  : 0-2634               APPID: GAFF6E24.FE3E.0402F4213409
FUNCTION: DB2 UDB, DRDA Application Server, sqljsSignalHandler, probe:20
MESSAGE : DIA0506I Execution of a component signal handling function is complete.

2008-02-15-14.51.54.866330+540 I41128A460         LEVEL: Event (OS)
PID     : 6643876              TID  : 1           PROC : db2agent (DBTA) 0
INSTANCE: dbtainst             NODE : 000         DB   : DBTA
APPHDL  : 0-2634               APPID: GAFF6E24.FE3E.0402F4213409
FUNCTION: DB2 UDB, trace services, pdInvokeCalloutScript, probe:10
OSERR   : EACCES (13) "파일 액세스 권한이 지정된 조치를 허용하지 않습니다."
MESSAGE : Error invoking pdInvokeCalloutScript

원인

Tablespace보다 작게 LV를 resize했을 경우
마치 quiesce가 걸린 것처럼 데이터는 유실되지 않은 상태로
해당 Tablespace에 접근하려는 시도는 모두 실패하면서
Dump를 생성하고, db가 재기동 되며 crash recovery를 수행함
    
Lv를 줄이는 방법 두 가지
    • vxassist -g $(DGname) [shrinkto|shrinkby] -f $(LVname) $(size)
    • vxresize -g $(DGname) $(LVname) $(size)


처리

문제가 발생한 Tablespace는 db2diag.log에서
Parent 정보를 통해 문제가 발생한 Tablespace와 Table을 알 수 있음

Ex)  Obj={pool:95;obj:108;type:1} State=x27 Parent={19;108}
    Tablespaceid = 19
    Tableid = 108

다시 LV를 원래 사이즈로 복원하면, 놀랍게도 resize 직전의 상태로 원상 복구됨

Paa-k the caa- in haa-vid yaa-d!

What American accent do you have? Your Result: The Midland   "You have a Midland accent" is just another way of saying "you don't have an accent." You probably are from the Midland (Pennsylvania, southern Ohio, southern Indiana, southern Illinois, and Missouri) but then for all we know you could be from Florida or Charleston or one of those big southern cities like Atlanta or Dallas. You have a good voice for TV and radio.
Philadelphia
The South
The West
Boston
The Northeast
The Inland North
North Central
What American accent do you have? Quiz Created on GoToQuiz

Load시 load할 data size를 측정하기는 쉬워도,
index size까지 미리 예측하고 load하기에는 꼼꼼함이 따라주지 않는다.

이런 부주의로 load 하다가 tablespace full 된 경우에는
RESTART INTO option을 사용해
중단된 Load를 다시 시작할 수 있다.

RESTART는 INSERT, REPLACE, TERMINATE와 함께
Load command의 모드 중 하나인데,
바로 이전의 load 에서 중지되었던 consistency point부터 Load를 계속해 나간다.

그런데, index build phase부터 restart하더라도
거의 load phase에서 걸렸던만큼의 시간이 지난 후에 index build를 한다.

in load.sql

LOAD FROM "./data.asc" OF ASC
MODIFIED BY STRIPTBLANKS CODEPAGE=970 RECLEN=71
METHOD L (1 4,5 8) NULL INDICATORS (0,0)
RESTART INTO ${schema}.${table}(col1, col2);

db2 -tvf load.sql