如何診斷和解決db2問題

參考書:
牛新莊:
Oracle資料庫開發講座—— Oracle9i Jdeveloper與J2EE實務應用
DB2應用開發實戰指導
循序漸進DB2――DBA系統管理、運維與應用案例 http://www.ibm.com/developerworks/cn/data/books/db2mang/
DB2資料庫效能調整和優化 http://www.ibm.com/developerworks/cn/data/books/db2perf/
深入解析DB2--高階管理、內部體系結構與診斷案例  http://www.ibm.com/developerworks/cn/data/books/db2advan/

=============================================
如何診斷和解決db2問題:
=============================================
1.對於常見的報錯資訊,執行db2 ?
db2 [? [phrase | message | sqlstate | class-code]]]

1)db2 ? <message>:
DB2 ? SQLnnnn 顯示這個SQLCODE的解釋資訊(SQLCODE   四位數字) db2 ? sql0440   db2 ? SQL10007N  注：code必須為4位數，不夠4位，前面補0
DB2 ? DB2nnnn 顯示這個DB2錯誤的解釋資訊
2)db2 ? <sqlstate>
DB2 ? nnnnn 顯示這個SQLSTATE的解釋資訊(SQLSTATE  5位數字) db2 ? 42734

2.收集db2diag.log,notification log, event log,dump file,trap file, core file:
db2support c:/temp -d adapter -a -g -l -r -s

如何清理db2diag.log檔案
db2diag.log，是用來記錄DB2資料庫執行中的資訊的檔案。可以通過此檔案，檢視記錄的有關DB2資料庫詳細的錯誤資訊。此檔案也是不斷增大的，需要定期進行清理。
可以通過檢視例項的配置引數DIAGPATH來確定db2diag.log檔案是放在哪個目錄下：db2 get dbm cfg. 如果Diagnostic data directory path(DIAGPATH) = /home/db2inst1/sqllib/db2dump，則此檔案是放在/home/db2inst1/sqllib/db2dump目錄下。當檔案系統/home的使用率達到80%－90%左右時，應及時刪除db2diag.log檔案。
請按以下正確步驟操作：確認應用（如BTP）、DB2已經停止。
將原db2diag.log檔案備份到其它檔案系統下。
刪除db2diag.log檔案。刪除後，DB2會自動建立一個新的檔案。

3.db2pd -everything >c:/temp/db2pd.log  功能超級強大，由於db2pd可以直接從DB2的記憶體中讀取資訊，所以它比起snapshot來說開銷更小，速度也更快
比如: db2pd -version -osinfo 檢視db2version和機器資訊
      db2pd -db adapter -dynamic 檢視記憶體中的sql statements   ---------非常有用
   db2 get snapshot for dynamic sql on adapter >c:/temp/stmt.txt ---------非常有用
   db2 SELECT * FROM TABLE( SNAPSHOT_DYN_SQL( 'SAMPLE', -1 )) as SNAPSHOT_DYN_SQL  ---------非常有用
  
   db2pd  -db adapter -static
   db2 SELECT * FROM TABLE( SNAPSHOT_STATEMENT( 'SAMPLE', -1 )) as SNAPSHOT_STATEMENT
   db2 select * from syscat.statements
   .....

4.開啟trace內容:
1)db2trc on -l 50M 開啟db2 trace 收集最後的50M資訊
2)reproduce問題
3)db2trc dmp c:/temp/db2trc.dmp
4)db2trc off
5)db2trc fmt c:/temp/db2trc.dmp 1.txt 得到一個按照process/thread排序的文字檔案
6)db2trc flw c:/temp/db2trc.dmp 2.txt 得到一個按照時間先後排序的文字檔案

5.離線收集資料:
db2dart adapter
db2dart adapter /TSI <tablespaceId>  (db2 list tablespaces 可以得到tablespaceId)
db2dart adapter /TSI <tablespaceId> /TN "customer"
db2dart adapter /TSI <tablespaceId> /MI /OI <indexId>

6.線上收集資料:讀取記憶體中東東
db2inspect check database results c:/temp/1.out
db2inspect check tbspaceid 2 results keep c:/temp/1.out
db2inspf c:/temp/1.out c:/temp/2.txt

7.db2cos.bat
編輯這個指令碼里面的內容使得db2pd -everthing

8.db2pdcfg  指定發生什麼事件的時候做什麼事情。
db2pdcfg -db2cos on 當database manager trap的時候，自動call db2cos.bat
db2pdcfg -catch -911,2 當死鎖發生的時候dump所有有用資訊
db2pdcfg -catch -911,2 db2cos 當死鎖發生的時候,call db2cos
db2pdcfg -catch -289 當tablespace full的時候，dump所有有用資訊
db2pdcfg -fodc dumpshmem=on 在最近的一次資料庫crash的情況下你懷疑記憶體corruption, 將shared memory全部dump出來。

9.db2fodc 指定當發生什麼事情的時候，dump內容
1)db2pdcfg -fodc dumpshmem=on 在最近的一次資料庫crash的情況下你懷疑記憶體corruption, 將shared memory全部dump出來。  or
  db2fodc -hang -alldb
  db2fodc -db adapter -hang full
2)執行db2support收集fodc資料： db2support c:/temp -d adapter -a -g -l -r -s

10.event monitor, activity monitor, snapshot:
1)如何使用snapshot：
C:/>db2 ? get snapshot
GET SNAPSHOT FOR {DATABASE MANAGER | ALL [DCS] DATABASES |
ALL [DCS] APPLICATIONS | ALL BUFFERPOOLS | [DCS] APPLICATION
{APPLID appl-id | AGENTID appl-handle} | FCM FOR ALL DBPARTITIONNUMS |
LOCKS FOR APPLICATION {APPLID appl-id | AGENTID appl-handle} |
{ALL | [DCS] DATABASE | [DCS] APPLICATIONS | TABLES |
TABLESPACES | LOCKS | BUFFERPOOLS | DYNAMIC SQL [write to file]}
ON database-alias} [AT DBPARTITIONNUM db-partition-number | GLOBAL]

NOTE: From the operating system prompt, prefix commands with 'db2'.
      Special characters MAY require an escape sequence (/), for example:
      db2 /? change database
      db2 ? change database xxx comment with /"text/"

db2 get snapshot for application agentid 2432;

11.用SQL語句實現DB2主要指標的監控 (參考Administrative SQL Routines and Views.pdf)
--Database manager:To capture a snapshot of database manager information:
SELECT * FROM TABLE( SNAPSHOT_DBM(-1)) as SNAPSHOT_DBM

--To capture a snapshot of database manager information specifically regarding the fast communication manager (FCM):
SELECT * FROM TABLE( SNAPSHOT_FCM(-1)) as SNAPSHOT_FCM

--To capture a snapshot of database manager information for a partition specifically regarding the fast communication manager(FCM):
SELECT * FROM TABLE( SNAPSHOT_FCMPARTITION(-1)) as SNAPSHOT_FCMPARTITION

--To capture the database manager's monitor switch settings:
SELECT * FROM TABLE( SNAPSHOT_SWITCHES(-1)) as SNAPSHOT_SWITCHES

--Database: To capture a snapshot of database information:
SELECT * FROM TABLE( SNAPSHOT_DATABASE( 'SAMPLE', -1 )) as SNAPSHOT_DATABASE

--Application:To capture a snapshot of application information:
SELECT * FROM TABLE( SNAPSHOT_APPL( 'SAMPLE', -1 )) as SNAPSHOT_APPL

--To capture a snapshot of application identification information:Chapter 3. Using the Snapshot Monitor 23
SELECT * FROM TABLE( SNAPSHOT_APPL_INFO( 'SAMPLE', -1 )) as SNAPSHOT_APPL_INFO

--To capture a snapshot of lock wait information:
SELECT * FROM TABLE( SNAPSHOT_LOCKWAIT('SAMPLE', -1 )) as SNAPSHOT_LOCKWAIT

--To capture a snapshot of statement information:
SELECT * FROM TABLE( SNAPSHOT_STATEMENT( 'SAMPLE', -1 )) as SNAPSHOT_STATEMENT

--Dynamic SQL cache: To capture a snapshot of dynamic SQL statement cache information:Snapshot monitor 24 System Monitor Guide and Reference
SELECT * FROM TABLE( SNAPSHOT_DYN_SQL( 'SAMPLE', -1 )) as SNAPSHOT_DYN_SQL

--To capture a snapshot of agent information:
SELECT * FROM TABLE( SNAPSHOT_AGENT( 'SAMPLE', -1 )) as SNAPSHOT_AGENT

--To capture a snapshot of subsection information:
SELECT * FROM TABLE( SNAPSHOT_SUBSECT( 'SAMPLE', -1 )) as SNAPSHOT_SUBSECT

--Buffer pool: To capture a snapshot of buffer pool information:
SELECT * FROM TABLE( SNAPSHOT_BP( 'SAMPLE', -1 )) as SNAPSHOT_BP

--Table space:To capture a snapshot of table space information:
SELECT * FROM TABLE( SNAPSHOT_TBS( 'SAMPLE', -1 )) as SNAPSHOT_TBS

--To capture a snapshot of table space configuration information:
SELECT * FROM TABLE( SNAPSHOT_TBS_CFG( 'SAMPLE', -1 )) as SNAPSHOT_TBS_CFG

--To capture a snapshot of table space quiescer information:
SELECT * FROM TABLE( SNAPSHOT_QUIESCER( 'SAMPLE', -1 )) as SNAPSHOT_QUIESCER

--To capture a snapshot of table space container configuration information:
SELECT * FROM TABLE( SNAPSHOT_CONTAINER( 'SAMPLE', -1 )) as SNAPSHOT_CONTAINER

--To capture a snapshot of the ranges for a table space map:
SELECT * FROM TABLE( SNAPSHOT_RANGES( 'SAMPLE', -1 )) as SNAPSHOT_RANGES

--Table: To capture a snapshot of table information:
SELECT * FROM TABLE( SNAPSHOT_TABLE( 'SAMPLE', -1 )) as SNAPSHOT_TABLE

--Lock: To capture a snapshot of lock information:
SELECT * FROM TABLE( SNAPSHOT_LOCK( 'SAMPLE', -1 )) as SNAPSHOT_LOCK

<<command.reference.pdf>.
db2_all.bat
db2_kill.bat
db2am.bat
db2ca.bat
db2cc.bat
db2cctrc.bat
db2ce.bat
db2cos.bat
db2cos_datacorruption.bat
db2cos_hang.bat
db2cos_perf.bat
db2cos_trap.bat
db2cw.bat
db2cwadmin.bat
db2env.bat
db2envar.bat
db2eva.bat
db2hc.bat
db2ic.bat
db2indbt.bat
db2journal.bat
db2lc.bat
db2memvis.bat
db2rc.bat
db2setcp.bat
db2tc.bat
db2unins.bat
db2vsregister.bat
db2vsrgx.bat
db2vsurgx.bat
db2wrapperconfig.bat
dxxadmin.cmd
asnacmd.exe
asnadmt.exe
asnanalyze.exe
asnapply.exe
asncap.exe
asnccmd.exe
asnclp.exe
ASNDONE.exe
ASNLOAD.exe
asnmail.exe
asnmcmd.exe
asnmig4c.exe
asnmig8.exe
asnmigpw.exe
asnmon.exe
asnpwd.exe
asnqacmd.exe
asnqanalyze.exe
asnqapp.exe
asnqcap.exe
asnqccmd.exe
asnqmfmt.exe
asnscrt.exe
asnsdrop.exe
asnserv.exe
asnslist.exe
asntdiff.exe
asntrc.exe
asntrcbp.exe
asntrep.exe
cc390ve.exe
dasmigr.exe
dasupdt.exe
db2.exe
db2_aram.exe
db2admin.exe
db2adutl.exe
db2advis.exe
db2audit.exe
db2batch.exe
db2bddbg.exe
db2bddbgsvc.exe
db2bfd.exe
db2bp.exe
db2cap.exe
db2cat.exe
db2cfexp.exe
db2cfimp.exe
db2ckbkp.exe
db2ckrst.exe
db2clpex.exe
db2clpsetcp.exe
db2cmd.exe
db2cmdadmin.exe
db2cmnclp.exe
db2cmnseh.exe
db2dart.exe
db2dascln.exe
db2daslevel.exe
db2dasrrm.exe
db2dasstm.exe
db2dclgn.exe
db2demigdbd.exe
db2diag.exe
db2disable_soap_udf.exe
db2drdat.exe
db2drvmp.exe
db2empfa.exe
db2enable_soap_udf.exe
db2envgen.exe
db2evmon.exe
db2evtbl.exe
db2exfmt.exe
db2exmig.exe
db2expln.exe
db2extsec.exe
db2flsn.exe
db2fmp.exe
db2fodc.exe
db2fopt.exe
db2fs.exe
db2gcf.exe
db2genp.exe
db2getinstalllog.exe
db2gov.exe
db2govd.exe
db2govds.exe
db2govlg.exe
db2gpmap.exe
db2icdocs.exe
db2iclus.exe
db2icons.exe
db2icrt.exe
db2idrop.exe
db2igp.exe
db2ilist.exe
db2imaudcfg.exe
db2imdbd.exe
db2imigr.exe
db2imnod.exe
db2inidb.exe
db2inspf.exe
db2iupdt.exe
db2javit.exe
db2javitCmd.exe
db2jcc.exe
db2jdbcbind.exe
db2ldcfg.exe
db2ldreg.exe
db2level.exe
db2licd.exe
db2licm.exe
db2listvolumes.exe
db2logsForRfwd.exe
db2look.exe
db2lspwd.exe
db2lswtch.exe
db2lueff.exe
db2mgmtsvc.exe
db2move.exe
db2mqCheck.exe
db2mqlsn.exe
db2mscs.exe
db2mtrk.exe
db2nchg.exe
db2ncrt.exe
db2ndmgr.exe
db2ndrop.exe
db2ndscv.exe
db2nkill.exe
db2nlist.exe
db2nmpcfg.exe
db2nmpsetup.exe
db2nstck.exe
db2pd.exe
db2pdcfg.exe
db2pdraw.exe
db2perfc.exe
db2perfi.exe
db2perfr.exe
db2pgixt.exe
db2profc.exe
db2profp.exe
db2rappl.exe
db2ras.exe
db2rbind.exe
db2rcmd.exe
db2rcmdc.exe
db2recri.exe
db2regfile.exe
db2reggv.exe
db2regsc.exe
db2relocatedb.exe
db2remot.exe
db2rfpen.exe
db2rspgn.exe
db2sampl.exe
db2schex.exe
db2se.exe
db2sec.exe
db2secv82.exe
db2set.exe
db2setres.exe
db2sqljbind.exe
db2sqljcustomize.exe
db2sqljprint.exe
db2star2.exe
db2start.exe
db2stat.exe
db2stop.exe
db2stop2.exe
db2support.exe
db2swtch.exe
db2swtchg.exe
db2sysc.exe
db2syscs.exe
db2systray.exe
db2tapemgr.exe
db2tbst.exe
db2tdbmgr.exe
db2trc.exe
db2trcd.exe
db2trcsc.exe
db2uiddl.exe
db2untag.exe
db2usvce.exe
db2vshelpreg.exe
db2vsreg.exe
db2wolfi.exe
db2xdbmig.exe
db2xdbsg.exe
db2xprt.exe
ddcspkgn.exe
ddcstrc.exe
disable_MQFunctions.exe
disable_MQXML.exe
dxxadm.exe
dxxgenx.exe
dxxgenxb.exe
dxxgenxc.exe
dxxisrt.exe
dxxlevel.exe
dxxMigv.exe
dxxretr.exe
dxxretr2.exe
dxxretrc.exe
dxxshrd.exe
dxxtrc.exe
dynexpln.exe
enable_MQFunctions.exe
enable_MQXML.exe
gseidx.exe
insertx.exe
killdb2.exe
ktdscvr.exe
pctn.exe
pctt.exe
profconv.exe
profdb.exe
profp.exe
rah.exe
retrieve.exe
retrieve2.exe
retrievec.exe
sdscvr.exe
shred.exe
sqlj.exe
strtstep.exe
tbscont.exe
testconn11.exe
testconn20.exe
tests2x.exe
tests2xb.exe
tests2xc.exe