ABORT_HIVE_TRANSACTIONS stored procedure

The ABORT_HIVE_TRANSACTIONS stored procedure interrupts transactions that are running against the Hive metastore. Use this procedure to release resources that are locked by the transaction that is to be aborted.

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

'transaction-id': Specifies a comma-separated list of one or more transaction IDs. The entire list must be enclosed by single quotation marks. Any invalid values, or values that do not match a valid transaction ID, are ignored.

Usage notes

You can use the SHOW_HIVE_TRANSACTIONS stored procedure and the SHOW_HIVE_LOCKS stored procedure to identify transaction IDs for activities that are running against the Hive metastore.

Examples

List the status of current transactions.

CALL SYSHADOOP.SHOW_HIVE_TRANSACTIONS;

  Result set 1
  --------------

  TRANSACTION_ID STATE    START_TIME           LAST_HEARTBEAT       USER    HOSTNAME            
  -------------- -------- -------------------- -------------------- ------- --------------------
  222            ABORTED  2020-01-16-11.00.05  2020-01-16-11.00.05  BIGSQL  example.ibm.com 
  223            OPEN     2020-01-16-11.00.06  2020-01-16-11.04.20  BIGSQL  example.ibm.com 
  224            ABORTED  2020-01-16-11.02.59  2020-01-16-11.02.59  BIGSQL  example.ibm.com 
  225            ABORTED  2020-01-16-11.03.01  2020-01-16-11.03.01  BIGSQL  example.ibm.com 

  4 record(s) selected.

Abort a single transaction whose ID is 222.

CALL SYSHADOOP.ABORT_HIVE_TRANSACTIONS('222');

Abort more than one transaction in a single procedure call.
```
CALL SYSHADOOP.ABORT_HIVE_TRANSACTIONS('224,225');
```