MON_GET_LATCH table function - List all latches in the current member
The MON_GET_LATCH table function returns a list of all latches in the current member.
Authorization
One of the following authorizations
is required:
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
Default PUBLIC privilege
None
Syntax
The schema is SYSPROC.
Routine parameters
- search_args
- An input parameter of type CLOB(1 K) that represents a list
of key-value pairs. If the list is empty or NULL, all latches from the
specified member are returned. Otherwise, all latches that match all of the conditions that are
represented by the list of key-value pairs are returned.
A key-value pair must follow this format:
- A key is a string that represents an opening tag. An opening tag
consists of an angle bracket, followed by the key name, followed by a closing angle bracket. Spaces
are not allowed. An example of an opening tag is
<application_handle> - The value is placed after the opening tag
- A closing tag consists of an opening angle bracket, followed by a forward slash, followed by the
key name, followed by a closing angle bracket. Spaces are not allowed. An example of a closing tag
is
</application_handle>
The following example shows of a full key-value pair:'<application_handle>123</application_handle>'All keys are case-sensitive and can be specified one time each in the search_args parameter.
The order of the keys does not matter.
SQLCODE -171 is returned for an invalid key-value pair.
An implicit AND operator exists between different keys.
The following use of the search_args parameter returns a list of all latches that are held or waiting to be acquired by the application with the handle 123:CLOB('<application_handle>123</application_handle>')The following keys are available for the search_args parameter:- application_handle
- Returns a list of all latches that are currently held, or are being acquired by, the specified
application handle. The value is specified as an INTEGER. For
example:
CLOB('<application_handle>145</application_handle>') - latch_name
- Returns a list of all latches that match the specified latch name. The value is specified as a
string, whose maximum length is 256 characters. For
example:
CLOB('<latch_name>SQLO_LT_SQLB_PTBL__pool_table_latch</latch_name>') - edu_id
- Returns a list of all latches that match the specified edu_id. For
example:
CLOB('<edu_id>28</edu_id>') - latch_status
- Returns a list of all latches in the specified status. The value that is specified can be one of
the following characters:
- H
- Latches that are held.
- W
- Latches that are waiting.
- C
- Latches that are contested. A contested latch occurs when an application acquires a latch, then another application waits for this latch.
CLOB('<latch_status>W</latch_status>')
The following examples demonstrate how to use key-value pairs in the search_args parameter.- Search for all latches:
CLOB( '' ) - Search for a latch that is named SQLO_LT_SQLB_PTBL__pool_table_latch, and an
application with a handle of 123 is holding or waiting for that latch:
CLOB('<application_handle>123</application_handle> <latch_name>SQLO_LT_SQLB_PTBL__pool_table_latch</latch_name>' ) - Search for all latches that are
contested:
CLOB('<latch_status>C</latch_status>')
- A key is a string that represents an opening tag. An opening tag
consists of an angle bracket, followed by the key name, followed by a closing angle bracket. Spaces
are not allowed. An example of an opening tag is
- member
- An input argument of type INTEGER that specifies the member from which data is returned. Specify -1 for the current member. Specify -2 for all active members.
Information returned
| Column name | Data type | Description |
|---|---|---|
| LATCH_NAME | VARCHAR(256) | latch_name - Latch name monitor element |
| MEMORY_ADDRESS | VARCHAR(32) | memory_address - Memory address monitor element |
| EDU_ID | BIGINT | edu_ID - Engine dispatchable unit ID monitor element |
| EDU_NAME | VARCHAR(128) | edu_name - Engine dispatchable unit name monitor element |
| APPLICATION_HANDLE | BIGINT | application_handle - Application handle monitor element If the value in the LATCH_STATUS column is 'H', APPLICATION_HANDLE represents the application that is holding the latch. If the value in the LATCH_STATUS column is 'W', APPLICATION_HANDLE represents the application that is waiting on the latch. |
| MEMBER | SMALLINT | member - Database member monitor element |
| LATCH_STATUS | CHAR(1) | latch_status - Latch status monitor element |
| LATCH_WAIT_TIME | BIGINT | latch_wait_time - Latch wait time monitor element |
Example
In this sample scenario, the MON_GET_LATCH table functions are used to get all latch information
in databases with connections, on all members.
- Call the MON_GET_LATCH table function to retrieve all latch information in databases with
connections, on all
members:
SELECT SUBSTR(LATCH_NAME,1,40) LATCH_NAME, SUBSTR(MEMORY_ADDRESS,1,20) MEMORY_ADDRESS, EDU_ID, SUBSTR(EDU_NAME,1,20) EDU_NAME, APPLICATION_HANDLE, MEMBER, LATCH_STATUS, LATCH_WAIT_TIME FROM TABLE ( MON_GET_LATCH( NULL, -2 ) ) ORDER BY LATCH_NAME, LATCH_STATUSThis query returns the following output:LATCH_NAME MEMORY_ADDRESS EDU_ID ... ---------------------------------------- -------------------- -------------------- ... SQLO_LT_SQLB_POOL_CB__ptfLotch 0x70000005351A440 36114 ... SQLO_LT_SQLB_POOL_CB__readLotch 0x70000005351A3C0 36114 ... SQLO_LT_SQLB_POOL_CB__readLotch 0x70000005351A3C0 37911 ... SQLO_LT_SQLB_PTBL__pool_table_latch 0x70000004108B910 37911 ... SQLO_LT_SQLB_PTBL__pool_table_latch 0x70000004108B910 37654 ... SQLO_LT_SQLB_PTBL__pool_table_latch 0x70000004108B910 37140 ... SQLO_LT_SQLE_KRCB__EDUChainLatch 0x780000000472EAC 37397 ... SQLO_LT_preventSuspendIOLotch 0x7800000046081C0 36114 ... SQLO_LT_sqeWLDispatcher__m_tunerLatch 0x780000001C68440 1029 ...Output for query (continued):... EDU_NAME APPLICATION_HANDLE MEMBER LATCH_STATUS LATCH_WAIT_TIME ... -------------------- -------------------- ------ ------------ -------------------- ... db2agent (SAMPLE) 118 0 H - ... db2agent (SAMPLE) 118 0 H - ... db2agent (SAMPLE) 124 0 W 65850 ... db2agent (SAMPLE) 124 0 H - ... db2agent (SAMPLE) 121 0 W 50397 ... db2agent (SAMPLE) 119 0 W 30886 ... db2agent (SAMPLE) 120 0 H - ... db2agent (SAMPLE) 118 0 H - ... db2wlmt - 0 H - 9 record(s) selected. - Call the MON_GET_LATCH table function to determine the latches that are
contested.
SELECT SUBSTR(LATCH_NAME,1,40) LATCH_NAME, SUBSTR(MEMORY_ADDRESS,1,20) ADDRESS, EDU_ID, SUBSTR(EDU_NAME,1,20) EDU_NAME, APPLICATION_HANDLE, MEMBER, LATCH_STATUS, LATCH_WAIT_TIME FROM TABLE ( MON_GET_LATCH( CLOB('<LATCH_STATUS>C</LATCH_STATUS>'), -2 ) ) ORDER BY LATCH_NAME, LATCH_STATUSThis query returns the following output:LATCH_NAME ADDRESS EDU_ID ... ---------------------------------------- -------------------- -------------------- ... SQLO_LT_SQLB_POOL_CB__readLotch 0x70000005351A3C0 36114 ... SQLO_LT_SQLB_POOL_CB__readLotch 0x70000005351A3C0 37911 ... SQLO_LT_SQLB_PTBL__pool_table_latch 0x70000004108B910 37911 ... SQLO_LT_SQLB_PTBL__pool_table_latch 0x70000004108B910 37654 ... SQLO_LT_SQLB_PTBL__pool_table_latch 0x70000004108B910 37140 ...Output for query (continued):... EDU_NAME APPLICATION_HANDLE MEMBER LATCH_STATUS LATCH_WAIT_TIME ... -------------------- -------------------- ------ ------------ -------------------- ... db2agent (SAMPLE) 118 0 H - ... db2agent (SAMPLE) 124 0 W 78140 ... db2agent (SAMPLE) 124 0 H - ... db2agent (SAMPLE) 121 0 W 62686 ... db2agent (SAMPLE) 119 0 W 43175 5 record(s) selected. - The previous output shows two latches that are contested by applications. To retrieve only the
SQLO_LT_SQLB_PTBL__pool_table_latch latch, specify the
latch_name value in the search_args argument to return the
applications that are contesting this
latch.
SELECT SUBSTR(LATCH_NAME,1,40) LATCH_NAME, SUBSTR(MEMORY_ADDRESS,1,20) ADDRESS, EDU_ID, SUBSTR(EDU_NAME,1,20) EDU_NAME, APPLICATION_HANDLE, MEMBER, LATCH_STATUS, LATCH_WAIT_TIME FROM TABLE ( MON_GET_LATCH( CLOB('<LATCH_STATUS>C</LATCH_STATUS> <LATCH_NAME>SQLO_LT_SQLB_PTBL__POOL_TABLE_LATCH</LATCH_NAME>'), -2 ) ) ORDER BY LATCH_NAME, LATCH_STATUSThis query returns the following output:LATCH_NAME ADDRESS EDU_ID ... ---------------------------------------- -------------------- -------------------- ... SQLO_LT_SQLB_PTBL__pool_table_latch 0x70000004108B910 37911 ... SQLO_LT_SQLB_PTBL__pool_table_latch 0x70000004108B910 37654 ... SQLO_LT_SQLB_PTBL__pool_table_latch 0x70000004108B910 37140 ...Output for query (continued):... EDU_NAME APPLICATION_HANDLE MEMBER LATCH_STATUS LATCH_WAIT_TIME ... -------------------- -------------------- ------ ------------ -------------------- ... db2agent (SAMPLE) 124 0 H - ... db2agent (SAMPLE) 121 0 W 74956 ... db2agent (SAMPLE) 119 0 W 55446 3 record(s) selected.