This single operational view API can return session information across VantageCloud Lake clusters. The API can also be used to trace query sessions to a compute node.
Syntax
REPLACE FUNCTION SYSLIB.MonitorSessionSV ( HostIdIn SMALLINT, UserNameIn TD_ANYTYPE, SessionNoIn INTEGER ) RETURNS TABLE ( HostId SMALLINT, SessionNo INTEGER, LogonPENo SMALLINT, RunVprocNo SMALLINT, PartName VARCHAR(16) CHARACTER SET LATIN, PEstate VARCHAR(18) CHARACTER SET LATIN, LogonTime VARCHAR(22) CHARACTER SET LATIN, UserId INTEGER, LSN INTEGER, UserName VARCHAR(128) CHARACTER SET UNICODE, UserAccount VARCHAR(128) CHARACTER SET UNICODE, PECPUsec FLOAT, XActCount FLOAT, ReqCount FLOAT, ReqCacheHits FLOAT, AMPState VARCHAR(18) CHARACTER SET LATIN, AMPCPUSec FLOAT, AMPIO FLOAT, ReqSpool FLOAT, Blk1HostId SMALLINT, Blk1SessNo INTEGER, Blk1UserId INTEGER, Blk1Lmode CHAR(1) CHARACTER SET LATIN, Blk1Otype CHAR(2) CHARACTER SET LATIN, Blk1ObjDBID INTEGER, Blk1ObjTID INTEGER, Blk1Status CHAR(1) CHARACTER SET LATIN, Blk2HostId SMALLINT, Blk2SessNo INTEGER, Blk2UserId INTEGER, Blk2Lmode CHAR(1) CHARACTER SET LATIN, Blk2Otype CHAR(2) CHARACTER SET LATIN, Blk2ObjDBID INTEGER, Blk2ObjTID INTEGER, Blk2Status CHAR(1) CHARACTER SET LATIN, Blk3HostId SMALLINT, Blk3SessNo INTEGER, Blk3UserId INTEGER, Blk3Lmode CHAR(1) CHARACTER SET LATIN, Blk3Otype CHAR(2) CHARACTER SET LATIN, Blk3ObjDBID INTEGER, Blk3ObjTID INTEGER, Blk3Status CHAR(1) CHARACTER SET LATIN, MoreBlockers CHAR(1) CHARACTER SET LATIN, LogonSource VARCHAR(128), HotAmp1CPU FLOAT, HotAmp2CPU FLOAT, HotAmp3CPU FLOAT, HotAmp1CPUId FLOAT, HotAmp2CPUId FLOAT, HotAmp3CPUId FLOAT, HotAmp1IO FLOAT, HotAmp2IO FLOAT, HotAmp3IO FLOAT, HotAmp1IOId INTEGER, HotAmp2IOId INTEGER, HotAmp3IOId INTEGER, LowAmp1CPU FLOAT, LowAmp2CPU FLOAT, LowAmp3CPU FLOAT, LowAmp1CPUId INTEGER, LowAmp2CPUId INTEGER, LowAmp3CPUId INTEGER, LowAmp1IO FLOAT, LowAmp2IO FLOAT, LowAmp3IO FLOAT, LowAmp1IOId INTEGER, LowAmp2IOId INTEGER, LowAmp3IOId INTEGER, AvgAmpCPUSec FLOAT, AvgAmpIOCnt FLOAT, AmpCount SMALLINT, TempSpaceUsg FLOAT, ReqStartTime VARCHAR(22) CHARACTER SET LATIN, ReqCPU FLOAT, ReqIO FLOAT, ReqNo INTEGER, WlcId INTEGER, DontReclassifyFlag SMALLINT, ProxyUser VARCHAR (128)CHARACTER SET UNICODE, CPUDecayLevel SMALLINT, IODecayLevel SMALLINT, TacticalCPUException INTEGER, TacticalIOException INTEGER, ReqIOKB FLOAT, ReqPhysIO FLOAT ReqPhysIOKB FLOAT ReqStepsCompletedCnt INTEGER, RedriveProtection CHAR (2) CHARACTER SET LATIN, CurrentRedriveParticipation CHAR (1) CHARACTER SET LATIN, ReqRedriveSpoolSpace FLOAT, BlockerSessionCnt SMALLINT, ReqTblOpBytesIn FLOAT, ReqTblOpBytesOut FLOAT, ProxyUserId INTEGER, ZoneId INTEGER, ReqHotAmpCPU FLOAT, ReqHotAmpCPUId SMALLINT, ReqHotAmpIO FLOAT, ReqHotAmpIOId SMALLINT, ReqInvolvedAMPCnt SMALLINT, ReqFirstRespTime VARCHAR(22) CHARACTER SET LATIN, ReqLocalQueryStatus SMALLINT, ReqRemoteHostId SMALLINT, ReqRemoteSessionId INTEGER, ReqRemoteRequestId INTEGER, ReqRemoteQueryId FLOAT) ReqHotAmpSpool FLOAT, ReqHotAmpSpoolId SMALLINT, ReqMapNo SMALLINT, ReqMaxNumMapAMPs INTEGER, ReqMinNumMapAMPs INTEGER, ReqSysDefNumMapAMPs INTEGER, ReqRemoteHostIp VARCHAR(128) CHARACTER SET UNICODE, ReqServerName VARCHAR(128) CHARACTER SET UNICODE, ReqFlexReleased SMALLINT, ReqAdmissionTime VARCHAR(22) CHARACTER SET LATIN, ParentSessionNo INTEGER, Type VARCHAR, Id VARCHAR, Name VARCHAR, Group VARCHAR ) ... ;
Syntax Elements
- HostIdIn
- Logical ID of a host (or client) with sessions logged on.
- UserNameIn
- User name of the session.
- SessionNoIn
- Number of the session.
- HostId
- This value is incremented by 10000 for each compute cluster for the single view. For example, the first node of the primary cluster starts at 1, the first node of the first online compute cluster starts at 10001, and the first node of the second online compute cluster starts with 20001, and so on.
- SessionNo
- Number of the current session. With a given host ID, a session number uniquely identifies a session on the database system. This value is assigned by the host (or client) at logon time.
- LogonPENo
- Vproc number of the PE the session is logged on to, which identifies the PE that has control responsibility for the session. Typically, this is the PE that processed the logon request, but if that PE goes offline, this is the PE to which the session was switched.
- RunVprocNo
- Vproc number of the AMP or PE assigned to process the session requests.
- PartName
- Name of the session partition associated with this session. Following a successful logon request by a session or as part of a connect request, the session identifies the partition with which the user wants the session to be associated. FASTLOAD, Teradata SQL, MONITOR, INTERNAL are examples of valid partition names.
- PEstate
- Current state of the session within the PE. For a list of the PEState values, see MONITOR SESSION.
- LogonTime
- Date and time portion of the information recorded by Session Control when a session logs on, typically formatted for display as yyyy/mm/dd 99:99:99.99, which represents the year, month, day, hours: minutes: seconds.
- UserId
- User or internal ID of a user for this session. Within the database, UserId is equivalent to Database ID. Typically, UserId is used when the associated record is known to be a user name, and Database ID is used when the associated record is known to be a database. However, UserId can identify either a given user or database name.
- LSN
- Logon Sequence Number (LSN) that is associated with a session when the session logs on, which identifies a collection of sessions performing a related activity. For example, in a FastLoad job, a user is logged on as a Teradata SQL session and n FastLoad sessions with the same user name. Therefore, n+1 sessions (1 Teradata SQL and n FastLoad) with the same LSN are all associated with the given FastLoad job. To see how the FastLoad job is doing, the user can pick out all sessions reported with the same LSN number.
- UserName
- User name of the session.
- UserAccount
- Current account for the session.
- PECPUsec
- CPU time, in seconds, used in a PE by the associated session for parsing and dispatching requests. Accurate to the second.
- XActCount
- Number of explicit and implicit transactions run by the session.
- ReqCount
- Number of requests (Tequel Start Request (TSR) messages) initiated by the session.
- ReqCacheHits
- Number of times that this session processed a request using information from the Teradata SQL Parser request cache, specifically, number of times there was a request cache hit.
- AMPState
- Current state of the associated session in AMP vprocs in decreasing
priority:
State Description ABORTING Transaction being rolled back; session aborting. BLOCKED Background activity in progress; last request on hold until background activity is completed. ACTIVE Normal, on-going activity. IDLE No work in progress on any AMP. UNKNOWN No recorded activity since monitoring began. - AMPCPUSec
- Current elapsed CPU time, in seconds, used on all AMPs by the associated session for executing requests. For example, for Teradata SQL requests, this is the time spent by the database actively working or rolling back an aborted transaction. This does not include any PDE CPU time spent handling database requests.
- AMPIO
- Current number of logical Reads and Writes issued across all AMPs by the associated session.
- ReqSpool
- Current spool used by current request across all AMPs, expressed in bytes.
- Blk1HostId
- Blk2HostId
- Blk3HostId
- Logical host ID of a session causing a block. This value is derived from equating the transactions causing a database lock conflict to the sessions that issued those transactions. The Blk_x_HostId, in combination with Blk_x_SessNo, uniquely identifies the session that is causing a block.
- Blk1SessNo
- Blk2SessNo
- Blk3SessNo
- Number of the session causing a block. This value is derived from associating the transactions causing a lock conflict to the sessions that issued those transactions. The Blk_x_SessNo in combination with Blk_x_HostId uniquely identifies the session causing a block.
- Blk1UserId
- Blk2UserId
- Blk3UserId
- ID of the user or host utility job preventing the session from being granted a lock. The user ID is the only information available about who placed the lock.
- Blk1Lmode
- Blk2Lmode
- Blk3Lmode
- Mode (severity) of the lock involved in causing a block:
- E = Exclusive
- W = Write
- R = Read
- A = Access
- Blk1Otype
- Blk2Otype
- Blk3Otype
- Type of object whose lock is causing the session described by the
associated row to be blocked:
- D = Database
- T= Table
- R = Row hash
- TP = Table Partition range
- RP = RowHash in Partition range
- RK = RowHash in one partition
- RN = RowKey range
- Blk1ObjDBID
- Blk2ObjDBID
- Blk3ObjDBID
- Unique ID of the database object over which a lock conflict is preventing the session from being granted a lock.
- Blk1ObjTID
- Blk2ObjTID
- Blk3ObjTID
- Unique ID of the table object over which a lock conflict is preventing the session from being granted a lock.
- Blk1Status
- Blk2Status
- Blk3Status
- Status of lock causing a block:
- W= Waiting
- G = Granted
- MoreBlockers
- Indicator of more lock conflicts:
- Blank = Blk x information is a complete list of sessions blocking the session described.
- Asterisk (*) = Additional sessions are blocking the session described.
- LogonSource
- Logon source information. At logon time, this information is optionally supplied by the Teradata Director Program or the Gateway to further identify the physical or logical location of the session, the logon user name, and the interface under which the session was initiated. (For example, the data string may include DBC as the user ID and BTEQ as the interface.)
- HotAmpnCPU
- Where n in [1, 3]: CPU time of the nth highest CPU used AMP during the collection interval.
- HotAmpnCPUId
- Where n in [1, 3]: Vproc ID of the nth highest CPU used AMP for the last session collection interval.
- HotAmpnIO
- Where n in [1, 3]: I/O count of the nth highest I/O used AMP during the collection interval.
- HotAmpnIOId
- Where n in [1, 3]: Vproc ID of the nth highest I/O used AMP for the last session collection interval.
- LowAmpnCPU
- Where n in [1, 3]: CPU time of the nth lowest CPU used AMP during the collection interval.
- LowAmpnCPUId
- Where n in [1, 3]: Vproc ID of the nth lowest CPU used AMP for the last session collection interval.
- LowAmpnIO
- Where n in [1, 3]: I/O count of the nth lowest I/O used AMP during the collection interval.
- LowAmpnIOId
- Where n in [1, 3]: Vproc ID of the nth lowest I/O used AMP for the last session collection interval.
- AvgAmpCPUSec
- Average AMP CPU usage for the last session collection interval. The average is calculated as the sum of CPU usage for all amps participating divided by the number of online AMPs.
- AvgAmpIOCnt
- Average AMP I/O usage for the last session collection interval. The average is calculated as the sum of I/O usage for all AMPs participating divided by the number of online AMPs.
- AmpCount
- Current number of AMPs executing on the associated node.
- TempSpaceUsg
- Total amount, in bytes, of temporary space used by the session.
- ReqStartTime
- Date and time of the current request on the session started, typically formatted for display as yyyy/mm/dd 99:99:99.99, which represents the year, month, day hours: minutes: seconds.
- ReqCPU
- Total CPU usage by the current SQL request on the session on all AMPs. This value contains proper request-level statistics for DBC/SQL sessions running SQL requests only. Ignore the value returned in this field for other types of sessions, such as DBC/SQL sessions linked to a utility job.
- ReqIO
- Total number of accesses by the current SQL request for the session on all AMPs. This value contains proper request-level statistics for DBC/SQL sessions running SQL requests only. Ignore the value returned in this field for other types of sessions, such as DBC/SQL sessions linked to a utility job.
- ReqNo
- Active request number.
- WlcId
- Workload ID associated with the specified request.
- DontReclassifyFlag
- Flag indicating that the next request on the session is not classified, but uses the workload ID (WlcId) already assigned to the session. This occurs if this is a utility session or a WlcId was assigned to the session using the TDWMAssignWD function or the TDWM WD ASSIGNMENT request. For more information on these APIs, see TDWMAssignWD and TDWM WD ASSIGNMENT.
- ProxyUser
- Name of the proxy user in a trusted session.
- CPUDecayLevel
- Current most severe decay level as reached due to CPU usage.
- IODecayLevel
- Current most severe decay level as reached due to I/O usage.
- TacticalCPUException
- Number of nodes that encountered a CPU exception.
- TacticalIOException
- Number of nodes that encountered an I/O exception.
- ReqIOKB
- Total logical I/O usage in KB.
- ReqPhysIO
- Number of physical I/Os.
- ReqPhysIOKB
- Physical I/O usage in KB.
- ReqStepsCompletedCnt
- Count of completed steps for the current request. If there is no change in ReqStepsCompletedCnt from the previous Monitor Session collection, this indicates that there are no new steps completed.
- RedriveProtection
- Redrive protection type:
- CurrentRedriveParticipation
- T = Redrive functionality is enabled (database restarts are transparent to applications and users)
- F = Redrive functionality is disabled (database restarts are not transparent to applications and users)
- ReqRedriveSpoolSpace
- Persistent spool space for the current request.
- BlockerSessionCnt
- Total number of blocker sessions for the session. This field returns only the first three blocker sessions in the record parcel. There are no additional blocker sessions if there are more than three blocker sessions.
- ReqTblOpBytesIn
- The total number of bytes transferred into the database from a foreign server for the current request through one or more table operators.
- ReqTblOpBytesOut
- The total number of bytes transferred out of the database and into a foreign server for the current request through one or more table operators.
- ProxyUserId
- The UserID charged for SPOOL and TEMP space if being charged to the proxy user.
- ZoneId
- The unique identifier of the zone.
- ReqHotAmpCPU
- The CPU time of the highest CPU used AMP during the life of the current request on the session.
- ReqHotAmpCPUId
- Vproc ID of the highest CPU used AMP for the current request.
- ReqHotAmpIO
- I/O count of the highest I/O used AMP during the life of the current request on the session.
- ReqHotAmpIOId
- Vproc ID of the highest I/O used AMP for the current request.
- ReqInvolvedAMPCnt
- The number of AMPs involved in processing the current request.
- ReqFirstRespTime
- Date and time that the first response of the current request on the session is ready. The response may be held to meet the TASM Minimum Response Time.
- ReqLocalQueryStatus
- The current state of the Unified Data Architecture (UDA) query.
- ReqRemoteHostId
- Host ID of the remote system.
- ReqRemoteSessionId
- Session ID of the executing remote query.
- ReqRemoteRequestId
- Request ID of the executing remote query.
- ReqRemoteQueryId
- Query ID of the executing remote query.
- ReqHotAmpSpool
- Spool value of the highest spool used AMP during the life of the current request on the session.
- ReqHotAmpSpoolId
- Vproc ID of the highest spool used AMP for the current request.
- ReqMapNo
- Map number for the largest map the request is using.
- ReqMaxNumMapAMPs
- Number of AMPs in the largest contiguous map used by the request.
- ReqMinNumMapAMPs
- Number of AMPs in the smallest contiguous map used by the request.
- ReqSysDefNumMapAMPs
- Number of AMPs in the system-default map used by the request.
- ReqRemoteHostIp
- Host IP address of the remote system.
- ReqServerName
- Name of the foreign server.
- ReqFlexReleased
- The TDWM Flex Throttle feature detects available system resources, overrides existing workload throttle limits, and automatically releases queries from the delay queue. This minimizes the DBA manually managing the TASM delay queue. Only Workload throttles are overridden; all System level throttles are still honored.
- ReqAdmissionTime
- Returns the date and time when a request is admitted into the system by TDWM.
- ParentSessionNo
- Only available in the detailed view and for compute clusters. This is an integer that links the query running from the primary cluster to the compute cluster. The Parent Session is the session from the primary cluster that correlates to this SessionNo from the compute cluster.
- Type
- Only available in the detailed view. Identifies the group type (compute cluster or primary cluster).
- Id
- Only available in the detailed view. Provides the TOS identifier the of the group (useful to identify compute clusters).
- Name
- Only available in the detailed view. Provides the name of the compute cluster.
- Group
- Only available in the detailed view. Provides the name of the compute group.