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!