In the DB2 command line, enter:
Update monitor switches using lock on table
Then open another DB2 command window to execute my hanged UPDATE statement.
Then execute in the First DB2 command window: [@[email protected]]get snapshotfor locks on database_name (name of your database) > locks. Txt
Then, you can see that the first DB2 window has an information output that outputs this information to TXT, roughly as follows:
Application Handle =36
Application identity = ac100c47. ic05.00f6c6095828
Serial =0246
Application name =java.exe
CONNECT Authorization ID =db2admin
Application state = UOW is waiting
state change time = not collected
Application code page =1208
Pending lock =0
Total wait time (milliseconds) =0
Application Handle =43
Application Identity =*local. DB2.060512054331
Serial =2273
Application name =java.exe
CONNECT Authorization ID =db2admin
Application state = Federated request pending
state change time = not collected
Application code page =1208
Pending lock =6
Total wait time (milliseconds) =0
Lock List
Lock name = 0x031f9052000000000000000055
Lock Property =0x00000000
Release logo =0x40000000
Lock Count =255
Suspend count = 0
Lock Object Name =0
Object type = Internal
mode = S
Lock name =0x26800000000000000000000044
Lock Property =0x00000000
Release logo =0x40000000
Lock count = 1
Suspend count = 0
Lock Object Name =0
Object type = Internal
mode = S
Lock name = 0x020006000f1700000000000052
Lock Property =0x00000000
Release logo =0x00000001
Lock count = 1
Suspend count = 0
Lock Object Name =5903
Object type = row
Table Space Name =userspace1
Table Mode =db2admin
Table name =c_user
mode = NS
Lock name =0x01000000010000000500bc0056
Lock Property =0x00000000
Release logo =0x40000000
Lock count = 1
Suspend count = 0
Lock Object Name =0
Object type = Internal change lock
mode = S
Lock name =0x535953534e333030fd965c0641
Lock Property =0x00000000
Release logo =0x40000000
Lock count = 1
Suspend count = 0
Lock Object Name =0
Object type = Internal Scheme lock
mode = S
Lock name =0x02000600000000000000000054
Lock Property =0x00000000
Release logo =0x00000001
Lock count = 1
Suspend count = 0
Lock Object Name =6
Object type = Table
Table Space Name =userspace1
Table Mode =db2admin
Table name =c_user
mode = is
Application Handle =557
Application Identity =*local. DB2.060512053913
Serial =1254
Application name =java.exe
CONNECT Authorization ID =db2admin
Application state = Federated request pending
state change time = not collected
Application code page =1208
Pending lock =6
Total wait time (milliseconds) =0
Lock List
Lock name = 0x031f9052000000000000000055
Lock Property =0x00000000
Release logo =0x40000000
Lock Count =255
Suspend count = 0
Lock Object Name =0
Object type = Internal
mode = S
Lock name =0x26800000000000000000000044
Lock Property =0x00000000
Release logo =0x40000000
Lock count = 1
Suspend count = 0
Lock Object Name =0
Object type = Internal
mode = S
Lock name =0x02000600071d00000000000052
Lock Property =0x00000000
Release logo =0x00000001
Lock count = 1
Suspend count = 0
Lock Object Name =7431
Object type = row
Table Space Name =userspace1
Table Mode =db2admin
Table name =c_user
mode = NS
Lock name =0x01000000010000000500bc0056
Lock Property =0x00000000
Release logo =0x40000000
Lock count = 1
Suspend count = 0
Lock Object Name =0
Object type = Internal change lock
mode = S
Lock name =0x535953534e333030fd965c0641
Lock Property =0x00000000
Release logo =0x40000000
Lock count = 1
Suspend count = 0
Lock Object Name =0
Object type = Internal Scheme lock
mode = S
Lock name =0x02000600000000000000000054
Lock Property =0x00000000
Release logo =0x00000001
Lock count = 1
Suspend count = 0
Lock Object Name =6
Object type = Table
Table Space Name =userspace1
Table Mode =db2admin
Table name =c_user
mode = is
Where the state of application handles 43 and 557 are deadlocked, guess is that these 2 applications contend with DB2 tables, causing deadlocks, according to the log prompts, in the DB2 command Window input:
Force application (43)
Force application (557)
Prompt This operation is asynchronous, I execute listapplicaions, the result process also has the 2 processes, that 2 processes may be performing the larger operation, need to wait patiently, how not yet, use the following command to force all applications to stop, and then restart DB2:
Force application All
Terminate
Db2stop Force
Db2start
If DB2 is on a window, you can use the right-click application, such as control center, to see the current locking situation, and to forcibly close a process or to display a lock chain.
DB2 how to unlock a database table