今天发现了一次reorg的失败，于是调查记录了一下：
首先，从db2diag里面得到了少量的信息：
2014-04-03-09.32.29.017545+120 I422278219A532 LEVEL: Severe
PID : 50463138 TID : 115273 PROC : db2sysc 0
INSTANCE: db2pb8 NODE : 000 DB : PB8
APPHDL : 0-8175 APPID: 10.194.6.66.8798.140302004032
AUTHID : SAPPB8
EDUID : 115273 EDUNAME: db2agent (PB8) 0
FUNCTION: DB2 UDB, relation data serv, sqlrreorg_table, probe:550
MESSAGE : Reorg: DMS return rc =
DATA #1 : Hexdump, 4 bytes
0x0A00000218FE72A4 : 8010 0044 ...D
2014-04-03-09.32.29.017780+120 I422278752A489 LEVEL: Severe
PID : 50463138 TID : 115273 PROC : db2sysc 0
INSTANCE: db2pb8 NODE : 000 DB : PB8
APPHDL : 0-8175 APPID: 10.194.6.66.8798.140302004032
AUTHID : SAPPB8
EDUID : 115273 EDUNAME: db2agent (PB8) 0
FUNCTION: DB2 UDB, relation data serv, sqlrreorg_table, probe:600
DATA #1 : String, 60 bytes
Table Schema : SAPPB8
Table Name : RSDDSTATINFO
信息量有点小，于是查询reorg的历史：
db2 list history reorg since 20140403 for pb8 | more
。.....
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
G T 20140403083223 F S0515900.LOG S0515915.LOG
----------------------------------------------------------------------------
Table: "SAPPB8 "."RSDDSTATINFO"
----------------------------------------------------------------------------
Comment: REORG INDEX 1 USE 3
Start Time: 20140403083223
End Time: 20140403093229
Status: A
----------------------------------------------------------------------------
EID: 527930
SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -911 sqlerrml: 2
sqlerrmc: 68
sqlerrp : SQLRR05A
sqlerrd : (1) -2146435004 (2) 68 (3) 0
(4) 0 (5) -1150 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
G T 20140403170652 F S0516032.LOG S0516035.LOG
----------------------------------------------------------------------------
Table: "SAPPB8 "."RSDDSTATINFO"
----------------------------------------------------------------------------
Comment: REORG INDEX 1 USE 3
Start Time: 20140403170652
End Time: 20140403170703
Status: A
----------------------------------------------------------------------------
EID: 528104
发现因为锁的问题，一个索引的reorg失败了；但是，紧接着，它又在17：06的时候成功了。
继续查看此reorg的类型：
db2 "select TABNAME,OPERATION,OPERATIONTYPE,SQLCODE,START_TIME,END_TIME from SYSIBMADM.DB_HISTORY where TABNAME='RSDDSTATINFO' fetch first 10 rows only"
TABNAME OPERATION OPERATIONTYPE SQLCODE START_TIME END_TIME
-------------------------------------------------------------------------------------------------------------------------------- --------- ------------- ----------- -------------- --------------
RSDDSTATINFO G F - 20140321085133 20140321085150
RSDDSTATINFO G F -911 20140403083223 20140403093229
RSDDSTATINFO G F -20140403170652 20140403170703
3 record(s) selected.
能够看到，reorg的类型是F，离线reorg；
能够看到成功的时间和上面的history输出是一致的。
同时，通过db history，我们还能了解到reorg是给table做的还是给index做的，例如：
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
G T 20140321082853 F S0511479.LOG S0511482.LOG
----------------------------------------------------------------------------
Table: "SAPPB8 "."/BI0/PAPO_PROD"
----------------------------------------------------------------------------
Comment: REORG INDEX 1 USE 3
Start Time: 20140321082853
End Time: 20140321082859
Status: A
----------------------------------------------------------------------------
EID: 521085
Obj T 表明，这个是给表做的reorg；
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
G I 20140321082900 N S0511479.LOG S0511483.LOG
----------------------------------------------------------------------------
Table: "SAPPB8 "."TNODEIMG"
----------------------------------------------------------------------------
Comment: REORG INDEXES
Start Time: 20140321082900
End Time: 20140321082903
Status: A
----------------------------------------------------------------------------
EID: 521087
Obj I， 表明，这个是给index做的reorg；
以上信息，从db_history中也能得到印证：
db2 "select TABNAME,OPERATION,OPERATIONTYPE,OBJECTTYPE,SQLCODE,START_TIME,END_TIME from SYSIBMADM.DB_HISTORY where OBJECTTYPE='T' and OPERATION='G'" | more
TABNAME OPERATION OPERAT
IONTYPE OBJECTTYPE SQLCODE START_TIME END_TIME
-------------------------------------------------------------------------------------------------------------------------------- --------- ------
------- ---------- ----------- -------------- --------------
/BI0/PAPO_PROD G F
T - 20140321082853 20140321082859
db2 "select TABNAME,OPERATION,OPERATIONTYPE,OBJECTTYPE,SQLCODE,START_TIME,END_TIME from SYSIBMADM.DB_HISTORY where OBJECTTYPE='I' and OPERATION='G'" | more
TABNAME OPERATION OPERAT
IONTYPE OBJECTTYPE SQLCODE START_TIME END_TIME
-------------------------------------------------------------------------------------------------------------------------------- --------- ------
------- ---------- ----------- -------------- --------------
TNODEIMG G N
I - 20140321082900 20140321082903
结论：
使用db history，也是发现并跟踪一些数据库操作的方法。
阅读(2724) | 评论(0) | 转发(0) |
