LOCK AVOIDANCE

book

Article ID: 110084

calendar_today

Updated On:

Products

CA Bind Analyzer for DB2 for z/OS CA SQL-Ease for DB2 for z/OS CA Sysview Performance Management Option for DB2 for z/OS CA Plan Analyzer for DB2 for z/OS CA Subsystem Analyzer for DB2 for z/OS

Issue/Introduction

How to get information about LOCK AVOIDANCE ?

Environment

Release:
Component: CIDB

Resolution

Due to the high overhead of collecting this information, Sysview Performance for DB2 doesn't have any capture of this data enabled by default. But, if you want to see this information, you can enable the RECTRACE request (=8.2.6.7 using the UI menu) and it will show you this data. Here is what you will see for each IFCID 223 produced: 

R/RECTRACE DETAIL TRACE OF DB2 ACTIVITY Row 279-309/27665 

Event Time Event Dur Event CPU Rtrn Lk Lck Lock Req/Ret Lock Req Lock 
Corr-ID Planname MM:SS.TTTT SS.TTTT Event zIIP Event Action Code Dr Ste Type Token Type Scope 
-------- -------- ---------- --------- ---------- ------------- ---- -- --- ---- -------- -------- ------ 
db2jcc_a DISTSERV 22:58.4391 CMT LSN TEST Database: DSNDB06 Pageset: SYSTSTSP Pg#: 0000001D 

and for each IFCID 218: 

R/RECTRACE DETAIL TRACE OF DB2 ACTIVITY Row 7940-7970/27665 

Event Time Event Dur Event CPU Rtrn Lk Lck Lock Req/Ret Lock Req Lock 
Corr-ID Planname MM:SS.TTTT SS.TTTT Event zIIP Event Action Code Dr Ste Type Token Type Scope 
-------- -------- ---------- --------- ---------- ------------- ---- -- --- ---- -------- -------- ------ 
db2jcc_a DISTSERV 22:58.4536 CMT LSN UOW Successful Test: Y 
Database: DSNDB06 Pageset: SYSUSER Successful Test: Y 
Database: DSNDB06 Pageset: SYSTSDBU Successful Test: N 
Database: DSNDB06 Pageset: SYSTSDBA Successful Test: N 
Database: DSNDB06 Pageset: SYSTSTAU Successful Test: N 
Database: DSNDB06 Pageset: SYSSTATS Successful Test: N 
Database: DSNDB06 Pageset: SYSTSFLD Successful Test: N 
Database: DSNDB06 Pageset: SYSTSKEY Successful Test: N 
Database: DSNDB06 Pageset: SYSTSIXS Successful Test: N 
Database: DSNDB06 Pageset: SYSTSTSP Successful Test: Y 
Database: DSNDB06 Pageset: SYSTSCOL Successful Test: N 
Database: DSNDB06 Pageset: SYSTSTAB Successful Test: Y 
Database: DSNDB06 Pageset: SYSTSQRY Successful Test: N 
Database: DSNDB06 Pageset: SYSTSPKS Successful Test: N 

Be CAREFUL  not to let this request run all the time. The overhead is quite high.