DB2 10.5 for Linux, UNIX, and Windows

LOCKWAIT procedure - Generate a report of current lock waits

The Lock Waits report contains information about each lock wait currently in progress. Details include information about the lock holder and requestor and characteristics of the lock held and the lock requested.

Syntax


>>-MONREPORT.LOCKWAIT--(--)------------------------------------><

Authorization

The following privilege is required:

EXECUTE privilege on the MONREPORT module

The following examples demonstrate various ways to call the LOCKWAIT procedure:

call monreport.lockwait;

call monreport.lockwait();

Figure 1. Sample MONREPORT.LOCKWAIT output - summary section

  --------------------------------------------------------------------------------
  -------------------------------------------------------------------------------- 
  Monitoring report - current lock waits                                           
  -------------------------------------------------------------------------------- 
  Database:               SAMPLE                                                   
  Generated:              08/28/2009 07:16:26                                      
                                                                                   
  ================================================================================ 
  Part 1 - Summary of current lock waits                                           
                                                                                   
  -------------------------------------------------------------------------------- 
                                                                                   
        REQ_APPLICATION  LOCK_MODE  HLD_APPLICATION  LOCK_  LOCK_OBJECT_TYPE       
  #     HANDLE           REQUESTED  _HANDLE          MODE                          
  ----  ---------------  ---------  ---------------  -----  ---------------------- 
  1     26               U          21               U      ROW                    
  2     25               U          21               U      ROW                    
  3     24               U          21               U      ROW                    
  4     23               U          21               U      ROW                    
  5     22               U          21               U      ROW                    
  6     27               U          21               U      ROW                    
                                                                                   
  ================================================================================ 
    
  ...


  390 record(s) selected.

  Return Status = 0

Figure 2. Sample MONREPORT.LOCKWAIT output - details section

  ================================================================================ 
  Part 2: Details for each current lock wait                                       
                                                                                   
  lock wait #:1                                                                    
  -------------------------------------------------------------------------------- 
                                                                                   
  -- Lock details --                                                               
                                                                                   
  LOCK_NAME            = 04000500040000000000000052                                
  LOCK_WAIT_START_TIME = 2009-08-28-07.15.31.013802                                
  LOCK_OBJECT_TYPE     = ROW                                                       
  TABSCHEMA            = TRIPATHY                                                  
  TABNAME              = INVENTORY                                                 
  ROWID                = 4                                                         
  LOCK_STATUS          = W                                                         
  LOCK_ATTRIBUTES      = 0000000000000000                                          
  ESCALATION           = N                                                         
                                                                                   
  -- Requestor and holder application details --                                   
                                                                                   
  Attributes           Requestor                      Holder                       
  -------------------  -----------------------------  ---------------------------- 
  APPLICATION_HANDLE   26                             21                           
  APPLICATION_ID       *LOCAL.tripathy.090828111531   *LOCAL.tripathy.090828111435 
  APPLICATION_NAME     java                           java                         
  SESSION_AUTHID       TRIPATHY                       TRIPATHY                     
  MEMBER               0                              0                            
  LOCK_MODE            -                              U                            
  LOCK_MODE_REQUESTED  U                              -                            
                                                                                   
  -- Lock holder current agents --                                                 
                                                                                   
  AGENT_TID            = 41                                                        
  REQUEST_TYPE         = FETCH                                                     
  EVENT_STATE          = IDLE                                                      
  EVENT_OBJECT         = REQUEST                                                   
  EVENT_TYPE           = WAIT                                                      
  ACTIVITY_ID          =                                                           
  UOW_ID               =                                                           
                                                                                   
  -- Lock holder current activities --                                             
                                                                                   
  ACTIVITY_ID          = 1                                                         
  UOW_ID               = 1                                                         
  LOCAL_START_TIME     = 2009-08-28-07.14.31.079757                                
  ACTIVITY_TYPE        = READ_DML                                                  
  ACTIVITY_STATE       = IDLE                                                      
                                                                                   
  STMT_TEXT            =                                                           
  select * from inventory for update                                               
                                                                                   
  -- Lock requestor waiting agent and activity --                                  
                                                                                   
  AGENT_TID            = 39                                                        
  REQUEST_TYPE         = FETCH                                                     
  ACTIVITY_ID          = 1                                                         
  UOW_ID               = 1                                                         
  LOCAL_START_TIME     = 2009-08-28-07.15.31.012935                                
  ACTIVITY_TYPE        = READ_DML                                                  
  ACTIVITY_STATE       = EXECUTING                                                 
                                                                                   
  STMT_TEXT            =                                                           
  select * from inventory for update