SHOW_HIVE_TRANSACTIONS stored procedure

The SHOW_HIVE_TRANSACTIONS stored procedure returns the status of all transactions that are running against 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

Read syntax diagramSkip visual syntax diagramSHOW_HIVE_TRANSACTIONS

Description

The function returns the following fields:

Table 1. Information returned by the SHOW_HIVE_TRANSACTIONS stored procedure
Field Description
TRANSACTION_ID Identifier for a transaction that is running against the Hive metastore.
STATE The current state of the transaction. Valid values are OPEN, COMMITTED, and ABORTED.
START_TIME Timestamp corresponding to the start of the transaction.
LAST_HEARTBEAT Timestamp corresponding to the most recent heart beat signal from the transaction.
USER User name under which the transaction was initiated.
HOSTNAME Host name of the server from which the transaction was submitted.

Example

  • Retrieve 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.