SHOW_HIVE_LOCKS stored procedure
The SHOW_HIVE_LOCKS stored procedure returns information about schema, table, database, or partition locks that are managed by the Hive metastore.
Authorization
Only the bigsql user or a user with Db2® Big SQL administrative privileges can run this Hadoop procedure. However, the bigsql user can grant EXECUTE privileges to any user, group, or role.
Syntax
Description
- schema
- Specifies the schema whose lock information is to be returned.
- table
- Specifies the table whose lock information is to be returned.
- partition
- Specifies the partition in a partitioned table whose lock information is to be returned.
The function returns the following fields:
| Field | Description |
|---|---|
| LOCK_ID | Identifier for a lock that is managed by the Hive metastore. |
| HIVE_SCHEMA | Name of the Hive schema (or database) for which the lock is held or requested. |
| HIVE_TABLE | Name of the Hive table for which the lock is held or requested. |
| PARTITION | For partitioned tables, the partition-spec of the partition for which the lock is held or requested. For nonpartitioned tables, this field contains the null value. |
| STATE | The current state of the lock request. Valid values are ACQUIRED, NOT_ACQUIRED, WAITING, and ABORT. |
| BLOCKED_BY | Lock ID of a conflicting lock request. |
| TYPE | The type of lock being requested. Valid values are EXCLUSIVE, SHARED_READ, and SHARED_WRITE. |
| TRANSACTION_ID | Identifier for the transaction that is requesting this lock. |
| LAST_HEARTBEAT | Timestamp corresponding to the most recent heart beat signal from the transaction that is holding a lock. |
| ACQUIRED_AT | Timestamp corresponding to the time at which the lock was acquired. |
| USER | User name under which the lock was requested. |
| HOSTNAME | Host name of the server from which the lock request was submitted. |
| AGENT_INFO | Information about the agent that issued the lock request. For example, for an SQL client, this is the Hive query ID. |
Examples
- Retrieve information about all locks that are managed by the Hive
server.
CALL SYSHADOOP.SHOW_HIVE_LOCKS; Result set 1 -------------- LOCK_ID HIVE_SCHEMA HIVE_TABLE PARTITION STATE BLOCKED_BY TYPE TRANSACTION_ID LAST_HEARTBEAT ACQUIRED_AT USER HOSTNAME AGENT_INFO ------- ----------- ---------- -------------- --------- ---------- ------------- -------------- -------------- -------------------- ------- ---------------- --------------------------------------------------------- 212.1 hr products - ACQUIRED - SHARED_WRITE 299 - 2020-01-27-17.02.35 BIGSQL example.ibm.com hive_20200127090235_58b15d83-7963-4e22-b274-8c74ba20b341 213.1 hr products - WAITING 212.1 SHARED_WRITE 300 - - BIGSQL example.ibm.com hive_20200127090235_53cbc168-1d7f-4563-bdae-9abffb5783c5 214.1 sales orders region=north ACQUIRED - SHARED_READ 301 - 2020-01-27-17.03.17 BIGSQL example.ibm.com 3 record(s) selected. - Retrieve information about all table locks in a particular schema that are managed by the Hive
server.
CALL SYSHADOOP.SHOW_HIVE_LOCKS('sales'); Result set 1 -------------- LOCK_ID HIVE_SCHEMA HIVE_TABLE PARTITION STATE BLOCKED_BY TYPE TRANSACTION_ID LAST_HEARTBEAT ACQUIRED_AT USER HOSTNAME AGENT_INFO ------- ----------- ---------- ------------- --------- ---------- ------------- -------------- -------------- -------------------- ------- ---------------- --------------------------------------------------------- 210.1 sales orders region=west ACQUIRED - SHARED_WRITE 297 - 2020-01-27-16.55.15 BIGSQL example.ibm.com hive_20200127085515_24045021-d9e6-49e3-a2ad-d4be247095b7 210.2 sales orders region=east ACQUIRED - SHARED_WRITE 297 - 2020-01-27-16.55.15 BIGSQL example.ibm.com hive_20200127085515_24045021-d9e6-49e3-a2ad-d4be247095b7 211.1 sales products - ACQUIRED - SHARED_READ 298 - 2020-01-27-16.55.17 BIGSQL example.ibm.com hive_20200127085516_28e604f1-f30c-47af-9b8a-f2b251de409a 211.2 sales orders region=west ACQUIRED - SHARED_READ 298 - 2020-01-27-16.55.17 BIGSQL example.ibm.com hive_20200127085516_28e604f1-f30c-47af-9b8a-f2b251de409a 211.3 sales orders - ACQUIRED - SHARED_READ 298 - 2020-01-27-16.55.17 BIGSQL example.ibm.com hive_20200127085516_28e604f1-f30c-47af-9b8a-f2b251de409a 211.4 sales orders region=east ACQUIRED - SHARED_READ 298 - 2020-01-27-16.55.17 BIGSQL example.ibm.com hive_20200127085516_28e604f1-f30c-47af-9b8a-f2b251de409a 6 record(s) selected. - Retrieve information about all locks on a particular table that are managed by the Hive
server.
CALL SYSHADOOP.SHOW_HIVE_LOCKS('sales','products'); Result set 1 -------------- LOCK_ID HIVE_SCHEMA HIVE_TABLE PARTITION STATE BLOCKED_BY TYPE TRANSACTION_ID LAST_HEARTBEAT ACQUIRED_AT USER HOSTNAME AGENT_INFO ------- ----------- ---------- --------- --------- ---------- ------------ -------------- -------------- -------------------- ------- ---------------- --------------------------------------------------------- 211.1 sales products - ACQUIRED - SHARED_READ 298 - 2020-01-27-16.55.17 BIGSQL example.ibm.com hive_20200127085516_28e604f1-f30c-47af-9b8a-f2b251de409a 1 record(s) selected. - Retrieve information about all partition locks in a partitioned table that are managed by the
Hive
server.
CALL SYSHADOOP.SHOW_HIVE_LOCKS('sales', 'orders', 'region=EAST'); Result set 1 -------------- LOCK_ID HIVE_SCHEMA HIVE_TABLE PARTITION STATE BLOCKED_BY TYPE TRANSACTION_ID LAST_HEARTBEAT ACQUIRED_AT USER HOSTNAME AGENT_INFO ------- ----------- ---------- ------------ --------- ---------- ------------- -------------- -------------- -------------------- ------- ---------------- --------------------------------------------------------- 210.2 sales orders region=east ACQUIRED - SHARED_WRITE 297 - 2020-01-27-16.55.15 BIGSQL example.ibm.com hive_20200127085515_24045021-d9e6-49e3-a2ad-d4be247095b7 211.4 sales orders region=east ACQUIRED - SHARED_READ 298 - 2020-01-27-16.55.17 BIGSQL example.ibm.com hive_20200127085516_28e604f1-f30c-47af-9b8a-f2b251de409a 2 record(s) selected.