實例：解決SQL911 RC=68 lock timeout 問題：
客戶抱怨數據庫應用程序經常會SQL911 RC=68錯誤退出。希望找出原因。
server是FP10，因此不大可能是APAR
思路：
我們知道SQL911 RC=68是lock timeout，因為lock timeout和deadlock不同，沒有event monitor可以抓到它，我們只能根據TIMEOUT VALUE來寫腳本循環作snapshot……
然後就要決定究竟什麼snapshot對我們有幫助
首先snapshot for applications , snapshot for locks不用說了，然後由於已經是8.2了，我們可以用db2pd獲取更加詳細的信息。
客戶的locktimeout=60，所以有腳本如下：
while [ 1 -eq 1 ];
do
wc= $(db2 "list applications show detail" | grep -i "Lock-Wait" | wc -l)
if [ $wc -gt 0 ]; then
db2pd -db dwbllog -locks showlocks > 58121.locks
db2pd -db dwbllog -transactions > 58121.trans
db2pd -db dwbllog -agents > 58121.agents
db2pd -db dwbllog -applications > 58121.app
db2 "get snapshot for locks on dwbllog global" > 58121.locksnap
db2 "get snapshot for applications on dwbllog global" > 58121.aPPSnap
db2pd -db dwbllog -locks showlocks > 58121.locks.2
print "Done snapshots"
fi
sleep 10
done
經過2個小時lock timeout再次出現，客戶拿到最後一次的snapshot，並且出錯退出進程為66681
分析：
首先自然是看application snapshot:
Application Snapshot
Application handle = 66681
Application status = Lock-wait
Status change time = 04/19/2006 09:54:40.879548
Application code page = 1208
Application country/region code = 1
DUOW correlation token = G93D0D4A.AF0E.0DE779133109
Application name = Java
Application ID = G93D0D4A.AF0E.0DE779133109
Sequence number = 0001
TP Monitor clIEnt user ID =
TP Monitor clIEnt workstation name =
TP Monitor clIEnt application name =
TP Monitor clIEnt accounting string =
。。。。
這裡Application status = Lock-wait看起來沒有錯誤，繼續往下：
Subsection waiting for lock = 0
ID of agent holding lock = 65836
Application ID holding lock = G93D0E35.A0E8.057C39131247
Database partition lock wait occurred on = 1
Lock name = 0x53595353483230320619C45A41
Lock attributes = 0x00000000
Release flags = 0x40000000
Lock object type = Internal Plan Lock
Lock mode = Exclusive Lock (X)
Lock mode requested = Share Lock (S)
Lock wait start timestamp = 04/19/2006 09:54:40.879549
看出我們正在等65836的一個X lock，這裡S等X很正常，不過Internal Plan Lock的X就比較有疑問了。
我們去65836的locksnapshot看看：
Application handle = 65836
Application ID = G93D0E35.A0E8.057C39131247
Sequence number = 0001
Application name = Java
CONNECT Authorization ID = WCONN
Application status = UOW Waiting
Status change time = 04/19/2006 09:55:04.481159
Application code page = 1208
Locks held = 56
Total wait time (ms) = 0
List Of Locks
Database partition = 1
Lock Name = 0x5359534C48323030BB989F3241
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 2
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = X
Database partition = 1
Lock Name = 0x53595353483230313F94F89E41
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S
Database partition = 1
Lock Name = 0x53514C4445464C540763DD2841
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S
Database partition = 1
Lock Name = 0x53595353483230320619C45A41
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 2
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = X
Database partition = 1
Lock Name = 0x535953534832303028EFECDC41
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S
Database partition = 2
Lock Name = 0x535953534832303028EFECDC41
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S
。。。。（還有其他的N多lock）
我們看到有很多X的Internal Plan Lock，很有意思，從文檔中我們知道
- If one attempts an operation that either Binds/Rebinds a package, drops a package, invalidates a package or inOperates a package, the package lock for that package will be acquired in Exclusive mode also for the duration of the unit of work. So, for example if one or more applications are running from a package A, and an attempt is made from another application to drop package A, then the application issuing the drop will be attempting to acquire an X lock on that package, but will have to wait behind the applications that are executing from that package as they have S locks on that package
所以4種可能：
1）正在bind
2）正在drop package
3）invalidate package
4）inOperates a package
我們看一看到底是哪個lock產生的問題。
在db2pd -application中我們找到66681：
0x07800000003C1C00 66681 [001-01145] 1 3829886 Lock-wait 0 0 108 895 G93D0D4A.AF0E.0DE779133109
然後db2pd -transaction裡的66681：
404C8600 66681 [001-01145] 67 4 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0 0 0x0000350BE23A 1 n/a
得到transaction handle 67
在db2pd -lock中有以下4個lock
0x07800000413C77C0 67 53595353483230313F94F89E41 Internal P ..S G 67 1 0 0x0000 0x40000000 Pkg UniqueID 53595353 48323031 Name 3f94f89e Loading = 0
0x07800000411FC980 67 53514C4445464C540763DD2841 Internal P ..S G 67 1 0 0x0000 0x40000000 Pkg UniqueID 53514c44 45464c54 Name 0763dd28 Loading = 0
0x0780000041235C00 67 53595353483230320619C45A41 Internal P ..S W 30 1 0 0x0000 0x40000000 Pkg UniqueID 53595353 48323032 Name 0619c45a Loading = 0
0x078000004115E0C0 67 535953534832303028EFECDC41 Internal P ..S G 67 1 0 0x0000 0x40000000 Pkg UniqueID 53595353 48323030 Name 28efecdc Loading = 0
正好對應db2pd -transaction裡的4
然後同樣步驟找到65836的lock。
由於65836的lock很多，我們只需要找到Internal P的X lock：
只有兩個：
0x078000004123E200 30 53595353483230320619C45A41 Internal P ..X G 30 2 0 0x0000 0x40000000 Pkg UniqueID 53595353 48323032 Name 0619c45a Loading = 0
0x0780000041241300 30 5359534C48323030BB989F3241 Internal P ..X G 30 2 0 0x0000 0x40000000 Pkg UniqueID 5359534c 48323030 Name bb989f32 Loading = 0
然後對比發現0619c45a 在兩個lock裡都存在，因而可以斷定是這個引起的問題。
然後檢查pckage ID: 53595353 48323032
0x53 0x59 0x53 0x53 0x48 0x32 0x30 0x32
在ASCII中是SYSSH202
因此，現在做
select pkgname,last_bind_time from syscat.packages
得到3個SYSSH202
SYSSH202 2006-04-19-10.17.53.372088
SYSSH202 2005-11-15-20.40.59.511091
SYSSH202 2005-11-15-20.34.19.734507
然後做：
select * from syscat.packages where pkgname='SYSSH202'
得到：
PKGSCHEMA PKGNAME BOUNDBY DEFINER DEFAULT_SCHEMA VALID UNIQUE_ID TOTAL_SECT FORMAT ISOLATION BLOCKING INSERT_BUF LANG_LEVEL FUNC_PATH QUERYOPT EXPLAIN_LEVEL EXPLAIN_MODE EXPLAIN_SNAPSHOT SQLWARN SQLMATHWARN EXPLICIT_BIND_TIME LAST_BIND_TIME CODEPAGE DEGREE MULTINODE_PLANS INTRA_PARALLEL VALIDATE DYNAMICRULES SQLERROR REFRESHAGE REMARKS FEDERATED TRANSFORMGROUP REOPTVAR OS_PTR_SIZE PKGVERSION PKG_CREATE_TIME
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----- --------- ---------- ------ --------- -------- ---------- ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ------------- ------------ ---------------- ------- ----------- -------------------------- -------------------------- -------- ------ --------------- -------------- -------- ------------ -------- ---------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------- ---------------------------------------------------------------- --------------------------
NULLID SYSSH202 DWBLLOG DWBLLOG DWBLLOG Y SYSLVL01 65 3 CS B N 0 "SYSIBM","SYSFUN","SYSPROC","DWBLLOG" 5 N N Y N 2004-02-14-16.58.21.045669 2006-04-19-10.17.53.372088 819 1 Y N B R N 0.000000 - U - N 64 2004-02-14-16.58.21.045669
NULLIDR1 SYSSH202 DWBLLOG DWBLLOG DWBLLOG Y SYSLVL01 65 3 CS B N 0 "SYSIBM","SYSFUN","SYSPROC","DWBLLOG" 7 N N Y N 2005-11-15-20.34.19.734507 2005-11-15-20.34.19.734507 819 ANY Y N B R N 0.000000 - U - O 32 2005-11-15-20.34.19.734507
NULLIDRA SYSSH202 DWBLLOG DWBLLOG DWBLLOG Y SYSLVL01 65 3 CS B N 0 "SYSIBM","SYSFUN","SYSPROC","DWBLLOG" 7 N N Y N 2005-11-15-20.40.59.511091 2005-11-15-20.40.59.511091 819 ANY Y N B R N 0.000000 - U - A 32 2005-11-15-20.40.59.511091
看來NULLID schema下的package的最新綁定時間是錯誤附近，因而我們可以想象估計是綁定包問題。
詢問客戶得知最近他們從32bit省級到64bit，然後讓客戶運行：
db2 bind @db2ubind.lst
db2 bind @db2cli.lst
問題解決
後備解決方案：
DB2_NO_PKG_LOCK=ON
但是產生的副作用過於嚴重，不推薦
