MonitorMySessions Syntax Elements - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Syntax Elements

HostId
Logical Host ID associated with a PE or session.
For a PE, HostId identifies one of the hosts or LANs associated with the described PE.
For a session, the combination of a host ID and a session number uniquely identifies a user session on the system.
This value is NULL for AMPs. A value of zero represents the Supervisor window.
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.
For sessions in Teradata SQL partitions, this value is identical to the LogonPENo. For sessions in FastLoad or MultiLoad partitions, this is the AMP that initially processes the data being loaded.
If a RunVprocNo value of -1 in record mode or NULL in indicator mode is returned by MonitorMySessions for FastLoad, MultiLoad, or FastExport sessions, this indicates that the session is in the process of starting up.
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. See the MONITOR SESSION PEState field for a list of the PEState values.
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) 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.
This information supplies the parent-child relationship for sessions involved with FastLoad and MultiLoad jobs.
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.

This value is valid only when associated with Teradata SQL and MONITOR partition sessions.

XActCount
Number of explicit and implicit transactions run by the session.
This value is valid only when returned for Teradata SQL sessions, and is NULL for all other partition sessions. For this value, you must make a request for data before completion of the first collection period that follows either a system outage or a change in the ResMonitor rate.
ReqCount
Number of requests (Tequel Start Request [TSR] messages) initiated by the session.
This value is NULL when a request for data is made before completion of the first collection period following either a system outage or change in the ResMonitor rate.
ReqCacheHits
Number of times this session processed a request using information from the Teradata SQL Parser request cache (number of times there was a request cache hit).
This value is valid only for Teradata SQL sessions, and is NULL for all other partition sessions. This value is also NULL when a request for data is made before completion of the first collection period following either a system outage or a change in the ResMonitor rate.
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.
This value is NULL when a request for data is made before completion of the first collection period following either a system outage or a change in the SesMonitorLoc or SesMonitorSys rate.
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.
This value is NULL when a request for data is made before completion of the first collection period following either a system outage or a change in the ResMonitor rate.
AMPIO
Current number of logical Reads and Writes issued across all AMPs by the associated session.
This value is NULL when a request for data is made before completion of the first collection period following either a system outage or a change in the ResMonitor rate.
ReqSpool
Current spool used by current request across all AMPs, expressed in bytes.
This value is NULL when a request for data is made before completion of the first collection period following either a system outage or a change in the ResMonitor rate.
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.
This value is NULL if:
  • The host ID is not available.
  • The session does not have an AMPState of BLOCKED.

If the Blk_x_HostId, Blk_x_SessNo, and Blk_x_UserID values all return as NULLs and AMPState is BLOCKED, a host utility lock left over after the session holding the lock was aborted or logged off. The lock was never released, and no blocking information is available because the session no longer exists.

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.
This value is NULL if:
  • SessNo is not available.
  • Session does not have an AMPState of BLOCKED.
  • A request for data is made before completion of the first collection period following either a system outage or a change in the SesMonitorLoc or SesMonitorSys rate.

If the Blk_x_HostId, Blk_x_SessNo, and Blk_x_UserID values all return as NULLs and AMPState is BLOCKED, a host utility lock left over after the session holding the lock was aborted or logged off. The lock was never released, and no blocking information is available because the session no longer exists.

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.
This value is NULL if:
  • Session does not have an AMPState of BLOCKED.
  • A request for data is made before completion of the first collection period following either a system outage or a change in the SesMonitorLoc or SesMonitorSys rate.

If the Blk_x_HostId, Blk_x_SessNo, and Blk_x_UserID values all return as NULLs and AMPState is BLOCKED, a host utility lock left over after the session holding the lock was aborted or logged off. The lock was never released, and no blocking information is available because the session no longer exists. If you need the user name who placed the HUT lock, contact Teradata Support.

Blk1Lmode
Blk2Lmode
Blk3Lmode
Mode (severity) of the lock involved in causing a block:
  • E = Exclusive
  • W = Write
  • R = Read
  • A = Access
Locks are reported in decreasing order of severity because removing the most severe lock conflict may eliminate the source of the lock conflict.
This value is NULL if:
  • The session does not have an AMPState of BLOCKED.
  • A request for data is made before completion of the first collection period following either a system outage or a change in the SesMonitorLoc or SesMonitorSys rate.
A session may be blocked by either a granted lock or an ungranted lock that precedes the blocked lock in the queue and is in conflict with the lock requested by this blocked session. For information on whether the lock is granted, see the MONITOR SESSION fields: Blk1Status, Blk2Status, and Blk3Status.
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
However, this object is not necessarily the type of object the blocked session is trying to access. For example, if the session is requesting a row hash lock, the blocking object may be a database, table, or row hash.
This value is NULL if:
  • Session does not have an AMPState of BLOCKED.
  • A request for data is made before completion of the first collection period following either a system outage or a change in the SesMonitorLoc or SesMonitorSys rate.
For a Table T, User A can block User B with a table-level lock on Table T on AMP_1 and with a Row Hash Level lock on that same Table T on AMP_2. When that occurs, the only lock conflict reported is that User B is blocked by User A on a table.
Blk1ObjDBID
Blk2ObjDBID
Blk3ObjDBID
Unique ID of the database object over which a lock conflict is preventing the session from being granted a lock.
Within the database system, Database ID is equivalent to User ID. Typically, User ID 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, Database ID can identify either a user or database name.
This value is NULL if:
  • The session does not have an AMPState of BLOCKED.
  • A request for data is made before completion of the first collection period following either a system outage or a change in the SesMonitorLoc or SesMonitorSys rate.
Blk1ObjTID
Blk2ObjTID
Blk3ObjTID
Unique ID of the table object over which a lock conflict is preventing the session from being granted a lock.
This value is NULL if:
  • The session does not have an AMPState of BLOCKED.
  • The Blk_x_OType is D.
  • A request for data is made before completion of the first collection period following either a system outage or a change in the SesMonitorLoc or SesMonitorSys rate.
Blk1Status
Blk2Status
Blk3Status
Status of lock causing a block:
  • W= Waiting
  • G = Granted
This value is NULL if:
  • Session does not have an AMPState of BLOCKED.
  • A request for data is made before completion of the first collection period following either a system outage or a change in the SesMonitorLoc or SesMonitorSys rate.
A lock request may be blocked by either a granted lock or an ungranted lock that precedes the blocked lock request in the queue and is in conflict with it.
A status of Waiting has a higher priority than that of Granted when there is more than one lock involved. For example, for a given object and a given session, a session that is blocked by a Waiting lock on one AMP and a Granted lock on another AMP has Waiting reported as its status.
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.
This value is NULL if:
  • The state of the session is not BLOCKED.
  • A request for data is made before completion of the first collection period following either a system outage or a change in the SesMonitorLoc or SesMonitorSys rate.
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.)
A two-byte value precedes the LogonSource data to indicate the length of the string. The length value is zero if LogonSource is NULL.
HotAmpnCPU
Where n in [1, 3]: CPU time of the nth highest CPU used AMP during the collection interval.
Where n in [1, 2]: This value is NULL if the request is made before the collection period expires.
Where n = 3: This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
HotAmpnCPUId
Where n in [1, 3]: Vproc ID of the nth highest CPU used AMP for the last session collection interval.
Where n in [1, 2]: This value is NULL if the request is made before the collection period expires.
Where n = 3: This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
HotAmpnIO
Where n in [1, 3]: I/O count of the nth highest I/O used AMP during the collection interval.
Where n in [1, 2]: This value is NULL if the request is made before the collection period expires.
Where n = 3: This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
HotAmpnIOId
Where n in [1, 3]: Vproc ID of the nth highest I/O used AMP for the last session collection interval.
Where n in [1, 2]: This value is NULL if the request is made before the collection period expires.
Where n = 3: This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
LowAmpnCPU
Where n in [1, 3]: CPU time of the nth lowest CPU used AMP during the collection interval.
Where n in [1, 2]: This value is NULL if the request is made before the collection period expires.
Where n = 3: This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
LowAmpnCPUId
Where n in [1, 3]: Vproc ID of the nth lowest CPU used AMP for the last session collection interval.
Where n in [1, 2]: This value is NULL if the request is made before the collection period expires.
Where n = 3: This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
LowAmpnIO
Where n in [1, 3]: I/O count of the nth lowest I/O used AMP during the collection interval.
Where n in [1, 2]: This value is NULL if the request is made before the collection period expires.
Where n = 3: This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
LowAmpnIOId
Where n in [1, 3]: Vproc ID of the nth lowest I/O used AMP for the last session collection interval.
Where n in [1, 2]: This value is NULL if the request is made before the collection period expires.
Where n = 3: This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
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.
This value is NULL if the request is made before the collection period expires.
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.
This value is NULL if the request is made before the collection period expires.
AmpCount
Current number of AMPs executing on the associated node.
TempSpaceUsg
Total amount, in bytes, of temporary space used by the session.
This value is NULL if the session did not materialize any temporary tables.
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.
This field is equivalent to the MONITOR SESSION RequestAmpCPU field.
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.
This field is equivalent to the MONITOR SESSION RequestAmpI/O field.
In the early phase of a request in parsing state, when PEState = PARSING, the request number may not be available and returned as zero or NULL. The active request number is available on the next collection.
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. See Teradata Dynamic Workload Management APIs: PM/APIs.
ProxyUser
Name of the proxy user in a trusted session.
CPUDecayLevel
Current most severe decay level, as reached due to CPU usage.
Nodes can be at different levels of decay (for example, 0, 1, or 2).
IODecayLevel
Current most severe decay level, as reached due to I/O usage.
Nodes can be at different levels of decay (for example, 0, 1, or 2).
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. No change in ReqStepsCompletedCnt from the previous Monitor Session collection indicates no new steps completed.
RedriveProtection
Redrive protection type:
  • ' ' = No Redrive protection. The session does not participate in Redrive and database restarts are not transparent to applications and users.
  • MN = Memory-based Redrive protection, no fallback spools
CurrentRedriveParticipation
Indicates if the session is participating in Redrive. Sessions that use Redrive can enable or disable the functionality using the REDRIVE reserved query band. Possible values:
  • 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. Unlike the MONITOR SESSION request, 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.
The request may involve one or multiple table operator executions. The ReqTblOpBytesIn output parameter shows bytes transferred across all invocations within the request.
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.
The request may involve one or multiple table operator executions. The ReqTblOpBytesOut output parameter shows bytes transferred across all invocations within the request.
ProxyUserId
The user ID 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.
This value is NULL if no request is running on the session.
ReqHotAmpSpoolId
Vproc ID of the highest spool used AMP for the current request.
This value is NULL if no request is running on the session.
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.
Return values:
  • 0: Indicates that the request was not released by TDWM Flex Throttles.
  • 1: Indicates that the request was released by TDWM Flex Throttles.
ReqAdmissionTime
Returns the date and time when a request is admitted into the system by TDWM.
QueryID
Query ID of the session being monitored.
CollectionTime
Database time of when the session cache was last refreshed.