List sessions / active connections in Teradata database

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

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

sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.