Examples: Using MonitorSessionSV - Teradata VantageCloud Lake

Example: Identifying All Connected Sessions

This query identifies all sessions that are currently connected to the system.

SELECT *
  FROM MONITORSESSIONSV(   
 USING HOSTID('-1')
       USERNAME('*')
       SESSIONID('0')
       ) AS DT
;

Example: Determining Number of Sessions in Each State

This query determines the number sessions in each state (Idle, Active, Blocked, Aborting, or Unknown).

SELECT AMPSTATE
      ,COUNT(*)
 
  FROM MONITORSESSIONSV(   
 USING HOSTID('-1')
       USERNAME('*')
       SESSIONID('0')
       )               AS DT
GROUP BY AMPSTATE
ORDER BY AMPSTATE
;

Example: Identifying Sessions That are Blocked

This query identifies sessions that are blocked by other sessions, along with the nature of the block.

SELECT USERNAME
      ,SESSIONNO           AS BLKD_SESS_NO
      ,USERNAME
      ,BLK1SESSNO          AS FIRST_BLKG_SESS_NO
      ,BLK1USERID          AS FIRST_BLKG_USER_ID
      ,BLK1LMODE           AS FIRST_BLKG_LOCK_TYPE
      ,BLK2SESSNO          AS SECOND_BLKG_SESS_NO
      ,BLK2USERID          AS SECOND_BLKG_USER_ID
      ,BLK2LMODE           AS SECOND_BLKG_LOCK_TYPE
      ,BLK3SESSNO          AS THIRD_BLKG_SESS_NO
      ,BLK3USERID          AS THIRD_BLKG_USER_ID
      ,BLK3LMODE           AS THIRD_BLKG_LOCK_TYPE
  FROM MONITORSESSIONSV(   
 USING HOSTID('-1')
       USERNAME('*')
       SESSIONID('0')
       )               AS DT
WHERE AMPSTATE = 'BLOCKED'
ORDER BY USERNAME
;

Example: Using ComputeWorkerInUse

SELECT SESSIONNO, USERNAME
FROM TABLE (MonitorSession(-1, '*')) AS t1
WHERE ComputeWorkerInUse = 'T';