DB2 10.5 for Linux, UNIX, and Windows

MON_GET_APPL_LOCKWAIT table function - Get information about locks for which an application is waiting

The MON_GET_APPL_LOCKWAIT table function returns information about all locks that each application's agents (that are connected to the current database) are waiting to acquire.

To get information about locks, use the MON_GET_APPL_LOCKWAIT, MON_FORMAT_LOCK_NAME, and MON_GET_LOCKS, table functions instead of the SNAPLOCKWAIT administrative view and SNAP_GET_LOCKWAIT table function, and the SNAPLOCK administrative view and SNAP_GET_LOCK table function, which are deprecated in Fixpack 1 of Version 9.7.


>>-MON_GET_APPL_LOCKWAIT--(--application_handle--,--member--+-----------------+--)-><
                                                            '-,--system_appls-'

The schema is SYSPROC.

Routine parameters

application_handle

An optional input parameter of type BIGINT that specifies a valid application handle in the same database as the one to which you are currently connected. If the argument is null, locks are retrieved for all applications that are currently waiting for locks to be acquired.

member

An input parameter of type INTEGER that specifies a valid member in the same instance as the currently connected database. Specify -1 for the current database member, or -2 for all active members. If the NULL value is specified, -1 is set.

system_appls

An input parameter of type SMALLINT that specifies whether information for system applications is to be returned. The following values are valid:

0 or NULL: System application information is not returned. NULL is the default if a value for system_appls is not provided.
1: User and system application information is returned.

Authorization

One of the following authorities or privilege is required:

SYSADM authority
SYSMON authority
EXECUTE privilege on the MON_GET_APPL_LOCKWAIT table function.

Default PUBLIC privilege

None

Information returned

The columns that are returned provide information in the following areas:

The following columns represent details about the lock that the application is currently waiting to acquire:
LOCK_WAIT_START_TIME, LOCK_NAME, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_CURRENT_MODE, LOCK_MODE_REQUESTED, LOCK_STATUS, LOCK_ESCALATION, LOCK_ATTRIBUTES, LOCK_RRIID, LOCK_COUNT, TBSP_ID, TAB_FILE_ID, SUBSECTION_NUMBER.
The following columns represent details about the application that is waiting to acquire this lock.
REQ_APPLICATION_HANDLE, REQ_AGENT_TID, REQ_MEMBER, REQ_EXECUTABLE_ID
The following columns represent details about the application that is currently holding the lock.
HLD_APPLICATION_HANDLE, HLD_MEMBER, ADDITIONAL_DETAILS

Table 1. Information returned by the MON_GET_APPL_LOCKWAIT table function
Column name	Data type	Description or monitor element
LOCK_WAIT_START_TIME	TIMESTAMP	lock_wait_start_time - Lock Wait Start Timestamp
LOCK_NAME	VARCHAR(32)	lock_name - Lock name The internal name can be formatted using the MON_FORMAT_LOCK_NAME table function to obtain details regarding the lock. For example, the table and table space that the lock references can be found, if this is a table lock.
LOCK_OBJECT_TYPE_ID	CHAR(1) FOR BIT DATA	Reserved for future use
LOCK_OBJECT_TYPE	VARCHAR(32)	lock_object_type - Lock object type waited on For possible values, see "lock_object_type - Lock object type waited on monitor element"
LOCK_MODE	VARCHAR(3)	lock_mode - Lock mode If the application holding this lock cannot be found, a value of NULL is returned. For a global lockwait, this value is NULL.
LOCK_CURRENT_MODE	VARCHAR(3)	lock_current_mode - Original Lock Mode Before Conversion If no conversion took place, then a value of NULL is returned.
LOCK_MODE_REQUESTED	VARCHAR(3)	lock_mode_requested - Lock mode requested
LOCK_STATUS	CHAR(1)	lock_status - Lock status
LOCK_ESCALATION	CHAR(1)	lock_escalation - Lock escalation
LOCK_ATTRIBUTES	CHAR(16)	lock_attributes - Lock attributes
LOCK_RRIID	BIGINT	lock_count - Lock count monitor element
LOCK_COUNT	BIGINT	lock_count - Lock count monitor element
TBSP_ID	BIGINT	tablespace_id - Table space ID
TAB_FILE_ID	BIGINT	table_file_id - Table file ID
SUBSECTION_NUMBER	BIGINT	ss_number - Subsection Number If the subsection number is not available, then a value of NULL is returned.
REQ_APPLICATION_HANDLE	BIGINT	req_application_handle - Requesting application handle
REQ_AGENT_TID	BIGINT	req_agent_tid - Requesting agent TID
REQ_MEMBER	SMALLINT	req_member - Requesting member
REQ_EXECUTABLE_ID	VARCHAR (32) FOR BIT DATA	req_executable_id - Requesting executable ID
HLD_APPLICATION_HANDLE	BIGINT	hld_application_handle - Holding application handle If the application holding this lock is unknown or cannot be found then a value of NULL is returned. For a global lockwait, this value is NULL.
HLD_MEMBER	SMALLINT	hld_member - Holding member
IS_SYSTEM_APPL	SMALLINT	is_system_appl - Is System Application

Example

In this sample scenario, the MON_GET_APPL_LOCKWAIT table function is used to investigate a hung application for the session authorization ID USER1.

Use the MON_GET_CONNECTION table function to look up the application handle for all connections with the SESSION_USER value of USER1:

SELECT COORD_PARTITION_NUM, APPLICATION_HANDLE
   FROM TABLE(MON_GET_CONNECTION(NULL,-2))
   WHERE SESSION_USER = 'USER1'

This query returns the following output:

COORD_PARTITION_NUM              APPLICATION_HANDLE  
-------------------------------  ----------------------------
2                                131130
  
1 record(s) selected.

Use the MON_GET_AGENT table function to obtain current information about all agents working for this connection, on all database partitions:

SELECT SUBSTR(CHAR(DBPARTITIONNUM),1,3) AS DBPART,
	    SUBSTR(CHAR(APPLICATION_HANDLE),1,7) AS APP_ID, 
	    SUBSTR(CHAR(WORKLOAD_OCCURRENCE_ID),1,7) AS WLO_ID, 
	    SUBSTR(CHAR(AGENT_TID),1,7) AS AGENT_ID, 
	    SUBSTR(CHAR(AGENT_TYPE),1,12) AS AGENT_TYPE, 
	    SUBSTR(AGENT_STATE,1, 8) AS STATE, 
	    SUBSTR(EVENT_TYPE,1, 8) AS EV_TYPE, 
	    SUBSTR(EVENT_OBJECT,1,12) AS EV_OBJECT
FROM TABLE(MON_GET_AGENT('','',131130,-2))
ORDER BY AGENT_TYPE, DBPART

This query returns the following output:

DBPART  APP_ID  WLO_ID  AGENT_ID  AGENT_TYPE  STATE   EV_TYPE  EV_OBJECT  
------  ------- ------- --------- ----------- ------  -------- ----------
2       131130  1       3110      COORDINATOR ACTIVE  WAIT     REQUEST
0       131130  1       7054      PDBSUBAGENT ACTIVE  ACQUIRE  LOCK
1       131130  1       5709      PDBSUBAGENT ACTIVE  ACQUIRE  LOCK
2       131130  1       5960      PDBSUBAGENT ACTIVE  ACQUIRE  LOCK

  4 record(s) selected.

An event of type ACQUIRE on an event object of type LOCK indicates a lock wait scenario. Now you can investigate which object is being waited for and which process is holding the lock on it.

To determine all locks that the application is waiting for, call the MON_GET_APPL_LOCKWAIT table function with application handle 131130 and member -2 as input parameters.

SELECT lock_name, 
       hld_member AS member, 
       hld_agent_tid as TID, 
       hld_application_handle AS HLD_APP FROM 
       TABLE (MON_GET_APPL_LOCKWAIT(131130, -2))

This query returns the following output:

LOCK_NAME                  MEMBER TID    HLD_APP
-------------------------- ------ ------ -------
00030005000000000280000452    0   1234   65564
00030005000000000280000452    1   5478   65564 
00030005000000000280000452    2   4678   65564

  3 record(s) selected.

Call the MON_GET_CONNECTION table function to find out more about the application that is holding the lock (this application has an application handle of 65564).

SELECT SYSTEM_AUTH_ID, APPLICATION_NAME AS APP_NAME,
   WORKLOAD_OCCURRENCE_STATE AS WL_STATE
FROM TABLE(MON_GET_CONNECTION(NULL,-2))
WHERE APPLICATION_HANDLE = 65564

This query returns the following output:

SYSTEM_AUTH_ID APP_NAME WL_STATE
-------------- -------- -----------
ZURBIE         db2bp    UOWWAIT