DB2并发问题诊断与实践-PartI

《DB2并发问题诊断与实践-PartI》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、DB2并发问题诊断与实践1.设置监控和Snapshot参数db2updatedbmcfgusingDFT_MON_LOCKonDFT_MON_STMTondb2updatemonitorswitchesusingbufferpoolonlockonsortonstatementontableontimestamponuowon2.查看Lock状态2.1通过listapplication查看通过listapplicationsshowdetail查看是否有lock-wat的applicationdb2listapplication

2、showdetailIgrep-iLock-waitCONNECTAuthIdApplicationNameAppl.ApplicationIdSeq#NumberofCoordinatingDBCoordinatorStatusStatusChangeTimeDBNameDBPathHandleAgentspartitionnumberpid/threadDB2INST8db2bp110*LOCAL.db2inst&1305150530480000110112Lock-waitNotCollectedTSTDB/datahom

3、e/db2inst8/db2inst8/NODE0000/SQL00001/DB2INST8db2bp106*LOCAL.db2inst8.1305150529030000110111Lock-waitNotCollectedTSTDB/datahome/db2inst8/db2inst8/NODE(X)00/SQL()0001/注意：UOWWaking是正常的，口J以用forceapplication(appid)结束lock-wait的application2.2通过db2pd来诊断db2pd-dbtstdb-lockswa

4、itshowlocksDatabasePartition0―DatabaseTSTDB―Active一Up0days00:29:10一Date2013-05-15-05.56.09.786262Locks:AddressTranHdlLocknameTypeModeStsOwnerDurHoldCountAtt0x00002B946D8D16801603000500060000000000000052Row.NWTbspacelD3TablelD5PartitionlD0Page0Slot60x00002B946D8D29801

5、703000500060000000000000052Row.NWW20TbspacelD30x00002B946D8D2B802TablelD5PartitionlD0Page0Slot603000500060000000000000052Row..SReleaseFlgrrllD100x000000000x40000000100x000000000x40000000I00x000000100x00000001TbspacelD3TablelD5PartitionlD0Page0Slot6注意红色标识的部分，锁名字相同代衣対同

6、一个対象加锁，类型为行锁，G代农已经获得该锁，W代农锁等待,会话的NW锁处于等待状态。2.3用UsingSnapshotTable查看lockSELECT*FROMTABLE(SNAPSHOT_LOCK('IOCDB；0))ASsSELECT*FROMTABLE(SNAPSHOT.LOCKWAIT(TOCDB；0))ASs2.3确定TransID对应的ApplIDdb2pd-dbtstdb-transDatabasePartition0—DatabaseTSTDB—Active—Up0days00:53:36—Date2013-

7、05-15-06.20.35.671600Transactions:AddressAppHandl[nod-index]TranHdlLocksStateTflagTflag2FirstlsnLastlsnSpaceReservedLogSpaceTIDAxRegCntGXIDClientUserlDClientWrkstnNameClientApplNameClientAccntng0x00002B946D409100106[000-00106]165WRITE0x000000000x000000000x00000000023

8、524530x00000000023524531241910x00000000014010n/an/an/an/a0x00002B946D40A000110[000-00110]175WRITE0x000000000x000000000x000000000235