Teradata view contains a lot of useful information about current sessions.

Using SessionInfoV view

Query

SELECT  SessionNo,
        UserName,
        ClientIpAddress,
        ClientProgramName,
        ClientSystemUserId,
        ClientOsName,
        CASE Transaction_Mode
            WHEN 'A' THEN 'ANSI'
            WHEN 'T' THEN 'TDBS'
            END AS TransactionMode,
        CurIsolationLevel
FROM DBC.SessionInfoV
ORDER BY UserName;

Columns

• SessionNo - ID of the session
• UserName - name of the user logged into this session
• ClientIpAddress - IP address of the client connected to this session
• ClientProgramName - name of the application that is connected to this session
• ClientSystemUserId - id of client system user
• ClientOsName - name of the client operatating system
• Transaction_Mode - mode of the session
  • A - ANSI
  • T - TDBS
• CurIsolationLevel - current transaction isolation level:
  • Serializable
  • Read uncommitted
  • RC - Read Committed
  • RR - Repeatable Read.

Sample results

Using MonitorSession function

Notes

User need EXECUTE FUNCTION privilege to SYSLIB.MonitorSession function

Function Prototype

MonitorSession( HostIdIn, UserNameIn, SessionNoIn)

• HostIdIn - Logical ID of a host (or client) with sessions logged on. Value -1 indicate all hosts.
• UserNameIn - User name of the session. An asterisk (*) indicates all users.
• SessionNoIn - ID of the session. A value of zero indicates all sessions

Query

SELECT  HostId,
        SessionNo,
        LogonTime,
        UserName,
        UserAccount,
        LogonSource,
        PEState
FROM TABLE (MonitorSession(-1,'*',1035)) dt
ORDER BY SessionNo;

Columns

• HostId - Logical id of the host
• SessionNo - ID of the session
• LogonTime - IP address of the client connected to this session
• UserName - name of the user logged into this session
• UserAccount - current account for the session
• LogonSource - logon source information.
• PEState - Current state of the session within the PE (Parsing Engine).
  • DELAYED
  • HOST-RESTART
  • ABORTING
  • PARSING-WAIT
  • PARSING
  • ELICIT CLIENT DATA
  • DISPATCHING
  • BLOCKED
  • ACTIVE
  • RESPONSE
  • IDLE: IN-DOUBT
  • IDLE
  • QTDELAYED
  • SESDELAYED
  • UNKNOWN