SESSION LIST - Teradata Unity

Teradata® Unity™ User Guide

Product
Teradata Unity
Release Number
16.50
Published
January 2020
Language
English (United States)
Last Update
2020-02-04
dita:mapPath
frp1571162435023.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2520
lifecycle
previous
Product Category
Analytical Ecosystem

Purpose

Lists all active client session connections to the Unity server.

Syntax

SESSION LIST ([CONNECTING | IDLE | INTRANS | RECONNECTABLE | DISCONNECTING] [SORT BY TIME {IDLE | TXN | CONNECTED}]) | (INTERRUPTED [ALL]) | (RECOVERING [<session id>, …] ON <tdpid>[, <tdpid>,…]) ;

Parameters

INTERRUPTED
Displays interrupted sessions with database errors.
INTERRUPTED ALL
Displays all interrupted sessions.
RECOVERING
Displays all recovering sessions.

Examples

Lists managed and passive sessions to the Unity server.

unityadmin>  session list; 
Unity Session Number      : 1000
Client TDP Session Number : 1050
Associated endpoint       : region1_ept (1)
Username                  : testuser1
Logon Source              : fast	634	testuser1  bteq  01 LSS
Client IP Address         : 153.65.194.177
Connected Since           : 10/28 16:58:53
Mode                      : Read-Only (managed)
Error List                : DefaultErrorList
Session Routing           : managedrouting1
Session State             : Available (in transaction)
Session in transaction    : 1 day 15 hr 54 min 17 sec 
Ordered Read Systems      : [1*] flash [2] blaze
System Sessions:
    [1] flash - 5316060 (Active)
    [2] blaze - 5223551 (Active)
---------------------------------------------------------------
Unity Session Number      : 40001
Client TDP Session Number	: 1049
Associated endpoint      	:	region1_ept (1)
Username                  :	testuser1
Logon Source              : fast	630	testuser1  bteq  01 LSS
Connected Since           : 10/28 16:58:50
Mode                     	:	Passive
Error List               	:	DefaultErrorList
Session Routing           :	passiverouting1
Session State             :	Available (not in transaction)
System Sessions           :	flash - 5316059 (Active)
Output Description
Unity Session Number Number of the Unity session.
Client TDP Session Number Associated Unity Session Number.
Associated endpoint Name of the Endpoint associated with the session.
Username Name of the Teradata user.
Account Account string if specified.
Group Group name for the user if specified.
Define a group in the routing files or with the USER commands.
Client IP Address IP Address
Connected Since Time stamp set when a session begins.
Mode Mode indicates Read-only, Read-Write, or Passive.
Error List Error list associated with the session routing rule.
Session Routing Routing rule session is using.
Session State Availability for sessions.
Session in Transaction Length of time session has been in transaction (valid only for Managed Routing rules).
Ordered Read Systems [Optional] Read system associated with session routing rule. Only shown when the routing rule has the read system specified.
Ordered Write Systems [Optional] Write system associated with session routing rule. Only shown when the routing rule has the write system specified.
System Sessions Session names and Teradata session numbers. Shows which Teradata Database system is in use and status.

List sessions that are established on the managed servers for recovery.

unityadmin> session list recovering all on 1,2;
---------------------------------------------
System TDP ID                               : sdt14336
Outstanding Requests/RecoverybatchSize      : 1 / 1000
Restoring Sessions Count                    : 1
Time Since Last Activity/RecoveryTimeout    : 0 sec / 300 sec
---------------------------------------------
Unity Session Number                      : 1002
Session Recovery Status                   : Completed (requests pending at dispatcher)

Sequencer Info:
Previous Error Txn                        : 92945
Previous Error Text                       : Duplicate row error in bharath.t2.
Previous Error Code                       : 2802
Session Established Time                  : 12/23 21:23:28
Last Sent Transaction ID                  : 92945
Last Expected Commit                      : 92945
Requests Sent To Dispatcher               : 1

Dispatcher Info:
System Session Number                     : 13678
Session Queue Size                        : 0
Session Type                              : DBCSQL
Last Committed Transaction ID             : 92944
Current Transaction ID                    : 92945
Current Request                           : ins t2(2); [Insert]
Run Time (locks-grant + execution)        : 30 (0 + 30)
--------------------------------------------
---------------------------------------------
System TDP ID                               : sdt14319
Outstanding Requests/RecoverybatchSize      : 1 / 1000
Restoring Sessions Count                    : 1
Time Since Last Activity/RecoveryTimeout    : 0 sec / 300 sec
---------------------------------------------
Unity Session Number                      : 1001
Session Recovery Status                   : Completed (requests pending at dispatcher)

Sequencer Info:
Previous Error Txn                        : 92938
Previous Error Text                       : Table 't3' already exists.
Previous Error Code                       : 3803
Session Established Time                  : 12/23 21:23:28
Last Sent Transaction ID                  : 92938
Last Expected Commit                      : 92938
Requests Sent To Dispatcher               : 1

Dispatcher Info:
System Session Number                     : 10482
Session Queue Size                        : 0
Session Type                              : DBCSQL
Last Committed Transaction ID             : 92899
Current Transaction ID                    : 92938
Current Request                           : ct t3(i int); [DDL]
Run Time (locks-grant + execution)        : 30 (0 + 30)
--------------------------------------------
---------------------------------------------
Connecting sessions       : 0
IDLE sessions             : 2
In transaction sessions   : 0
Reconnectable sessions    : 0
Disconnecting sessions    : 1
Interrupted sessions      : 0
Recent attempted connects : 3
Recent failed attempts    : 0
Output Description
System TDP ID System ID
Outstanding Requests/RecoverybatchSize How many requests have been sent to dispatcher/how many requests can be sent to dispatcher without waiting for response
Restoring Sessions Count The number of sessions that need to be recovered
Time Since Last Activity/RecoveryTimeout The amount of time (in seconds) since the last request was sent or the last response was received/The amount of time sequencer would wait for a response before timeout
Unity Session Number The associated Unity session number
Session Recovery Status Possible values:
  • Waiting
  • In Progress
  • Completed
System Session Number The associated TDP session number on the managed server
Session Queue Size The number of requests yet to be processed at the dispatcher
Session Type The type of session (DBCSQL, MLOAD, FASTLOAD, EXPORT, UNKNOWN)
Current Transaction ID The ID of the current transaction at the dispatcher
Current Request The SQL text and statement type of the current request at the dispatcher
Run Time (locks-grant + execution) The time (in seconds) taken by the current request at the dispatcher (waiting for locks grant and database server)
Pending Locks The object IDs of the locks waiting for grant at the dispatcher
Previous Error Txn The transaction ID of previous failure
Previous Error Text Error text explaining the previous failure
Previous Error Code Error code of previous failure
Session Established Time The time the session was established
Last Sent Transaction ID The transaction ID of the last request sent to the dispatcher
Last Committed Transaction ID The last transaction ID committed by this session
Last Expected Commit The last transaction ID that this session has to commit to complete recovery

Lists information on an interrupted session with a database error. In this example, Unity is trying to access a database that does not exist.

unityadmin> session list INTERRUPTED;
---------------------------------------------
Unity Session Number      : 1000
Unity Transaction ID      : 0
System                    : jasonu4
Root Cause                : Yes
Interrupt Time            : 09/29 16:17:16
Time of Original Query    : 09/29 16:07:09
Error Code                : 3802
Error Text                : Database 'jason2' does not exist.
SQL Text                  : database jason2;
---------------------------------------------
Output Description
Unity Session Number Number of the Unity session.
Unity Transaction ID Number associated with transaction.
System System associated with interrupted session.
Root Cause Yes indicates the cause was from a database issue. No indicates the session was interrupted by another session.
Interrupt Time Timestamp when system became interrupted.
Time of Original Query Timestamp when session attempted to access database.
Error Code Code associated with error.
Error Text Text string indicating error.
SQL Text SQL Text string showing error.