How to quickly locate lock wait statements in DB2

In daily DB2 O & M, we often encounter slow execution of Several SQL statements, but the cpu and memory usage of the database server is not high, similar problems are basically caused by locks, sorting, and other reasons. Next we will analyze how to quickly locate the lock wait problem from an instance and lock the table.

I. Lock information in logs

When you find a large amount of sqlcode reports 911 information in the database LOG DB2DIAG. LOG, there is usually a lock wait problem.

2014-01-03-10.05.26.616285 + 480 I4936115E839 LEVEL: Severe

PID: 16724 TID: 47174238857536 PROC: db2sysc 0

INSTANCE: db2inst1 NODE: 000 DB: PORTALDB

APPHDL: 0-47283 APPID: 10.4.125.115.38673.14020.201531

AUTHID: IPORTAL

EDUID: 88 EDUNAME: db2agent (PORTALDB) 0

FUNCTION: DB2 UDB, relation data serv, sqlrr_array_input, probe: 210

DATA #1: SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes

Sqlcaid: SQLCA sqlcabc: 136 sqlcode:-911 sqlerrml: 1

Sqlerrmc: 2

Sqlerrp: SQLRI2C4

Sqlerrd: (1) 0x80100002 (2) 0x00000002 (3) 0x00000000

(4) 0x00000000 (5) 0xFFFFFE0C (6) 0x00000000

Sqlwarn: (1) (2) (3) (4) (5) (6)

(7) (8) (9) (10) (11)

Ii. DB2PD analysis lock wait

The db2pd tool is mainly used to collect statistical information of DB2 instances and databases. The biggest advantage of db2pd is that information is obtained quickly without consuming Database Engine resources. To use this powerful tool, you must have SYSADMIN permissions and be the instance owner in UNIX or Linux environments.

$ Db2pd-db portaldb-app | more # obtain application information

Database Partition 0 -- Database PORTALDB -- Active -- Up 17 days 07:04:15 -- Date 2014-01-10-15.33.14.983796

Applications:

Address AppHandl [nod-index] NumAgents CoorEDUID Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid

WorkloadID WorkloadOccID CollectActData CollectActPartition CollectSectionActuals

0x0000000201050080 26 [000-00026] 1 61 ConnectCompleted 0 0 0 * LOCAL. DB2.131224002909

0 0 N C N

0x0000000201450080 47914 [000-47914] 1 104 UOW-Waiting 0 0 677 41 10.4.125.114.11658.140110014742

1 3694 N C N

0x0000000200F80080 19 [000-00019] 1 54 UOW-Waiting 0 0 0 0 * LOCAL. DB2.131224002902

0 0 N C N

For further analysis, find the anchid with applid 47914

$ Db2pd-db portaldb-app | grep-I "47914"

0x0000000201450080 47914 [000-47914] 1 104 UOW-Waiting 0 0 98 1 10.4.125.114.11658.140110014742 1 3694 N C N

0x0000000201450080 47914 [000-47914] 10.4.125.114 None IPORTAL

0x0000000201450080 47914 [000-47914] n/

-- Anchid is 98
-- Then, use the db2pd-dynamic function to find the SQL statement that is being executed corresponding to the 47914 Application ID according to anch = 98 (applicable to v9 and later versions, v8 db2pd-dynamic does not have anch =)

$ Db2pd-db portaldb-dynamic anch = 98 | more

System Temp Table Stats:

Number of System Temp Tables: 69817

Comp Eligible Sys Temps: 0

Compressed Sys Temps: 0

Total Sys Temp Bytes Stored: 16140381976

Total Sys Temp Bytes Saved: 0

Total Sys Temp Compressed Rows: 0

Total Sys Temp Table Rows: 1134615144

User Temp Table Stats:

Number of User Temp Tables: 720

Comp Eligible User Temps: 0

Compressed User Temps: 0

Total User Temp Bytes Stored: 90045

Total User Temp Bytes Saved: 0

Total User Temp Compressed Rows: 0

Total User Temp Table Rows: 551

Database Partition 0 -- Database PORTALDB -- Active -- Up 17 days 07:06:14 -- Date 2014-01-03-15.35.13.996815

Dynamic Cache:

Current Memory Used 733842896

Total Heap Size 1675218370

Cache Overflow Flag 0

Number of References 19142165

Number of Statement Inserts 151614

Number of Statement Deletes 116009

Number of Variation Inserts 116930

Number of Statements 35605

Dynamic SQL Statements:

Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text

0x00002AEA103CF0C0 98 90 1 1 1 1 select count (*) as col_0_0 from KC_USER user0 _ where user0 _. USER_ID = 349289

--- You can also use the method of obtaining snapshots to see what SQL is being executed by the application with the current applid of 47914

$ Db2 get snapshot for application agentid 47914

When you find that such a query statement has a lock, you can immediately terminate the lock.

The method is:

$ Db2 "force application (47914 )"

Note: Later, I contacted the developer to know that they are modifying the KC_USER table and have not submitted the table for a long time. As a result, a large number of locks related to the table are generated. The problem is resolved after the transaction is committed.

Summary:

Through the above instance analysis, we briefly described a db2 lock problem and statement locating method, hoping to help you in analyzing and locating application performance problems.

This article is from the blog "dishui tushi Sun Jie" and will not be reposted!