Shulou(Shulou.com)06/01 Report--

This article mainly shows you "how to quickly locate lock waiting statements in DB2". The content is simple and easy to understand, and the organization is clear. I hope it can help you solve your doubts. Let Xiaobian lead you to study and learn this article "how to quickly locate lock waiting statements in DB2".

In DB2 daily operation and maintenance, we often encounter a few SQL execution is very slow, but the database server CPU and memory usage is not high, similar problems are basically caused by locking, sorting and other reasons. Here's an example of how to quickly locate the lock wait problem and lock in that table.

1. Information locked in logs

When you find a large number of sqlcode 911 messages in the database log DB2DIAG.LOG, there is usually a lock waiting problem.

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

PID : 16724 TID : 47174238857536PROC : db2sysc 0

INSTANCE: db2inst1 NODE : 000 DB : PORTALDB

APPHDL : 0-47283 APPID: 10.4.125.115.38673.140109201531

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)

DB2PD analysis lock waiting

The db2pd tool is mainly used to collect statistical information about DB2 instances and databases. The biggest advantage of db2pd is that information is obtained very quickly and does not need to consume database engine resources. To use this powerful tool, you must have SYSADMIN privileges and be the owner of the instance in a UNIX or Linux environment.

$db2pd -db portaldb -app| more #Get app info

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 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

Further analysis found an 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/a

--anchid is 98

--Then use db2pd -dynamic function to find out the executing sql corresponding to the 47914 application id number according to anch =98 (applicable to versions above v9, db2pd -dynamic without anch= ** option in v8 version)

$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 snapshot method to see what SQL is being executed by the application whose current applid is 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: I contacted the developers later and knew that they were modifying the KC_USER table and had not submitted it for a long time, so there were a lot of locks on this table. After the transaction is committed, the problem is resolved.

The above is "DB2 how to quickly locate the lock waiting statement" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.