MONITOR SESSION Request | Application Programming Reference | Teradata Vantage - MONITOR SESSION - Advanced SQL Engine - Teradata Database

Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
cpn1571792172880.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

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 currently 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 will produce an error response. Remember that 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:

Usage Notes - MONITOR SESSION

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

Use this information, when a job hangs because of unavailable resources, to find out about 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 execute 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 particular 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 crash 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 utilized 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 particular 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 would be available until the next MONITOR SESSION request is issued at 9:10 a.m. (upon 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.
  • When a system crash occurs, the system crash clears out the previous data and data collection starts over 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 Teradata Vantage™ - Database Messages, B035-1096.

The MONITOR SESSION request always 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.

If ActivityCount is... The ...
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 it encounters the EndStatement parcel.

A value of -1 is used because ActivityCount cannot be determined when the request is executed. 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 particular 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). Once 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, a DBS Control General Record 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 more information about the MonSesCPUNormalization field and CPU normalization, see the DBS Control utility in Teradata Vantage™ - Database Utilities , B035-1102 .

For a complete description of these fields, see Response Groups.

You can also refer to 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 more current data is necessary. If more 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.

All 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, it is the global rate.

When a user executes 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.

If data in the PE memory repository is... The ...
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 some 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.

A 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 because:
  • They may be blocking an important session.
  • They are hard to recognize.
  • They 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 Teradata Vantage™ - Database Administration, B035-1093.

You must determine what to do about a session that is blocking some 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

Some internal sessions are not easy to recognize simply from the data returned in these Blk_x fields. With an internal session, any or all of the Blk_x fields may be NULL. In this case, you might mistake an internal session with a NULL Blk_x_SessNo for a client utility session. All three fields could be filled in with legitimate looking values. In this case, you can frequently recognize an internal session by a value of zero in the Blk_x_HostId field. Still, 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 some guidelines for recognizing internal sessions the HostId, SessNo, and UserID Blk_x fields internal sessions.

For fields: If Values Are: Session Type is:
HostId, SessNo, UserID NULL Internalsession
  • HostId & SessNo
  • UserID
  • NULL
  • Non-NULL
Idle Client utility
HostId, SessNo, UserID Non-NULL Special rule: 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.
If ... You ...
an internal session is blocking an important session cannot abort the internal session.
the internal session lock request is waiting can abort the work of the sessions that are blocking the internal sessions.
the internal session lock request is granted must wait for it 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 Flavor 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). It 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). It contains blocker data describing the session.
EndStatement 11 6 StatementNo = 2-byte integer, value = 3
EndRequest 12 4 None

Response

Each of the statement types described below correspond 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 results in 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. For information on TASM Workloads, see Teradata® Viewpoint User Guide, B035-2206.
SessionRateThreshold SMALLINT

NOT NULL

DBS Control PMPC_SessionRateThreshold value. See the DBS Control PMPC_SessionRateThreshold field in Teradata Vantage™ - Database Utilities , B035-1102 for details.
DBQLFlushRate SMALLINT

NOT NULL

DBS Control DBQLFlushRate value. See the DBS Control DBQLFlushRate field in Teradata Vantage™ - Database Utilities , B035-1102 for details.
RedriveProtection VARCHAR (2)

NOT NULL

Redrive protection type:

  • ' ' = No Redrive protection. This indicates that the session will not participate in Redrive and database restarts will not be transparent to applications and users.
  • MN = Memory-based Redrive protection, no fallback spools

Statement 2

The response to the second statement results in 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

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

mon_ver_ID Entry 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 through IV
6 Groups I-IV Data Fields and JDBC ResultSet Columns Returns data fields concerned with Group I through 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 through Group V.
9 - 11 Groups I - VI Data Fields and JDBC ResultSet Columns Returns data fields concerned with Group I through VI.
12 Groups I - VII Data Fields and JDBC ResultSet Columns Returns data fields concerned with Group I through VII.
13 Groups I - VIII Data Fields and JDBC ResultSet Columns Returns data fields concerned with Group I through VIII.
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.

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 currently logged on to; it identifies the PE that has control responsibility for the session. Normally, this is the PE that processed the logon request; but if that PE goes offline, it will be the PE to which the session was switched.
RunVprocNo SMALLINT Vproc number of the AMP or PE currently 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. For HUTPARSE or HUTCTL (archive/recovery) sessions, this value is NULL.

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. Together 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) that is associated with a session when the session logs on. It 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, as well as 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, MultiLoad, and Archive/Recovery jobs.
LogonTime FLOAT

NOT NULL

Time portion of the information recorded by Session Control when a session successfully logs on. Together with LogonDate, it indicates when the session logged on to the system. It is usually 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 successfully logs on. Together with LogonTime, it 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 currently 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:
  • 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. See Teradata® Viewpoint User Guide, B035-2206 for information on this rule.
  • HOST-RESTART: A restart is in progress for the associated host (or client).
  • ABORTING: The transaction is being rolled back; the session is aborting.
  • 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 it to the AMP.
  • DISPATCHING: The Dispatcher or Monitor is having a request executed.
  • BLOCKED: Some 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 currently 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. It is accurate to the second.

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

This value is NULL when it is returned for all other sessions.

XActCount FLOAT Number of explicit and implicit transactions executed 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 that this session processed a request using information from the Teradata SQL Parser request cache, specifically, how many 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:
  • ABORTING: The transaction is being rolled back; session is aborting.
  • BLOCKED:Some 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 as a number of 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.

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.

A valid value is not returned if an inactive Archive/Recovery job is causing the lock conflict, because no session is logged in.

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

Use the Show Locks utility to obtain the user name that placed the HUT lock. For more information, see .

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 information is unavailable if an inactive Archive/Recovery job is causing the lock conflict.

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.

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.

Use the Show Locks utility to obtain the user name that placed the HUT lock. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 .

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. This information is especially important when an Archive/Recovery job is holding the lock, because 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.

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.

Use the Show Locks utility to obtain the user name that placed the HUT lock. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 .

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 SESSSION 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 could 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, it is possible for User A to 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.

For a list of the commonly seen LogonSource string application names, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilization for the last session collection interval. The average is calculated as the sum of CPU utilization 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 utilization for the last session collection interval. The average is calculated as the sum of I/O utilization 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 some rare cases, in the very 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 will be available on the next collection.

WDId INTEGER Workload ID associated with the specified request.
Classification Mode SMALLINT Indicator if a running query or session is (or any future queries will be) forced into a WD or not.

The classification mode is valid only for DBC/SQL sessions and if the Teradata dynamic workload management software is enabled. If the Teradata dynamic workload management software is enabled, it 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 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.
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 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.
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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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 utilized 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. Note, 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.

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 Statement 2.

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.

A valid value is not returned if an inactive Archive/Recovery job is causing the lock conflict, because no session is logged in.

This value is NULL if:
  • The host ID is not available.
  • The session does not have an AMPState of BLOCKED.

If the Blk_HostId, Blk_SessNo, and Blk_UserID values all return as NULLs and AMPState is BLOCKED, a Host Utility (HUT) 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.

Use the Show Locks utility to obtain the user name that placed the HUT lock. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 .

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 information is unavailable if an inactive Archive/Recovery job is causing the lock conflict.

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.

If the Blk_HostId, Blk_SessNo, and Blk_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.

Use the Show Locks utility to obtain the user name that placed the HUT lock. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 .

Blk_UserID INTEGER ID of the user or host utility job preventing the session from being granted a lock. This information is especially important when an Archive/Recovery job is holding the lock, because 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.

If the Blk_HostId, Blk_SessNo, and Blk_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.

Use the Show Locks utility to obtain the user name that placed the HUT lock. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 .

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 could 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, it is possible for User A to 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), although you can set it to any size. However, a minimum response size is 32,000 bytes.

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

Relationship Between MONITOR SESSION and ABORT SESSION

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

For information on this PMPC PM/API request relationship, see Relationship Between ABORT SESSION and MONITOR 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.

Relationship Between MONITOR SESSION and SET SESSION RATE

You must execute the SET SESSION RATE request to activate session-level data collection before you execute a MONITOR SESSION request. This means that 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 could 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. In this case, User B receives 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.

  1. 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.
  2. 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.
  3. Morris executes a MONITOR SESSION request at 9:08 a.m. for host_idof 510 and a s ession_no of 1000:

    MONITOR SESSION 2 510 1000

  4. 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 receives 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.
  5. 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). This means that 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.