Purpose
Lists all active client session connections to the Business Continuity Manager 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 Business Continuity Manager server.
bcmadmin> session list;
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)
---------------------------------------------------------------
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 |
|---|---|
| Session Number | Number of the Business Continuity Manager session. |
| Client TDP Session Number | Associated Business Continuity Manager 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 used in the session. |
| Session State | Availability for sessions. |
| Session in Transaction | Length of time session is 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 database system is in use and status. |
List sessions that are established on the managed servers for recovery.
bcmadmin> 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 --------------------------------------------- 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 --------------------------------------------- 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 | Number of requests sent to the dispatcher/The number of requests that can be sent to the dispatcher without waiting for a response |
| Restoring Sessions Count | Number of sessions that need to be recovered |
| Time Since Last Activity/RecoveryTimeout | Time (in seconds) since the last request was sent or the last response was received/The amount of time sequencer waits for a response before timeout |
| Session Number | Associated Business Continuity Manager session number |
| Session Recovery Status | Indicates the current recovery status of the session Possible values:
|
| System Session Number | Associated TDP session number on the managed server |
| Session Queue Size | Number of requests yet to be processed at the dispatcher |
| Session Type | Type of session Possible values:
|
| Current Transaction ID | ID of the current transaction at the dispatcher |
| Current Request | SQL text and statement type of the current request at the dispatcher |
| Run Time (locks-grant + execution) | Time (in seconds) taken by the current request at the dispatcher (waiting for locks grant and database server) |
| Pending Locks | Object IDs of the locks waiting for grant at the dispatcher |
| Previous Error Txn | 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 | Time the session was established |
| Last Sent Transaction ID | Transaction ID of the last request sent to the dispatcher |
| Last Committed Transaction ID | Last transaction ID committed by this session |
| Last Expected Commit | 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, Business Continuity Manager is trying to access a database that does not exist.
bcmadmin> session list INTERRUPTED; --------------------------------------------- Session Number : 1000 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 |
|---|---|
| Session Number | Number of the Business Continuity Manager session |
| Transaction ID | Number associated with transaction |
| System | System associated with interrupted session |
| Root Cause |
|
| 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 |
A new BCM sequencer setting has been introduced to display locks held by listed session SessionShowLockDetails is disabled by default. Enable it to start seeing locking information along with session details.
If the parameter is enabled then locking information appears in "session list", as seen here:
localhost> config update sequencer SessionShowLockDetails 1 reason show_locks; ------>this is how we enable the parameter
localhost>
localhost>
localhost> session list;
---------------------------------------------
Session Number : 1000
Client TDP Session Number : 1003
Associated endpoint : region1_ept (1)
Username : kiran
Logon Source : localhost 20335 root bteq 01 LSS
Client IP Address : 127.0.0.1
Connected Since : 03/24 05:51:50
Idle Time (in seconds) : 16
Mode : Read-Write (managed)
Error List : DefaultErrorList
Session Routing : DefaultRouting
Session State : Available (in transactions)
Ordered Read Systems : [1*] bcmn-tdvm-smp-0398
System Sessions:
[1] bcmn-tdvm-smp-0398 - 6312 (Active)
[2] pe-tdvm-smp-0397 - 4658 (Standby)
Locking details: <--------- this is the info that will be displayed if the parameter is enabled
Lock pending txn scope table write lock on 'kiran.p1' [1001] <---------
Lock pending txn scope table write lock on 'kiran.p2' [1002] <---------
---------------------------------------------