MONITOR SESSION Request | VantageCloud Lake - MONITOR SESSION

Returns session or request resource usage statistics.

Input Data

Element	Data Type	Description
IndByte	BYTE	Indicator bits that specify which fields to treat as NULL if you are using indicator mode. Each bit in the byte corresponds to one field in the input data. If data is supplied for that field, set the bit to zero. If the data for that field is NULL (that is, there is no data supplied for that field), set the bit to 1. The IndByte field is only required if the CLIv2 request is submitted in indicator mode.
mon_ver_id	SMALLINT NOT NULL	MONITOR software version ID. This can be version 2 or later. Version 6 or later can be used to determine if a utility session is on the Teradata dynamic workload management software delay queue. For information on returning the utility delay queue, see TDWMGetDelayedUtilities. For a general explanation of monitor version choices, see MONITOR VERSION.
host_id	SMALLINT	Logical ID of a host (or client) with sessions logged on. host_id cannot exceed 1023. A host_id of zero identifies the system Console ID of the host on which sessions are running.
session_no	INTEGER	Session number. The session number combined with the host_id represents a unique session ID.
user_name	VARCHAR (512)	Name of user who is running the sessions.

If you do not specify host_id, user_name, or session_no, or group_id, all hosts, all users, or all sessions, or all groups are monitored. For example, if you specify 127 for host_id, PEDERSON for user_name, and do not specify session_no or group_id, the MONITOR SESSION request reports on all sessions logged on from host 127 as user PEDERSON.
NULL in any field, with the exception of mon_ver_id, indicates a match for all potential values of that field. A NULL for mon_ver_id isssues an error. IndicData parcels are used to specify the output fields that are null. For additional information on IndicData, see Creating a Request with CLIv2.
For information on the data returned for each monitor version, see Response Groups.

Monitor Privileges

To use this request, you must have MONSESSION privilege as part of your default role or this privilege must be granted directly to you.

For more information on roles and privileges, see:

Using Roles to Manage User Privileges
Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html

Usage Notes - MONITOR SESSION

Before using this request, see Impact of Object Name Length on PM/API Requests.

Use this information, when a job stops responding because of unavailable resources, to find the following:

The user causing a block
The locked database or table
System usage data on a session-by-session basis

In the MONITOR partition, each job is a session. You can run the MONITOR SESSION request to query:

All hosts (or clients) with all sessions
Single host (or client) with all sessions
Single host (or client) with all sessions for a given user name
Single host (or client) with a single session and a given user name

MONITOR SESSION requests return data:

Identical for all sessions
Specific to a session

MONITOR SESSION requests return accumulated session statistics commencing with the last time that one of the following events impacted a session:

The time that a session logged on.
The time that a session-switch changed the vproc with Session Control responsibility for a session.
The time that a system outage or TPA restart occurred.
The time that system or local rate was set to a nonzero value.

A MONITOR SESSION request can also be used to track session-level AMP usage and provide data on the three highest and lowest CPU/IO used AMPs. This can help identify:

Skewed data
Possible hardware problems
Possible database bugs

You can use the data returned by the MONITOR SESSION request as input for the IDENTIFY request to determine lock activity. For more information, see IDENTIFY.

Be aware of the following rules:

To collect statistics, set the system or local rate to a nonzero value.
Data on a session is reported only for the last complete collection period. For example, assume the collection rate is 600 seconds (or 10 minutes) and that you issue a MONITOR SESSION request at 9:00 a.m. If the user for whom you are seeking data logged onto the system at 9:01 a.m., no session data is available until the next MONITOR SESSION request is issued at 9:10 a.m. (on completion of the current collection period). A MONITOR SESSION request made at 9:05 a.m. (after the user logged on at 9:01 a.m.) reports only on sessions that stayed logged on at 9:00 a.m.
When a session-switch changes the processor with session control responsibility for a session, previous data in memory is lost and the data collection for the affected session starts over again.
A system outage occurs clears out the previous data and data collection starts again.

The RSS data loss affects only specific sessions when a session logs off or a PE goes down, forcing all sessions on that PE to switch to another PE. In other cases, such as a system outage, the data loss affects all sessions.

If a system failure occurs, the response to subsequent MONITOR SESSION requests contains a warning message. For more information, see Database Messages.

The MONITOR SESSION request returns an ActivityCount (one of the fields in the Success parcel indicating total number of records selected, updated, and so forth) equal to 0 or -1.

ActivityCount	Meaning
0	Requested combination of logical HostId, UserName, and SessionNo does not match a session.
-1	Request generates an unknown number of Response Rows. The application must continue to gather data until encountering the EndStatement parcel. A value of -1 is used because ActivityCount cannot be determined when the request is run. Because the response is collected from data shared by all running Monitor sessions, that data can be updated while a request is in progress. If that update adds or removes information on sessions, the activity count calculated at the beginning of a session query response generation does not become valid by the end of the response processing for that session. Rather than return possibly incorrect data, the ActivityCount is set to indicate that the count is unknown.

ActivityCount

Meaning

0

Requested combination of logical HostId, UserName, and SessionNo does not match a session.

-1

Request generates an unknown number of Response Rows. The application must continue to gather data until encountering the EndStatement parcel.

A value of -1 is used because ActivityCount cannot be determined when the request is run. Because the response is collected from data shared by all running Monitor sessions, that data can be updated while a request is in progress. If that update adds or removes information on sessions, the activity count calculated at the beginning of a session query response generation does not become valid by the end of the response processing for that session. Rather than return possibly incorrect data, the ActivityCount is set to indicate that the count is unknown.

A Trusted Session enables a middle-tier application to switch the user on an already active database session to another user (proxy user). When the user is switched, all subsequent requests uses the privileges and session attributes of the proxy user. Monitor Session returns the logon user name and ID in the UserName and UserId fields and the proxy user name and ID in the ProxyUserName and ProxyUserId fields.

The following CPU fields in the MONITOR SESSION response are affected by the MonSesCPUNormalization field:

AMPCPUSec
AvgAmpCPUSec
HotAmp1CPU
HotAmp2CPU
HotAmp3CPU
LowAmp1CPU
LowAmp2CPU
LowAmp3CPU
PECPUSec
RequestAmpCPU

The MonSesCPUNormalization field controls whether normalized or non-normalized statistical CPU data is reported by the MONITOR SESSION request, and by the functions MonitorMySessions and MonitorSession.

For a complete description of these fields, see the "Response Groups" table in this topic..

You can also see MonitorMySessions or MonitorSession for a list of these CPU fields.

Collecting Session Data

After a collection interval is set to a nonzero rate, session usage data is accumulated in AMP and PE storage areas (regardless of the rate set). When a user makes a MONITOR request, the central coordinator task determines if current data is necessary. If current data is required, the central coordinator task directs the processors to transmit data from processor storage areas on the AMPs to the main memory repository on each PE where each PE is simultaneously collecting its own session data. Otherwise, data in the main PE repositories is considered current and is sent to the user.

Current data is associated with an internal timestamp not visible to the user. Every MONITOR SESSION request issued by a user is associated with a session-level data collection rate. This rate is the local rate if a local rate has been set. Otherwise, this rate is the global rate.

When a user runs a MONITOR SESSION request, the central coordinator task checks the age of the current session-level data (current time minus the data timestamp). Depending on the age determination, action is then taken on the data as shown in the following table.

Data in PE Memory Repository	Result
Considered current (the age of the data is less than or equal to the session collection rate)	Data is returned to the user.
Not considered current (the age of the data is greater than the session collection rate)	Coordinator task forces a data update (causing its internal timestamp to be reset to the current time) and returns the updated data to the user.

Unreported Session Partitions

Resource usage in session partitions may not be reported or fully reported. MONITOR SESSION requests do not report statistics on session resources used by the DBCUTIL utility. Although resource usage is reported, not all resource usage data is accounted for. These restrictions may affect the following data returned from a MONITOR SESSION request:

AMPCPUSec
AMPIO
Request_AMPSpool
AMPState
PECPUSec
PEState
ReqCount

As an example of how the returned data is affected, both AMPState and PEState data values may be UNKNOWN if I/O is done on behalf of the unreported partitions. As another example, in a Teradata SQL session, PECPUSec spent in a PE do not include time spent in Gateway communications processing.

Down Processor

When a previously active PE is down because of a system outage, the data returned for those sessions logged on to the down processor may not be meaningful. If you encounter a system outage, pay attention to the following information, because the PE data does not change:

HostId
LogonPENo
SessionNo
UserName
UserAccount
UserID
LSN
LogonTime
LogonDate
PartName
PEState
LogonSource

Internal Sessions

Internal sessions are a part of the database software and cannot be started or aborted from the client. For example, the operation sending a message from a parser to an AMP is an internal session.

Internal sessions can cause problems for these reasons:

Internal sessions may be blocking an important session.
Internal sessions are hard to recognize.
Internal sessions may be blocked by other requests.

Internal sessions that are blocked are reported by MONITOR SESSION. These include:

Internal sessions associated with the user that show the blocking activity on the user session itself.
DBQL/Teradata dynamic workload management software artificial internal sessions that appear in the Monitor Session output and exist only to show blocked DBQL/Teradata dynamic workload management software internal express requests.
For information on handling blocked DBQL/Teradata dynamic workload management software internal express requests, see DBQL Tables.

You must determine what to do about a session that is blocking an important activity.

The following fields (returned by a MONITOR SESSION request) may provide information about internal sessions blocking other sessions:

Blk_x_HostId
Blk_x_SessNo
Blk_x_UserID

Internal sessions may not be easily recognized from data returned in Blk_x fields, any or all of which may be NULL. You may mistake an internal session with a NULL Blk_x_SessNo for a client utility session. All three fields can have legitimate-looking values. You can frequently recognize an internal session by a value of zero in the Blk_x_HostId field, but this is not guaranteed, because a Teradata SQL session started from the system Console running DBW has a value of zero in the Blk_x_HostId field.

The following matrix provides guidelines for recognizing internal sessions the HostId, SessNo, and UserID Blk_x fields internal sessions.

Fields and Values	Session Type
HostId = NULL SessNo = NULL UserID = NULL	Internal
HostId = NULL SessNo = NULL UserID ≠ NULL	Idle Client utility
HostId ≠ NULL SessNo ≠ NULL UserID ≠ NULL	A MONITOR SESSION request does not return a record for an internal session. If you specify internal session in an IDENTIFY request to specify the name of the session causing the lock, the session returns an error message.

Situation	Result
Internal session is blocking an important session.	You cannot stop the internal session.
Internal session lock request is waiting.	You can stop the work of the sessions that are blocking the internal sessions.
Internal session lock request is granted.	You must wait for request to complete.

CLIv2 Response Parcels

The MONITOR SESSION request is treated internally as a multiple statement request with each statement generating a response. The multiple statement response returned from the database contains the following sequence of parcel types:

Parcel Sequence	Parcel Number	Length (Bytes)	Comments and Key Parcel Body Fields
Success	8	18 to 273	StatementNo = 1 ActivityCount = 1 ActivityType = 84 (PCLMONSESS)
DataInfo	71	6 to 64100	(Optional). This parcel is present if request was IndicData parcel.
Record	10	5 to 64100 (record mode) 6 to 64100 (indicator mode)	Returns data in Record or Indicator mode depending on the request (for example, Data or IndicData).
EndStatement	11	6	StatementNo = 2-byte integer
Success	8	18 to 273	StatementNo = 2 ActivityCount = -1 or 0 ActivityType = 84 (PCLMONSESS)
DataInfo	71	6 to 64100	(Optional) This parcel is present if request was IndicData parcel.
Record	10	5 to 64100 (record mode) 6 to 64100 (indicator mode)	Returns data in Record or Indicator mode depending on the request (for example, Data or IndicData). Contains data describing the session.
EndStatement	11	6	StatementNo = 2-byte integer, value = 2
Success	8	18 to 273	StatementNo = 3 ActivityType = 84 (PCLMONSESS)
DataInfo	71	6 to 64100	(Optional) This parcel is present if request was IndicData parcel.
Record	10	5 to 64100 (record mode) 6 to 64100 (indicator mode)	Returns data in Record or Indicator mode depending on the request (for example, Data or IndicData). Contains blocker data describing the session.
EndStatement	11	6	StatementNo = 2-byte integer, value = 3
EndRequest	12	4	None

Response

Each of the following statement types corresponds to a ResultSet returned by the Teradata JDBC Driver, and each statement type field corresponds to a ResultSet column. For more information on ResultSets, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html.

Statement 1

The response to the first statement returns a Record parcel containing the following fields:

Field/Column Name	Data Type	Description
CollectionInterval	SMALLINT NOT NULL	Actual interval in seconds between current and last cache refresh.
CollectionSeqNum	INTEGER NOT NULL	Monitor session cache refresh sequence number.
CollectionDate	DATE NOT NULL	Database date of when the session cache was last refreshed.
CollectionTime	FLOAT NOT NULL	Database time of when the session cache was last refreshed.
SessionRate	SMALLINT NOT NULL	Session rate.
ExceptionInterval	SMALLINT NOT NULL	Exception interval. If TASM Workloads are disabled, this field returns a zero.
SessionRateThreshold	SMALLINT NOT NULL	The PMPC_SessionRateThreshold value.
DBQLFlushRate	SMALLINT NOT NULL	The DBQLFlushRate value.
RedriveProtection	VARCHAR (2) NOT NULL	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

Statement 2

The response to the second statement returns multiple Record parcels that consist of a record for each session in the system. Records are sorted in order of LogonPENo, HostId, and SessionNo.

Response Groups

The MONITOR SESSION request returns groups of response data fields or JDBC ResultSet columns. The following table shows the different values returned from mon_ver_id.

mon_ver_ID	Response Group Returned	Description
2	Group I Data Fields and JDBC ResultSet Columns	Returns data fields concerned primarily with session-level user status.
3	Groups I Data Fields and II and JDBC ResultSet Columns	In addition, returns data fields on session-specific AMP resource usage.
4	Groups I-III Data Fields and JDBC ResultSet Columns	In addition, returns request level usage information.
5	Groups I-IV Data Fields and JDBC ResultSet Columns	Returns data fields concerned with Group I using IV
6	Groups I-IV Data Fields and JDBC ResultSet Columns	Returns data fields concerned with Group I using IV. This value is required for Teradata Dynamic Workload Management PM/APIs.
7 and 8	Groups I-V Data Fields and JDBC ResultSet Columns	Returns data fields concerned with Group I using Group V.
9 - 11	Groups I - VI Data Fields and JDBC ResultSet Columns	Returns data fields concerned with Group I using VI.
12	Groups I - VII Data Fields and JDBC ResultSet Columns	Returns data fields concerned with Group I using VII.
13-14	Groups I - VIII Data Fields and JDBC ResultSet Columns	Returns data fields concerned with Group I using VIII.
15-16	Groups I - IX Data Fields and JDBC ResultSet Columns	Returns data fields concerned with Group I using IX.
17	Groups I - X Data Fields and JDBC ResultSet Columns	Returns data fields concerned with Group I using X.

If monitor version software ID 10 or later is specified, object fields in the input area can be up to 512 bytes in variable length in host character set format. For more information, see "Impact of Object Name Length on PM/API Requests" in System PMPC PM/APIs.

Group I Data Fields and JDBC ResultSet Columns

The Record returns the following Group I values.

Field/Column Name

Data Type

Description

HostId

SMALLINT

Logical host ID associated with a PE or session. For a PE, the Host ID 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.

LogonPENo

SMALLINT

NOT NULL

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

SMALLINT

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 MONITOR SESSION for FastLoad, MultiLoad or FastExport sessions, this indicates that the session is in the process of starting up.

SessionNo

INTEGER

NOT NULL

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.

UserName

VARCHAR (128)

CHARACTER SET UNICODE

NOT NULL

User name of the session.

UserAccount

VARCHAR (128)

CHARACTER SET UNICODE

NOT NULL

Current account for the session.

UserID

INTEGER

NOT NULL

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

INTEGER

NOT NULL

Logon Sequence Number (LSN) associated with session when session logs on. Identifies collection of sessions performing 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.

LogonTime

FLOAT

NOT NULL

Time portion of the information recorded by Session Control when a session logs on. With LogonDate, indicates when the session logged on to the system. T formatted for display as 99:99:99, which represents hours: minutes: seconds.

LogonDate

DATE

NOT NULL

Date portion of the information recorded by Session Control when a session logs on. With LogonTime, indicates when the session logged on to the system.

PartName

VARCHAR (16)

NOT NULL

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.

Reserved2

VARCHAR (2)

This field is not used.

PEState

VARCHAR (18)

Current state of the session within the PE. This session describes PARSING, DISPATCHING, and Monitor activity. The session states are reported in decreasing priority:

Session State	Meaning
DELAYED	The request is either waiting on a queue table for rows to be inserted in to that table or, because of a TASM System Throttle and Session Control rule, the request is on the Teradata dynamic workload management software delay queue.
DEFERRED	A request is being deferred by an arrival rate meter rule.
HOST-RESTART	A restart is in progress for the associated host (or client).
ABORTING	The transaction is being rolled back; the session is ending.
PARSING-WAIT	Waiting for information from the Data Dictionary.
PARSING	The Parser portion of the PE is processing a request.
ELICIT CLIENT DATA	The Dispatcher is eliciting data from the client and sending the data to the AMP.
DISPATCHING	The Dispatcher or Monitor is having a request run.
BLOCKED	Background activity is in progress and the last request is on hold until this background activity is completed.
ACTIVE	Normal, on-going activity is being done by this session.
RESPONSE	The Dispatcher is returning query responses to the session.
IDLE: IN-DOUBT	A session using Two-Phase Commit (2PC) is IN-DOUBT.
IDLE	No work in progress for this session.
QTDELAYED	A session is delayed due to a Queue Table restriction.
SESDELAYED	A utility session is on the Teradata dynamic workload management software delay queue.
UNKNOWN	The Parser, Dispatcher, and Monitor on the PE are unaware of this session.

This value is NULL when a request for data is made before completion of the first collection period that follows either a system outage or a change in the ResMonitor rate.

PECPUsec

FLOAT

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.

This value is NULL when returned for all other sessions.

XActCount

FLOAT

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

FLOAT

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

FLOAT

Number of times this session processed a request using information from the Teradata SQL Parser request cache, specifically, 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

VARCHAR (18)

Current state of the associated session in AMP vprocs in decreasing priority:

Session State	Meaning
ABORTING	The transaction is being rolled back; session is ending.
BLOCKED	Background activity is in progress and the last request is on hold until this background activity is completed.
ACTIVE	Normal, on-going activity is being done by this session.
IDLE	No work in progress for this session on any AMP.
UNKNOWN	No recorded activity by this session 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

FLOAT

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

FLOAT

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.

Request_AmpSpool

FLOAT

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 a system outage or a change in the ResMonitor rate.

Blk_1_HostId,

Blk_2_HostId,

Blk_3_HostId

SMALLINT

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.

Blk_1_SessNo,

Blk_2_SessNo,

Blk_3_SessNo

INTEGER

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:

The SessNo is not available.
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.

Blk_1_UserID,

Blk_2_UserID,

Blk_3_UserID

INTEGER

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:

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.

Blk_1_LMode,

Blk_2_LMode,

Blk_3_LMode

VARCHAR (1)

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 block status MONITOR SESSION fields (Blk_1_Status, Blk_2_Status, and Blk_3_Status) later in this table.

Blk_1_OType,

Blk_2_OType,

Blk_3_OType

VARCHAR(2)

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 can be a database, table, or row hash.

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.

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.

Blk_1_ObjDBId,

Blk_2_ObjDBId,

Blk_3_ObjDBId

INTEGER

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.

Blk_1_ObjTId,

Blk_2_ObjTId,

Blk_3_ObjTId

INTEGER

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.

Blk_1_Status,

Blk_2_Status,

Blk_3_Status

VARCHAR (1)

Status of lock causing a block:

W= Waiting
G = Granted

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

VARCHAR (1)

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. In rare cases, the blocker information in statement 2 and 3 do not represent a complete list of sessions 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

VARCHAR (128)

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

Data strings for TCP/IP sessions are inserted by CLIv2 or the Teradata JDBC Driver, which truncates strings that exceed 128 bytes.

A two-byte value precedes the LogonSource data to indicate the length of the string. The length value is zero if LogonSource is NULL.

TempSpace

FLOAT

Total amount, in bytes, of temporary space used by the session.

This value is NULL if the session did not materialize any temporary tables.

Group II Data Fields and JDBC ResultSet Columns

The Record returns the following Group II values.

The values are NULL if the request is made before the collection period expires.

Field/Column Name	Data Type	Description
HotAmp1CPU	FLOAT	CPU time of the highest CPU used AMP during the collection interval. This value is NULL if the request is made before the collection period expires.
HotAmp2CPU	FLOAT	CPU time of the second highest CPU used AMP during the collection interval. This value is NULL if the request is made before the collection period expires.
HotAmp3CPU	FLOAT	CPU time of the third highest CPU used AMP during the collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
HotAmp1IO	FLOAT	I/O count of the highest I/O used AMP during the collection interval. This value is NULL if the request is made before the collection period expires.
HotAmp2IO	FLOAT	I/O count of the second highest I/O used AMP during the collection interval. This value is NULL if the request is made before the collection period expires.
HotAmp3IO	FLOAT	I/O count of the third highest I/O used AMP for the last collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs in the system.
HotAmp1CPUId	SMALLINT	Vproc ID of the highest CPU used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires.
HotAmp2CPUId	SMALLINT	Vproc ID of the second highest CPU used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires.
HotAmp3CPUId	SMALLINT	Vproc ID of the third highest CPU used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs in the system.
HotAmp1IOId	SMALLINT	Vproc ID of the highest I/O used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires.
HotAmp2IOId	SMALLINT	Vproc ID of the second highest I/O used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires.
HotAmp3IOId	SMALLINT	Vproc ID of the third highest I/O used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs in the system.
LowAmp1CPU	FLOAT	CPU time of the lowest CPU used AMP during the collection interval. This value is NULL if the request is made before the collection period expires.
LowAmp2CPU	FLOAT	CPU time of the second lowest CPU used AMP during the collection interval. This value is NULL if the request is made before the collection period expires.
LowAmp3CPU	FLOAT	CPU time of the third lowest CPU used AMP during the collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
LowAmp1IO	FLOAT	I/O count of the lowest I/O used AMP during the collection interval. This value is NULL if the request is made before the collection period expires.
LowAmp2IO	FLOAT	I/O count of the second lowest I/O used AMP during the collection interval. This value is NULL if the request is made before the collection period expires.
LowAmp3IO	FLOAT	I/O count of the third lowest I/O used AMP during the collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
LowAmp1CPUId	SMALLINT	Vproc ID of the lowest CPU used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires.
LowAmp2CPUId	SMALLINT	Vproc ID of the second lowest CPU used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires.
LowAmp3CPUId	SMALLINT	Vproc ID of the third lowest CPU used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
LowAmp1IOId	SMALLINT	Vproc ID of the lowest I/O used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires.
LowAmp2IOId	SMALLINT	Vproc ID of the second lowest I/O used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires.
LowAmp3IOId	SMALLINT	Vproc ID of the third lowest I/O used AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system.
UpAMPCount	SMALLINT	Total number of AMPs participating for this session during the last session collection interval. This value is NULL if the request is made before the collection period expires.
AvgAmpCPUSec	FLOAT	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	FLOAT	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.

Group III Data Fields and JDBC ResultSet Columns

The Record returns the following Group III values.

Field/Column Name	Data Type	Description
RequestStartTime	FLOAT	Time of the current request on the session started.
RequestStartDate	DATE	Date of the current request on the session started.
RequestAmpCPU	FLOAT	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 MonitorSession ReqCPU field.
RequestAmpI/O	FLOAT	Total number of accesses 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 MonitorSession ReqIO field.

Group IV Data Fields and JDBC ResultSet Columns

The Record returns the following Group IV values.

Field/Column Name	Data Type	Description
Request Number	INTEGER	Active request number. If no request is running, this field displays a value of zero or NULL in indicator mode. In the early phase of a request in parsing state, when PEState = PARSING, the request number may not be available and may be returned as zero or NULL. The active request number is available on the next collection.
WDId	INTEGER	Workload ID associated with the specified request.
Classification Mode	SMALLINT	Indicator if a running query or session or future queries are forced into a WD. The classification mode is valid only for DBC/SQL sessions and if the Teradata dynamic workload management software is enabled. If enabled, the Teradata dynamic workload management software classifies all incoming queries into a WD. If the Teradata dynamic workload management software is enabled, the value is one of the following: 0 = Automatic 1 = Manual at Request Level 2 = Manual at Session Level If the Teradata dynamic workload management software is disabled and the DBC/SQL session is not a valid session, the value is 255 or NULL.

Field/Column Name

Data Type

Description

Request Number

INTEGER

Active request number.

If no request is running, this field displays a value of zero or NULL in indicator mode.

In the early phase of a request in parsing state, when PEState = PARSING, the request number may not be available and may be returned as zero or NULL. The active request number is available on the next collection.

WDId

INTEGER

Workload ID associated with the specified request.

Classification Mode

SMALLINT

Indicator if a running query or session or future queries are forced into a WD.

The classification mode is valid only for DBC/SQL sessions and if the Teradata dynamic workload management software is enabled. If enabled, the Teradata dynamic workload management software classifies all incoming queries into a WD.

If the Teradata dynamic workload management software is enabled, the value is one of the following:

0 = Automatic
1 = Manual at Request Level
2 = Manual at Session Level

If the Teradata dynamic workload management software is disabled and the DBC/SQL session is not a valid session, the value is 255 or NULL.

Group V Data Field and JDBC ResultSet Columns

The Record returns the following Group V value.

Field/Column Name	Data Type	Description
ProxyUser	VARCHAR (128) CHARACTER SET UNICODE	Name of a proxy user in a trusted session.

Group VI Data Fields and JDBC ResultSet Columns

The Record returns the following Group VI values.

Field/Column Name	Data Type	Description
CPUDecayLevel	SMALLINT	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	SMALLINT	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	INTEGER	Number of nodes that encountered a CPU exception.
TacticalIOException	INTEGER	Number of nodes that encountered an I/O exception.
ReqIOKB	FLOAT	Total logical I/O usage in KB.
ReqPhysIO	FLOAT	Number of physical I/Os.
ReqPhysIOKB	FLOAT	Physical I/O usage in KB.
ReqStepsCompletedCnt	INTEGER	Count of completed steps for the current request. If there is no change in the ReqStepsCompletedCnt field from the previous Monitor Session collection, this indicates that there are no new steps completed.
CurrentRedriveParticipation	VARCHAR (1)	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	FLOAT	Persistent spool space for the current request.
BlockerSessionCnt	SMALLINT	Total number of blocker sessions. If there are more than three blocker sessions, this field returns the blocking sessions in one or more record parcels in statement 3. This field returns only the first three blocking sessions in statement 2. This output parameter is available on monitor software version ID 11 or later.
ReqTblOpBytesIn	FLOAT	The total number of bytes transferred into the database from a foreign server for the current request using 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. This output parameter is available on monitor software version ID 11 or later.
ReqTblOpBytesOut	FLOAT	The total number of bytes transferred out of the database and into a foreign server for the current request using 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. This output parameter is available on monitor software version ID 11 or later.

Group VII Data Fields and JDBC ResultSet Columns

The Record returns the following Group VII values.

Field/Column Name	Data Type	Description
ProxyUserId	INTEGER NULLABLE	The user ID charged for SPOOL and TEMP space if being charged to the proxy user.
ZoneId	INTEGER NULLABLE	The unique identifier of the zone.
ReqHotAmpCPU	FLOAT	The CPU time of the highest CPU used AMP during the life of the current request on the session. This value is NULL if there is no request running on the session.
ReqHotAmpCPUId	SMALLINT	Vproc ID of the highest CPU used AMP for the current request. This value is NULL if no request is running on the session.
ReqHotAmpIO	FLOAT	I/O count of the highest I/O used AMP during the life of the current request on the session. This value is NULL if there is no request running on the session.
ReqHotAmpIOId	SMALLINT	Vproc ID of the highest I/O used AMP for the current request. This value is NULL if there is no request running on the session.
ReqInvolvedAMPCnt	SMALLINT	The number of AMPs involved in processing the current request. This value is NULL if there is no request running on the session.
ReqFirstRespTime	FLOAT	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. This value is NULL if there is no request running on the session or the PE state is not RESPONSE.
ReqFirstRespDate	DATE	Date 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. This value is NULL if there is no request running on the session or the PE state is not RESPONSE.
ReqLocalQueryStatus	SMALLINT	The current state of the Unified Data Architecture (UDA) query. This value is NULL when no UDA query is running.
ReqRemoteHostId	SMALLINT	Host ID of the remote system. This value is NULL when there is no UDA query running.
ReqRemoteSessionId	INTEGER	Session ID of the executing remote query. This value is NULL when there is no UDA query running.
ReqRemoteRequestId	INTEGER	Request ID of the executing remote query. This value is NULL when there is no UDA query running.
ReqRemoteQueryId	FLOAT	Query ID of the executing remote query. This value is NULL when there is no UDA query running.

Group VIII Data Fields and JDBC ResultSet Columns

The Record returns the following Group VIII values.

Field/Column Name	Data Type	Description
ReqHotAmpSpool	FLOAT	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	SMALLINT	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	SMALLINT	Map number for the largest map the request is using.
ReqMaxNumMapAMPs	INTEGER	Number of AMPs in the largest contiguous map used by the request.
ReqMinNumMapAMPs	INTEGER	Number of AMPs in the smallest contiguous map used by the request.
ReqSysDefNumMapAMPs	INTEGER	Number of AMPs in the system-default map used by the request.
ReqRemoteHostIp	VARCHAR(128)	Host IP address of the remote system.
ReqServerName	VARCHAR(128)	Name of the foreign server.
ReqFlexReleased	SMALLINT	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	FLOAT	Time that the current request was admitted into the system by workload management (after being checked for applicable arrival rate meters).
ReqAdmissionDate	DATE	Date that the current request was admitted into the system by workload management (after being checked for applicable arrival rate meters).
ReqQueryIDHigh	INTEGER	High 32 bits of the Query ID. QueryID can be constructed as: ((unsigned long) ReqQueryIDHigh << 32 \| (unsigned int) ReqQueryIDLow)
ReqQueryIDLow	INTEGER	Lower 32 bits of the Query ID. QueryID can be constructed as: ((unsigned long) ReqQueryIDHigh << 32 \| (unsigned int) ReqQueryIDLow)

Group IX Data Fields and JDBC ResultSetColumns

Field/Column Name	Data Type	Description
ComputeWorkerInUse	VARCHAR(1)	Returns 'T' if the current request is using the Compute Worker AMPs. Returned only when the monitor version is 15 and above. Older applications with previous monitor version numbers do not see this field.

Group X Data Fields and JDBC ResultSetColumns

Field/Column Name	Data Type	Description
ReqQueryIDHigh	INTEGER	High 32 bits of the Query ID. QueryID can be constructed as: ((unsigned long) ReqQueryIDHigh << 32 \| (unsigned int) ReqQueryIDLow)
ReqQueryIDLow	INTEGER	Lower 32 bits of the Query ID. QueryID can be constructed as: ((unsigned long) ReqQueryIDHigh << 32 \| (unsigned int) ReqQueryIDLow)

Statement 3

This statement returns additional blocker sessions. If more than three sessions are blocking the session, the first three blocker sessions are listed in statement 2 and the remaining blocker sessions are listed in statement 3. Each record parcel holds one blocker information set. Record parcels are returned in statement 3 for a specified session that has more than three blocking sessions.

For more information about the first three blocker sessions, see the "Statement 2" section in this topic.

Field/Column Name	Data Type	Description
HostId	SMALLINT	Logical host ID of the blocked session.
SessionNo	INTEGER, NOT NULL	Session number of the blocked session that has more than three blocker sessions. The first three blocker sessions are listed in statement 2 and the remaining blocker sessions are listed in statement 3.
Blk_HostId	SMALLINT	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_HostId, in combination with Blk_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.
Blk_SessNo	INTEGER	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_SessNo, in combination with Blk_HostId, uniquely identifies the session causing a block. This value is NULL if: The SessNo is not available. 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.
Blk_UserID	INTEGER	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: 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.
Blk_LMode	VARCHAR (1)	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 Blk_Status field.
Blk_OType	VARCHAR(2)	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 can be a database, table, or row hash. 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. 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 Blk_LMode. When that occurs, the only lock conflict reported is that User B is blocked by User A on a table.
Blk_ObjDBId	INTEGER	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.
Blk_ObjTId	INTEGER	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_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.
Blk_Status	VARCHAR (1)	Status of lock causing a block: W= Waiting G = Granted 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 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.

Sample Input - CLIv2 Request

This example shows how the parcels for a MONITOR SESSION request, built by CLIv2, appear when sent to the database server using a host_id of 387, a session_no of 1098, and a user_name of WEEKLY. In this example, the size of the response buffer is set at the maximum (64,000 bytes). The minimum response size is 32,000 bytes.

Number		Length	Body
Num	Name	Bytes	Field	Value
0001	Req	19	Request	MONITOR SESSION
0003	Data	42	MonVerID	10
			HostId	387
			SessionNo	1098
			UserName	WEEKLY
0004	Resp	6	BufferSize	64000

Sample Input - Teradata JDBC Driver Request

For an example of how the PM/API request, built in Java, appears when sent to the database server, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html.

Sample Output - MONITOR SESSION Statements 1 through 3

Using monitor software version ID 13, this request returns the following values for statements 1 through 3.

HostId:        1   SessionNo: 1006
LogonPENo: 30718   RunVprocNo: 30718
PartName: DBC/SQL                         PEState:  DISPATCHING

LogonDate/Time:  04/29/2016 11:26:46.00
UserID: 1028 LSN: 0
UserName: [JCK]
UserAccount: [DBC]

PECPUSec:       0.02   XactCount:          1.00
ReqCount:        2.0   ReqCacheHits:        0.0

AMPState: ACTIVE
AMPCPUSec:     13.60   AMPIO: 4606.00
Request_AMPSpool: 491517952.0

Blk_1_HostId:      0   Blk_2_HostId:      0   Blk_3_HostId:      0
Blk_1_SessNo:      0   Blk_2_SessNo:      0   Blk_3_SessNo:      0
Blk_1_UserID:      0   Blk_2_UserID:      0   Blk_3_UserID:      0
Blk_1_LMode:        Blk_2_LMode:        Blk_3_LMode:
Blk_1_OType:        Blk_2_OType:        Blk_3_OType:
Blk_1_ObjDBId:     0   Blk_2_ObjDBId:     0   Blk_3_ObjDBId:     0
Blk_1_ObjTId:      0   Blk_2_ObjTId:      0   Blk_3_ObjTId:      0
Blk_1_Status:       Blk_2_Status:       Blk_3_Status:
MoreBlockers:

LogonSource: (TCP/IP) f1a3 153.64.183.39 MYSYSTEM      9964  JK121219  BTEQ  01 LSS

HotAmp1CPU:      3.43   HotAmp2CPU:      3.40   HotAmp3CPU:      3.40
HotAmp1CPUId:       3   HotAmp2CPUId:       2   HotAmp3CPUId:       0

HotAmp1IO:    1158.00   HotAmp2IO:    1152.00   HotAmp3IO:    1152.00
HotAmp1IOId:        3   HotAmp2IOId:        2   HotAmp3IOId:        1

LowAmp1CPU:      3.38   LowAmp2CPU:      3.40   LowAmp3CPU:      3.40
LowAmp1CPUId:       1   LowAmp2CPUId:       2   LowAmp3CPUId:       0

LowAmp1IO:    1144.00   LowAmp2IO:    1152.00   LowAmp3IO:    1152.00
LowAmp1IOId:        0   LowAmp2IOId:        1   LowAmp3IOId:        2

AvgAmpCPUSec:    3.40   AvgAmpIOCnt:  1151.50
AmpCount:           4

TempSpaceUsg:    0.00

ReqStartTime:  04/29/2016 11:26:46.00
ReqCPU:      13.60  ReqIO:    4606.00
ReqNo:3   WlcId: 13     DontReclassifyFlag: 0
ProxyUser: []

ProxyUserID: 0
CPUDecayLevel=0,  IODecayLevel=0,  TacticalCPUException=0, TacticalIOException=0
ReqIOKB=541295      , ReqPhysIO=661         , ReqPhysIOKB=268502

ReqStepsCompletedCnt=9
CurrentRedriveParticipation=[F]
ReqRedriveSpoolSpace=0.00

BlockerSessionCnt = 0
ReqTblOpBytesIn = 0           , ReqTblOpBytesOut = 0

ZoneId = 0
ReqHotAmpCPU =       3.43 ReqHotAmpCPUId = 3
ReqHotAmpIO =    1158.00 ReqHotAmpIOId = 3
ReqInvolvedAMPCnt = 4
ReqFirstResp Date/Time= 00/00/0000 00:00:00.00

ReqLocalQueryStatus = 0
ReqRemoteHostId = 0, ReqRemoteSessionId = 0
ReqRemoteRequestId = 0, ReqRemoteQueryId = 0
ReqRemoteHostIp:

ReqServerName:

ReqHotAmpSpool = 123837952.00 ReqHotAmpSpoolId = 3
ReqMapNo = 0,   ReqMaxNumMapAMPs = 0
ReqMinNumMapAMPs = 0,   ReqSysDefNumMapAMPs = 0
ReqFlexReleased = N (0)

Sample Output: MONITOR SESSION Statements 1 and 2: Indicator Mode

Stmt #1 ** Data Info ** Field Count: 9 
Field 1) [CollectionInterval]:  	SMALLINT 	NOT NULL        	Type = 500 (500) Size = 2 (2) 
Field 2) [CollectionSeqNum]:  	INTEGER 	NOT NULL         	Type = 496 (496) Size = 4 (4) 
Field 3) [Collectiondate]:  	DATE		NOT NULL            	Type = 752 (752) Size = 4 (4) 
Field 4) [CollectionTime]:  	FLOAT		NOT NULL           	Type = 480 (480) Size = 8 (8) 
Field 5) [SessionRate]:  	SMALLINT 	NOT NULL        	Type = 500 (500) Size = 2 (2) 
Field 6) [ExceptionInterval]:  	SMALLINT 	NOT NULL        	Type = 500 (500) Size = 2 (2) 
Field 7) [SessionRateThreshold]:  	SMALLINT 	NOT NULL        	Type = 500 (500) Size = 2 (2) 
Field 8) [DBQLFlushRate]:  	SMALLINT 	NOT NULL        	Type = 500 (500) Size = 2 (2) 
Field 9) [RedriveProtection]:  	VARCHAR 	NOT NULL         	Type = 448 (448) Size = 10 (10) 


***** Stmt #1    Record Count: 1 *****
RECORD #1:
Field 1) [CollectionInterval] 	= 125
Field 2) [CollectionSeqNum] 	= 5965
Field 3) [Collectiondate] 	= 1240207
Field 4) [CollectionTime] 	=   82907.00
Field 5) [SessionRate]		= 60
Field 6) [ExceptionInterval] 	= 0
Field 7) [SessionRateThreshold] 	= 60
Field 8) [DBQLFlushRate] 	= 600
Field 9) [RedriveProtection] 	= [MN]



Stmt #2 ** Data Info ** Field Count: 122 
Field 1) [HostId]: 		SMALLINT 	NULLABLE        	Type = 501 (501) Size = 2 (2) 
Field 2) [LogonProcId]:  	SMALLINT 	NOT NULL        	Type = 500 (500) Size = 2 (2) 
Field 3) [RunProcId]: 		SMALLINT 	NULLABLE        	Type = 501 (501) Size = 2 (2) 
Field 4) [SessionNo]: 		INTEGER 	NOT NULL         	Type = 496 (496) Size = 4 (4) 
Field 5) [UserName]: 		VARCHAR 	NOT NULL         	Type = 448 (448) Size = 514 (514) 
Field 6) [UserAccount]:  	VARCHAR 	NOT NULL         	Type = 448 (448) Size = 514 (514) 
Field 7) [UserId]: 		INTEGER 	NOT NULL         	Type = 496 (496) Size = 4 (4) 
Field 8) [LSN]: 		INTEGER 	NOT NULL         	Type = 496 (496) Size = 4 (4) 
Field 9) [LogonTime]: 		FLOAT		NOT NULL           	Type = 480 (480) Size = 8 (8) 
Field 10) [LogonDate]: 		DATE		NOT NULL            	Type = 752 (752) Size = 4 (4) 
Field 11) [PartName]: 		VARCHAR 	NOT NULL         	Type = 448 (448) Size = 66 (66) 
Field 12) [Priority]: 		VARCHAR 	NOT NULL         	Type = 448 (448) Size = 10 (10) 
.
.
.
Field 114) [ReqMaxNumMapAMPs]:  	INTEGER 	NULLABLE         	Type = 497 (497) Size = 4 (4) 
Field 115) [ReqMinNumMapAMPs]:  	INTEGER 	NULLABLE         	Type = 497 (497) Size = 4 (4) 
Field 116) [ReqSysDefNumMapAMPs]:  	INTEGER 	NULLABLE         	Type = 497 (497) Size = 4 (4) 
Field 117) [ReqRemoteHostIp]:  	VARCHAR 	NULLABLE         	Type = 449 (449) Size = 514 (514) 
Field 118) [ReqServerName]:  	VARCHAR 	NULLABLE         	Type = 449 (449) Size = 514 (514) 
Field 119) [ReqFlexReleased]:  	SMALLINT 	NULLABLE        	Type = 501 (501) Size = 2 (2) 
Field 120) [ReqAdmissionTime]:  	FLOAT		NULLABLE           	Type = 481 (481) Size = 8 (8) 
Field 121) [ReqAdmissionDate]:  	DATE		NULLABLE            	Type = 753 (753) Size = 4 (4) 
Field 122) [ComputeWorkerInUse]:  	VARCHAR 	NULLABLE         	Type = 449 (449) Size = 6 (6) 
Field 123) [ReqQueryIDHigh]:  	INTEGER 	NULLABLE         	Type = 497 (497) Size = 4 (4) 
Field 124) [ReqQueryIDLow]:  	INTEGER 	NULLABLE         	Type = 497 (497) Size = 4 (4) 




***** Stmt #2    Record Count: -1 *****
RECORD #1:
Field 1) [HostId]		= 1
Field 2) [LogonProcId]		= 30719
Field 3) [RunProcId]		= 30719
Field 4) [SessionNo]		= 1021
Field 5) [UserName]		= [DBC]
Field 6) [UserAccount]		= [DBC]
Field 7) [UserId]		= 1
Field 8) [LSN]		= 0
Field 9) [LogonTime]		=   84738.00
Field 10) [LogonDate]		= 1240207
Field 11) [PartName]		= [MONITOR]
Field 12) [Priority]		= [(null)]
.
.
.
Field 114) [ReqMaxNumMapAMPs] 	= (null)
Field 115) [ReqMinNumMapAMPs] 	= (null)
Field 116) [ReqSysDefNumMapAMPs] 	= (null)
Field 117) [ReqRemoteHostIp] 	= [(null)]
Field 118) [ReqServerName] 	= [(null)]
Field 119) [ReqFlexReleased] 	= (null)
Field 120) [ReqAdmissionTime] 	= (null)
Field 121) [ReqAdmissionDate] 	= (null)
Field 122) [ComputeWorkerInUse] 	= [F]
Field 123) [ReqQueryIDHigh] 	= (null)
Field 124) [ReqQueryIDLow] 	= (null)

Relationship between MONITOR SESSION and ABORT SESSION

When sessions are being ended or sessions are being blocked by the sessions being ended, data returned from subsequent MONITOR SESSION queries may be affected. After the ending operation starts, you can immediately notice the changes from ending sessions. However, you do not notice the changes resulting from sessions that were blocked by ending sessions in MONITOR SESSION responses until the ending operation is complete.

For information on this PMPC PM/API request relationship, see "Relationship between ABORT SESSION and MONITOR SESSION" in ABORT SESSION.

Relationship between MONITOR SESSION and IDENTIFY

PM/API can report on locks placed by any user or object with the MONITOR SESSION and IDENTIFY requests. The MONITOR SESSION request helps you identify the types of locks blocking a session.

For information on this PMPC PM/API request relationship, see "Relationship Between IDENTIFY and MONITOR SESSION" in IDENTIFY.

Relationship between MONITOR SESSION and SET SESSION RATE

You must run the SET SESSION RATE request to activate session-level data collection before you run a MONITOR SESSION request. Therefore, either the global rate or local rate must be set to nonzero. If both rates are set to zero, an error message is returned.

A change in the global session rate may affect the data reported by a MONITOR SESSION request. Specifically, if User A makes a change in the global rate, this change can affect the viewing for User B of displayed data from a MONITOR SESSION request. Unless User B is aware of the rate change, User B may draw incorrect conclusions from the observed data. User B gets a warning message when executing a MONITOR SESSION request.

Example: Using SET SESSION RATE and MONITOR SESSION

The following example is a single user on a system that uses the SET SESSION RATE and MONITOR SESSION requests.

User Morris uses the SET SESSION RATE request to set a session-level global rate of 600 seconds (or 10 minutes) at 9:00 a.m. The data time-stamp was set at 9:00 a.m. because that was the last time data was requested.
Morris then uses the SET SESSION RATE request to set a session-level local rate of 300 seconds (5 minutes) at 9:05 a.m. Morris does not make a request for data at this time. Therefore, the time-stamp is still set at 9:00 a.m.
Morris runs a MONITOR SESSION request at 9:08 a.m. for host_id of 510 and a session_no of 1000:
MONITOR SESSION 2 510 1000
A collection occurs because the “current” data was not considered current (that is, the age of the data is greater than the session collection rate). The system calculates that 9:08 a.m. (current time) - 9:00 a.m. (time-stamp) = 8 minutes, which is the age of the data. The 8 minutes is greater than 5 minutes, which is the local collection rate. Because of the forced update, Morris gets 8 minutes of data (from 9:00 a.m. to 9:08 a.m.), and the time-stamp is reset to 9:08 a.m.
At 9:10 a.m., Morris decides to make another MONITOR SESSION request. This time, no collection occurs, because the “current” available data was considered current. The system calculates that 9:10 a.m. (current time) - 9:08 a.m. (time-stamp) = 2 minutes (age of the data), which is less than 5 minutes (local collection rate). Therefore, the current data available is considered current, because no data update to the session-level memory repository occurs. Morris gets the same data that was returned at 9:08 a.m.

Two other events can cause data to be collected:

Event	Comments
A default system timer of 10 minutes has elapsed without a request from a user.	Whenever 10 minutes has expired without a collection, the system timer causes a default collection to occur. This causes data in the main memory repository to be updated, even if no request is made.
When the AMP Session Cache is full	A full AMP Session Cache forces an update of data in the main memory repository. This is a rare occurrence.

MONITOR SESSION Request | VantageCloud Lake - MONITOR SESSION - Teradata VantageCloud Lake

Lake - Monitor Resources and Performance

Input Data

Monitor Privileges

Usage Notes - MONITOR SESSION

Collecting Session Data

Unreported Session Partitions

Down Processor

Internal Sessions

CLIv2 Response Parcels

Response

Statement 1

Statement 2

Response Groups

Group I Data Fields and JDBC ResultSet Columns

Group II Data Fields and JDBC ResultSet Columns

Group III Data Fields and JDBC ResultSet Columns

Group IV Data Fields and JDBC ResultSet Columns

Group V Data Field and JDBC ResultSet Columns

Group VI Data Fields and JDBC ResultSet Columns

Group VII Data Fields and JDBC ResultSet Columns

Group VIII Data Fields and JDBC ResultSet Columns

Group IX Data Fields and JDBC ResultSetColumns

Group X Data Fields and JDBC ResultSetColumns

Statement 3

Sample Input - CLIv2 Request

Sample Input - Teradata JDBC Driver Request

Sample Output - MONITOR SESSION Statements 1 through 3

Sample Output: MONITOR SESSION Statements 1 and 2: Indicator Mode

Relationship between MONITOR SESSION and ABORT SESSION

Relationship between MONITOR SESSION and IDENTIFY

Relationship between MONITOR SESSION and SET SESSION RATE

Example: Using SET SESSION RATE and MONITOR SESSION