Retour
-
Titre
What is the procedure to identify the blocking sql for Performance Analysis for Db2? -
Description
The user has configured Performance Analysis for DB2 on one of her/his Db2 database on Windows server. The user can see the SQLs experiencing locking issue, by selecting "Resource" as locks in the History screen. In the "blocking locks" tab, the user could see the contenting Object/Application name/Client user/Client workstation, but it doesn't show the contending SQL. Is there a way to grab the contending Sqls that holds the lock via PA ? There are so many Sqls ran by the combination Application name/Client user/Client workstation, so it was difficult to identify the actual contenting one. -
Résolution
Best practice to identify the blocking sql statement from Real Time window instead of the History window:
1) Under History option based on your screenshot, there are no Sidsthat gets retained that correlates with the blocking sqls.This is by designed.The user could only narrow down to the time when she/he thought thereis a blocking sqls under History.2) However, under Real Time option next to the History optionfrom the Performance Analysis GUI client, this does retain the Sids of theblocking sql which helps to identify the blocking sql statement in the Real time mode.In Real-Time mode, the Blocking Locks tab displays alllocks that have occurred and are occurring in the currentperiod. This tool facilitates an at-a-glance,in-depth viewing of system locking activity. Use it toidentify sessions causing excessive lock problems.Expanding the blocking session by clicking on the + icon displays the set of individuallocks created by the blocking session. Only locks which actually resulted in a block areincluded. The lock icon indicates a currently active lock.The displayed metrics are as follows:Agent ID - Agent ID of the blocked session. Click the Agent ID to drill downinto the blocked session and trace its activity.Database - The database where the blocked session is running.Mode Wanted - The blocking mode for a SQL statement that is currently blockedLock Type - Type of blocking lock.Lock Time - Date and time when the blocking began.Lock Status - Status of blocking lock.Locked Server PID - The Process Identification (PID) of the blocked session.The user could also click on F1 function key on the Window where locks are located to get more information.