15.10 - MONITOR SESSION - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

Returns session or request resource usage statistics.

 

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.

Note: 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.

Note: 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” on page 412.

For a general explanation of monitor version choices, see “MONITOR VERSION” on page 162.

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.

Note:  

  • If you do not specify host_id, user_name, or session_no, all hosts, all users, or all sessions are monitored. For example, if you specify 127 for host_id, PEDERSON for user_name, and do not specify session_no, 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” on page 28.
  • For information on the data returned for each monitor version, see “Response Groups” on page 119.

    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:

  • Database Administration
  • Security Administration
  • Teradata JDBC Driver User Guide
  • Before using this request, see “Impact of Object Name Length on PM/API Requests” on page 54.

    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 Teradata 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” on page 68.

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

    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:

     

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

    For a complete description of these fields, see “Response Groups” on page 119.

    You can also refer to “MonitorMySessions” on page 238 or “MonitorSession” on page 269 for a list of these CPU fields.

    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 table below.

     

    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.

    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.

    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:

     

    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.
  • Note: For information on handling blocked DBQL/Teradata dynamic workload management software internal express requests, see Database Administration.

    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

    Internal session

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

    The MONITOR SESSION request is treated internally as a multiple statement request with each statement generating a response. The multiple statement response returned from Teradata 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

    Note: 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.

    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.

    SessionRateThreshold

    SMALLINT,
    NOT NULL

    DBS Control PMPC_SessionRateThreshold value. See the DBS Control PMPC_SessionRateThreshold field in Utilities, for details.

    DBQLFlushRate

    SMALLINT,
    NOT NULL

    DBS Control DBQLFlushRate value. See the DBS Control DBQLFlushRate field in Utilities 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
  • 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.

    Note: 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.

    Note: 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” on page 54.

    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.

    Note: 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 Teradata 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 Teradata 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.

    Note: 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)

    Note: 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 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 Teradata Database actively working or rolling back an aborted transaction. This does not include any PDE CPU time spent handling Teradata 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 Teradata 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 Utilities.

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

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

    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 Teradata 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.
  • Note: 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.

    Note: 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 SQL Data Definition Language.

    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.

    The Record returns the following Group II values.

    Note: 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.

    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.

    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.

    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.

    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.

    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.

    Note: 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.

    Note: 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.

    CurrentRedrive
    Participation

    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.

    Note: This field returns only the first three blocking sessions in statement 2.

    Note: This output parameter is available on monitor software version ID 11 or later.

    ReqTblOpBytesIn

    FLOAT

    The total number of bytes transferred into Teradata Database from a foreign server for the current request through one or more table operators.

    Note: The request may involve one or multiple table operator executions. The ReqTblOpBytesIn output parameter shows bytes transferred across all invocations within the request.

    Note: This output parameter is available on monitor software version ID 11 or later.

    ReqTblOpBytesOut

    FLOAT

    The total number of bytes transferred out of Teradata Database and into a foreign server for the current request through one or more table operators.

    Note: The request may involve one or multiple table operator executions. The ReqTblOpBytesOut output parameter shows bytes transferred across all invocations within the request.

    Note: This output parameter is available on monitor software version ID 11 or later.

    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.

    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” on page 119.

     

    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 Teradata 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 Utilities.

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

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

    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 Teradata 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.
  • Note: 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.

    This example shows how the parcels for a MONITOR SESSION request, built by CLIv2, appear when sent to the Teradata 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

    For an example of how the PM/API request, built in Java, appears when sent to the Teradata Database server, see Teradata JDBC Driver Reference.

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

    Submitting request MONITOR SESSION; ...
     
    CollectionInterval:      30 seconds
    Collection Seq Number: 724
    Collection Date/Time:   02/10/2015 17:40:02.00
    SessionRate:           1 seconds
    ExceptionInterval:     0 seconds
    SessionRateThreshold:  60 seconds
    DBQLFlushRate:         600 seconds
    RedriveProtection:     [MN]
     
    HostId:        1   SessionNo: 1022
    LogonPENo: 30719   RunVprocNo: 30719
    PartName: DBC/SQL                         PEState:  DISPATCHING
     
    LogonDate/Time:  02/10/2015 08:36:02.00
    UserID: 1026 LSN: 0
    UserName: [JCK]
    UserAccount: [DBC]
     
    PECPUSec:       3.49   XactCount:       1465.00
    ReqCount:     1466.0   ReqCacheHits:     1463.0
     
    AMPState: ACTIVE
    AMPCPUSec:  31822.81   AMPIO: 12302198.00
    Request_AMPSpool: 1522495488.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) ee6c 153.64.183.39 MYSYSTEM      4088  JK121219  BTEQ  01 LSS
     
    HotAmp1CPU:      7.17   HotAmp2CPU:      7.17   HotAmp3CPU:      7.08
    HotAmp1CPUId:       1   HotAmp2CPUId:       0   HotAmp3CPUId:       2
     
    HotAmp1IO:    2637.00   HotAmp2IO:    2612.00   HotAmp3IO:    2600.00
    HotAmp1IOId:        0   HotAmp2IOId:        1   HotAmp3IOId:        2
     
    LowAmp1CPU:      7.06   LowAmp2CPU:      7.08   LowAmp3CPU:      7.17
    LowAmp1CPUId:       3   LowAmp2CPUId:       2   LowAmp3CPUId:       1
     
    LowAmp1IO:    2583.00   LowAmp2IO:    2600.00   LowAmp3IO:    2612.00
    LowAmp1IOId:        3   LowAmp2IOId:        2   LowAmp3IOId:        1
     
    AvgAmpCPUSec:    7.12   AvgAmpIOCnt:  2608.00
    AmpCount:           4
     
    TempSpaceUsg:    0.00
     
    ReqStartTime:  02/10/2015 17:39:26.00
    ReqCPU:      33.90  ReqIO:   12722.00
    ReqNo:1467   WlcId: 0     DontReclassifyFlag: 255
    ProxyUser: []
     
    ProxyUserID: 0
    CPUDecayLevel=0,  IODecayLevel=0,  TacticalCPUException=0, TacticalIOException=0
    ReqIOKB=1555892     , ReqPhysIO=2787        , ReqPhysIOKB=1283426
     
    ReqStepsCompletedCnt=9
    CurrentRedriveParticipation=[F]
    ReqRedriveSpoolSpace=0.00
     
    BlockerSessionCnt = 0
    ReqTblOpBytesIn = 0           , ReqTblOpBytesOut = 0
     
    ZoneId = 0
    ReqHotAmpCPU =       8.51 ReqHotAmpCPUId = 1
    ReqHotAmpIO =    3198.00 ReqHotAmpIOId = 0
    ReqInvolvedAMPCnt = 4
    ReqFirstResp Date/Time= 00/00/0000 00:00:00.00
     
    ReqLocalQueryStatus = 0
    ReqRemoteHostId = 0, ReqRemoteSessionId = 0
    ReqRemoteRequestId = 0, ReqRemoteQueryId = 0

    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” on page 65.

    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” on page 73.

    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

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