linux db2数据库锁表,如何解锁DB2中被锁定的表？

usually for such problem it always looks like either lock timeout or deadlock.

you can use db2pd -catch to capture the error and dump the snapshot information when the error occured.

that will clearly show you which lock caused the problem.

for more information about db2pd -catch please search on infocenter

thanks

http://publib.boulder.ibm.com/in ... 3%61%74%63%68%22%20

#

Step-by-step instructions to catch a lock timeout.

Copy the db2cos template into sqllib.

cp sqllib/cfg/db2cos sqllib/db2cos

Set the error catch setting. You can use -911,68, -911, or locktimeout. If fact, if you know the lock type or lock name in advance, you can use use the locktype or lockname suboptions to filter out unwanted lock catches.

db2pd -catch locktimeout count=1

Error Catch #1

Sqlcode: 0

ReasonCode: 0

ZRC: -2146435004

ECF: 0

Component ID: 0

LockName: Not Set

LockType: Not Set

Current Count: 0

Max Count: 1

Bitmap: 0xA1

Action: Error code catch flag enabled

Action: Execute sqllib/db2cos callout script

Reproduce a lock timeout.

db2 update staff set id = 0

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0911N The current transaction has been rolled back because of a deadlock

or timeout. Reason code "68". SQLSTATE=40001

Notice the db2diag.log tells us what happened.

2005-01-06-15.31.59.017134-300 I416442C274 LEVEL: Event

PID : 9875676 TID : 1 PROC : db2pd

INSTANCE: db2inst1 NODE : 000

FUNCTION: DB2 UDB, RAS/PD component, pdErrorCatch, probe:30

START : Error catch set for ZRC -2146435004

2005-01-06-15.36.07.084657-300 I416717C403 LEVEL: Event

PID : 10379460 TID : 1 PROC : db2agent (SAMPLE)

INSTANCE: db2inst1 NODE : 000 DB : SAMPLE

APPHDL : 0-71 APPID: *LOCAL.db2inst1.050106202920

FUNCTION: DB2 UDB, trace services, pdInvokeCalloutScript, probe:10

START : Invoking sqllib/db2cos script from lock manager sqlplnfd

2005-01-06-15.36.09.274708-300 I417121C386 LEVEL: Event

PID : 10379460 TID : 1 PROC : db2agent (SAMPLE)

INSTANCE: db2inst1 NODE : 000 DB : SAMPLE

APPHDL : 0-71 APPID: *LOCAL.db2inst1.050106202920

FUNCTION: DB2 UDB, trace services, pdInvokeCalloutScript, probe:20

STOP : Completed invoking sqllib/db2cos script

Now look in sqllib/db2dump/db2cos.rpt which is the default output file that contains the db2pd output generated when the error was caught.

Lock Timeout Caught

Thu Jan 6 15:36:07 EST 2005

Instance db2inst1

Datbase: SAMPLE

Partition Number: 0

PID: 10379460

TID: 1

Function: sqlplnfd

Component: lock manager

Probe: 999

Timestamp: 2005-01-06-15.36.07.084474

AppID: *LOCAL.db2inst1.050106202920

AppHdl:

Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:06:53

Locks:

Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att Rlse

0x402C6B30 3 00020003000000040000000052 Row ..X W* 3 1 0 0 0x40

Just look for the "W*" as this is the lock that experienced the timeout. You can map this to a transaction, application, agent, and even SQL statement with all of the db2pd output provided. You can get strategic and narrow down the output or use other commands to collect the information you need. For example, you could change the db2pd options to use the "-locks wait" option that only prints locks with a wait status and their waiter. You could also put in -app, and -agent options if that's what you need.

"LOCKTIMEOUT&quot

echo "Lock Timeout Caught" >> $HOME/sqllib/db2dump/db2cos.rpt

date >> $HOME/sqllib/db2dump/db2cos.rpt

echo "Instance " $instance >> $HOME/sqllib/db2dump/db2cos.rpt

echo "Datbase: " $database >> $HOME/sqllib/db2dump/db2cos.rpt

echo "Partition Number:" $dbpart >> $HOME/sqllib/db2dump/db2cos.rpt

echo "PID: " $pid >> $HOME/sqllib/db2dump/db2cos.rpt

echo "TID: " $tid >> $HOME/sqllib/db2dump/db2cos.rpt

echo "Function: " $function >> $HOME/sqllib/db2dump/db2cos.rpt

echo "Component: " $component >> $HOME/sqllib/db2dump/db2cos.rpt

echo "Probe: " $probe >> $HOME/sqllib/db2dump/db2cos.rpt

echo "Timestamp: " $timestamp >> $HOME/sqllib/db2dump/db2cos.rpt

echo "AppID: " $appid >> $HOME/sqllib/db2dump/db2cos.rpt

echo "AppHdl: " $apphld >> $HOME/sqllib/db2dump/db2cos.rpt

db2pd -db $database >> $HOME/sqllib/db2dump/db2cos.rpt

db2pd -db $database -locks wait -fmtlock -app -age

>> $HOME/sqllib/db2dump/db2cos.rpt

;;