The SNAPLOCK administrative view and the SNAP_GET_LOCK table function return snapshot information about locks, in particular, the lock logical data group.
This administrative view allows you to retrieve lock logical data group snapshot information for the currently connected database.
Used with the SNAPLOCKWAIT administrative view, the SNAPLOCK administrative view provides information equivalent to the GET SNAPSHOT FOR LOCKS ON database-alias CLP command.
The schema is SYSIBMADM.
Refer to Table 1 for a complete list of information that can be returned.
SELECT AGENT_ID, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_STATUS
FROM SYSIBMADM.SNAPLOCK WHERE DBPARTITIONNUM = 0
AGENT_ID LOCK_OBJECT_TYPE LOCK_MODE LOCK_STATUS
-------------------- ---------------- --------- -----------
7 TABLE IX GRNT
1 record(s) selected.
The SNAP_GET_LOCK table function returns the same information as the SNAPLOCK administrative view, but allows you to retrieve the information for a specific database on a specific database partition, aggregate of all database partitions or all database partitions.
Used with the SNAP_GET_LOCKWAIT table function, the SNAP_GET_LOCK table function provides information equivalent to the GET SNAPSHOT FOR LOCKS ON database-alias CLP command.
Refer to Table 1 for a complete list of information that can be returned.
>>-SNAP_GET_LOCK--(--dbname--+------------------+--)----------->< '-, dbpartitionnum-'
The schema is SYSPROC.
If both dbname and dbpartitionnum are set to NULL, an attempt is made to read data from the file created by SNAP_WRITE_FILE procedure. Note that this file could have been created at any time, which means that the data might not be current. If a file with the corresponding snapshot API request type does not exist, then the SNAP_GET_LOCK table function takes a snapshot for the currently connected database and database partition number.
SELECT AGENT_ID, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_STATUS
FROM TABLE(SNAP_GET_LOCK('',-1)) as T
AGENT_ID LOCK_OBJECT_TYPE LOCK_MODE LOCK_STATUS
--------...--- ------------------ ---------- -----------
680 INTERNALV_LOCK S GRNT
680 INTERNALP_LOCK S GRNT
2 record(s) selected.
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | snapshot_timestamp - Snapshot timestamp monitor element |
AGENT_ID | BIGINT | agent_id - Application handle (agent ID) |
TAB_FILE_ID | BIGINT | table_file_id - Table file identification |
LOCK_OBJECT_TYPE | VARCHAR(18) | lock_object_type - Lock object type
waited on . This interface returns a text identifier based
on the defines in sqlmon.h and is one of:
|
LOCK_MODE | VARCHAR(10) | lock_mode - Lock mode . This interface returns
a text identifier based on the defines in sqlmon.h and is one of:
|
LOCK_STATUS | VARCHAR(10) | lock_status - Lock status . This interface returns
a text identifier based on the defines in sqlmon.h and is one of:
|
LOCK_ESCALATION | SMALLINT | lock_escalation - Lock escalation |
TABNAME | VARCHAR(128) | table_name - Table name |
TABSCHEMA | VARCHAR(128) | table_schema - Table schema name |
TBSP_NAME | VARCHAR(128) | tablespace_name - Table space name |
LOCK_ATTRIBUTES | VARCHAR(128) | lock_attributes - Lock attributes . This
interface returns a text identifier based on the defines in sqlmon.h. If there are no locks, the text identifier is
NONE, otherwise, it is any combination of the following separated
by a '+' sign:
|
LOCK_COUNT | BIGINT | lock_count - Lock count |
LOCK_CURRENT_MODE | VARCHAR(10) | lock_current_mode - Original lock mode
before conversion . This interface returns
a text identifier based on the defines in sqlmon.h and is one of:
|
LOCK_HOLD_COUNT | BIGINT | lock_hold_count - Lock hold count |
LOCK_NAME | VARCHAR(32) | lock_name - Lock name |
LOCK_RELEASE_FLAGS | BIGINT | lock_release_flags - Lock release flags |
DATA_PARTITION_ID | INTEGER | data_partition_id - Data Partition identifier . For a non-partitioned table, this element is NULL. |
DBPARTITIONNUM | SMALLINT | The database partition from which the data was retrieved for this row. |