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:
|
| 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. |