SNAPLOCK administrative view and SNAP_GET_LOCK table function - Retrieve lock logical data group snapshot information
The SNAPLOCK administrative view and the SNAP_GET_LOCK table function return snapshot information about locks, in particular, the lock logical data group.
SNAPLOCK administrative view
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.
Authorization
- SELECT privilege on the SNAPLOCK administrative view
- CONTROL privilege on the SNAPLOCK administrative view
- DATAACCESS authority
- EXECUTE privilege on the SNAP_GET_LOCK table function
- DATAACCESS authority
- SYSMON
- SYSCTRL
- SYSMAINT
- SYSADM
Default PUBLIC privilege
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
Example
SELECT AGENT_ID, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_STATUS
FROM SYSIBMADM.SNAPLOCK WHERE DBPARTITIONNUM = 0AGENT_ID LOCK_OBJECT_TYPE LOCK_MODE LOCK_STATUS
-------------------- ---------------- --------- -----------
7 TABLE IX GRNT
1 record(s) selected.SNAP_GET_LOCK table function
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 member, aggregate of all database members or all database members.
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.
Syntax
The schema is SYSPROC.
Table function parameters
-
dbname
- An input argument of type VARCHAR(128) that specifies a valid database name in the same instance as the currently connected database. Specify a database name that has a directory entry type of either "Indirect" or "Home", as returned by the LIST DATABASE DIRECTORY command. Specify a null value or empty string to take the snapshot from the currently connected database. member
- An optional input argument of type INTEGER that specifies a valid database member number. Specify -1 for the current database member, or -2 for an aggregate of all active database members. If dbname is not set to NULL and member is set to NULL, -1 is set implicitly for member. If this input option is not used, that is, only dbname is provided, data is returned from all active database members. An active database member is a member where the database is available for connection and use by applications.
If both dbname and member 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 member number.
Authorization
- EXECUTE privilege on the SNAP_GET_LOCK table function
- DATAACCESS authority
- SYSMON
- SYSCTRL
- SYSMAINT
- SYSADM
Default PUBLIC privilege
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
Example
SELECT AGENT_ID, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_STATUS
FROM TABLE(SNAP_GET_LOCK('',-1)) as TAGENT_ID LOCK_OBJECT_TYPE LOCK_MODE LOCK_STATUS
--------...--- ------------------ ---------- -----------
680 INTERNALV_LOCK S GRNT
680 INTERNALP_LOCK S GRNT
2 record(s) selected. Information returned
| Column name | Data type | Description or corresponding monitor element |
|---|---|---|
| SNAPSHOT_TIMESTAMP | TIMESTAMP | The date and time that the snapshot was taken. |
| 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 | dbpartitionnum - Database partition number monitor element |
| MEMBER | SMALLINT | member - Database member monitor element |