ABORT SESSION Request | Application Programming Reference | VantageCloud Lake - ABORT SESSION - Teradata VantageCloud Lake

Lake - Monitor Resources and Performance
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
wyu1683671641248.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
wyu1683671641248

Aborts any outstanding request or transaction of one or more sessions, and optionally logs those sessions off the database system.

Input Data

If host_id, user_name, or session_no is omitted or set to NULL, ABORT SESSION tries to abort all hosts, all users, or all sessions. For example, if you specify 127 for host_id, FRR for user_name, and NULL for session_no, ABORT SESSION attempts to abort every and all sessions logged on from host 127 as user FRR.

Element Data Type/ Range 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.

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. For example, a hostid of zero identifies internal sessions or system Console sessions.

host_id cannot exceed 1023.
session_no INTEGER Session number. session_no combined with the host_id represents a unique session ID.
user_name VARCHAR(128) Name of user who is running the sessions.
list VARCHAR(1) Indicator of whether to display a list of sessions.

To display a list of all session_no sessions, specify y or Y. If you do not want to display a list of sessions, specify n, N, NULL, or blank.

Do not use list when large numbers of sessions are being stopped. Otherwise, system degradation can result, especially when the list contains more than 1500 sessions. The slowdown occurs because all impacted sessions must be buffered and then sorted on a single processor. During the sort, the processor is unavailable for requests from any other MONITOR session logged on to that PE. The number of sessions to be sorted can exhaust scratch space on the processor and cause a system restart.

If your site is running Two-Phase Commit (2PC), the following information applies:
  • Do not use ABORT SESSION to abort or log off Teradata Director Program internal sessions used for 2PC processing. To log off the sessions, use the Teradata Director Program command DISABLE IRF instead.
  • Be sure to issue DISABLE IRF before running ABORT SESSION using the host_id.* or *.* parameters to avoid any possible problem with the Teradata Director Program.
  • To identify Teradata Director Program internal sessions, run the Teradata Director Program command DISPLAY SESSIONS. Teradata Director Program internal sessions have the job name *TDPINT*. (The MONITOR SESSION request cannot identify Teradata Director Program internal sessions.)
logoff VARCHAR(1) Indicator of whether to log session_no sessions off the database after aborting them.

To log session_no sessions off the database, specify y or Y. To end session_no sessions, specify n, N, NULL, or blank.
override VARCHAR(1) Possible values:
  • Y or y, if you do not want the ABORT SESSION request to fail in any of the following cases:
    • An identified session is being session-switched.
    • An identified session is running its own ABORT SESSION request.
    • An identified session has a PEState of IDLE: IN-DOUBT as a result of a 2PC.
      Sessions are marked IN-DOUBT by the 2PC protocol, which governs how transactions are committed by multiple systems that do not share memory. The protocol guarantees that either all systems commit or all roll back.
Therefore, when you specify y or Y, session_no sessions that fit one of the preceding criteria are ignored, and all sessions that do not fit any of the preceding criteria are aborted.
  • N, n, NULL, or blank

    If at least one identified session fits one of the preceding criteria, the ABORT SESSION request fails.

    If you specify n or N, or do not specify this option, the entire ABORT SESSION operation fails if any non-abortable session is encountered. If sessions are not aborted (including those in abortable states), this field returns an error message.

Performance and list Option

The ABORT SESSION request is different from other PM/API requests, because when processing begins, the ABORT SESSION operation cannot be stopped.

The ABORT SESSION cannot be stopped even if list displays sessions that were included by mistake.

Monitor Privileges

To use this request, you must have the ABORTSESSION 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

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

Aborting a session is useful when a session causes a production job to block other sessions waiting for locks, or when a session uses resources that a critical application is running too slowly.

By default, the ABORT SESSION request aborts the current transaction for the specified sessions and logs those sessions off the database.

The ABORT SESSION request is logged to the DBC.SW_Event_Log table (accessible from the DBC.Software_Event_LogV view). If you specify y or Y for logoff, records are added to DBC.SW_Event_Log table.

ABORT SESSION does not abort nor log off a session under any of the following conditions:
  • The session status is IN-DOUBT.
  • The session is being session-switched.
  • The session is a Database Query Log (DBQL) artificial internal session
  • The actual session is running an ABORT SESSION request.

If you try to abort a session that is running an ABORT SESSION request, either a diagnostic message displays, indicating that the ABORT SESSION request was not accepted, or, the ABORT SESSION request aborts any identified sessions that do not meet the preceding conditions.

This request may end and log off other PM/API sessions, but ignores the session from which it was submitted.

At least one of the transactions you want to end either cannot be ended or already is being ended if:
  • A session is in the final stage of an ALTER TABLE operation and cannot be aborted.
  • A user-initiated abort must complete before whatever caused the ABORT SESSION request to run can be done. Therefore, if you specify list, that list indicates sessions that are:
    • IN-DOUBT
    • Being session-switched
    • Already aborting or committing their own transactions
    • Running an ABORT SESSION request

The ABORT SESSION request has the following impact as described in the following table.

Type of Session Impact of ABORT SESSION Option Comments
A session with a transaction that is being committed or rolled back None with or without logoff The ABORT SESSION request does not fail. Instead, the stage 1 response from the database includes a warning that identified sessions are in the process of committing or rolling back their transactions.
An internal session None   The activity of such sessions is vital to the continued database execution and is considered to be more important than any user-initiated work that may be blocked. The database system acts as if the internal sessions do not appear in the optional list of sessions identified by this request.
Specify a host_id of zero to end Console Basic Teradata Query (BTEQ) sessions.
A DBQL/Teradata dynamic workload management software artificial internal session Does not work   This is not a real session and cannot be ended. If you try to end this session, an error message is returned.
A client utility user Little   Client utility locks are designed to survive system outages or interruptions in the archive process, and are not necessarily associated with an active session. Instead, these locks are associated with the user who originally submitted the archive or recovery operation. Therefore, an ABORT SESSION request does not necessarily remove locks placed by the client utility and does not necessarily cause whatever activity any such locks were blocking to become unblocked. However, such sessions still appear in the optional list of sessions identified by this request.
MLOAD, FASTLOAD, or DBCUTIL partition sessions Does not work without logoff Sessions within these partitions are associated with transactions or locks. Also, these partitions may not include the Teradata SQL or PM/API ability to recover from an interrupted request without terminating the application. That is, these partitions are designed to be restarted, but not rolled back. Therefore, when a FastLoad or MultiLoad operation must be terminated, the ABORT SESSION request typically specifies that all sessions associated with the utility job be ended and logged off. These utility-related sessions appear in the optional list of sessions identified by this request.

Ending a FastLoad, MultiLoad, or FastExport job by user_name is easier than with session_no. These utilities have multiple sessions under one user_name and ending one utility session running under that user_name but not another does not make sense. However, if the same user_name is running other sessions at the same time, those sessions are also ended and logged off.

Processing Messages

Unlike other PM/API requests, ABORT SESSION has an unpredictable execution time and can take hours to roll back a transaction.

Therefore, on receipt of an ABORT SESSION request, the database sends one or more of the following processing messages:
  • Indicates that the ABORT SESSION request is received and is in one of the following states:
    • Is accepted and is in execution.
    • Cannot be accepted because resources are exhausted, because of either a heavy workload or multiple ABORT SESSION requests are queued and waiting to finish processing. Information is recorded in the error log.

      The database does not restart as a result of this error. Depending on the available resources, this request completes after the queued end requests have completed processing.

  • Lists the number of sessions affected by the running request (if Y was entered into the list field).
  • Indicates the request is complete when all identified sessions have been ended.
  • Indicates that all session have been logged off (if Y was entered into the logoff field).

CLIv2 Response Parcels

Because of the unpredictable run time of ABORT SESSION, the database system handles ABORT SESSION as a two-statement request, with each statement generating a response.

The two-statement response contains the following sequence of parcel types.

Parcel Sequence Parcel Number Length (Bytes) Comments/Key Parcel Body Fields
Success 8 18 to 273 StatementNo =1

ActivityCount = Number of sessions identified by the ABORT SESSION request

ActivityType = 86 (PCLABTSESS)
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)
 Data or IndicData list of sessions. This parcel is present only if you specified list.
EndStatement 11 6 StatementNo = 2-byte integer.
Success 8 18 to 273 StatementNo = 2

ActivityCount = 0 (Number of sessions identified by the ABORT SESSION request)

ActivityType = 86 (PCLABTSESS)
DataInfo 71 6 to 64100 Optional; this parcel is present if request was IndicData parcel.
EndStatement 11 6 StatementNo = 2-byte integer.
EndRequest 12 4 None.

The only difference between the parcels returned with the list option set to y or Y, and those returned with the list option set to n, N, blank, or NULL, is that one or more Record parcels are added when you specify the former.

Response

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

In the first response, the database indicates that the request was accepted and is running, and lists the affected sessions and their status if you specify y or Y for list.

Statement 1

The Record parcel in the first statement of the response returns the list of sessions in increasing order of HostId. The following table shows the values returned from the first statement.

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.
UserName VARCHAR(128)

CHARACTER SET UNICODE

NOT NULL

 User name of the session.
SessionNo INTEGER

NOT NULL

 Number of the current session. With a given host ID, a session number uniquely identifies a session on the database system. This value is assigned by the host (or client) at logon time.
AbortStatus VARCHAR(1) Information on the status of the associated session:
  • I = In-Doubt
  • A = Ending a transaction
  • C = Committing a transaction
  • E = Executing (running) an ABORT SESSION request
  • S = Switching
  • NULL = In a state other than the preceding

For an ABORT without LOGOFF, any status except NULL indicates the reason the request did not impact the associated session.

For an ABORT with LOGOFF, an I, E, or S status value indicates that the associated session cannot be ended or logged off.

If you do not specify logging off sessions, the Success parcel produced for the first statement, when applicable, includes a warning that identified sessions are in the process of committing or rolling back their transactions. An ABORT with logoff completes and does not produce any warnings.

Statement 2

In the second response, the database indicates that the request is complete. The completion response is sent only after all impacted sessions have been ended and, if requested, logged off.

Sample Input - CLIv2 Request

The following example shows how the parcels for an ABORT SESSION request, built by CLIv2, look when sent to the database server using a host_id of 52, session_no of 2521, user_name of user_01, list of Y, logoff of Y, and override of N. In this example, the size of the response buffer is set at the maximum (64,000 bytes). The minimum response size is 32,000 bytes.

Number Length Body
Num Name Bytes Field Value
0001 Req 17 Request ABORT SESSION
0003 Data 47 MonVerID 2
HostId 348
SessionNo 1000
UserName user_01
ListOption Y
Logoff Y
Override N
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

With a host_id of 52, session_no of 2521, user_name of DBC, list of Y, and logoff of Y, this request may return the following values in character text format. Your application program may return the values in a different format or display.

Success parcel:
 StatementNo: 1    ActivityCount: 1
 ActivityType: 86    FieldCount: 4
DataInfo iparcel:
 FieldCount: 4
Record parcel.
 Parcel flavor:        10    Parcel body length:   38
 HostId = 52,  UserName = "DBC",  SessionNo = 2521,
 AbortStatus = ' '.
EndStatement.
Success parcel:
 StatementNo: 2    ActivityCount: 0
 ActivityType: 86    FieldCount: 0
DataInfo parcel:
 FieldCount: 0
EndStatement.
EndRequest.

Relationship between ABORT SESSION and MONITOR SESSION

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

Ended Sessions

For ended sessions, expect the following types of changes in the response returned from a subsequent MONITOR SESSION query:
  • PEState changes to PARSING. AMPState changes to ABORTING.
  • AMPCPUSec is updated due to its continued tracking of the CPU time used during the transaction rollback process. Resources consumed during a transaction rollback are charged to the user the same way as any other form of resource usage.
  • AMPIO is updated due to its tracking of logical I/O necessary during the transaction rollback process.
  • Request_AMPSpool decreases if there are spool files in use by the ended request, because those spool files are discarded.
  • After the end operation has completed and if the ended sessions are not logged off, the sessions become IDLE (PEState) while waiting for subsequent requests.

    Ended sessions that are logged off are no longer tracked by subsequent MONITOR SESSION requests.

Blocked Sessions

For sessions blocked or slowed down by ended sessions, expect the following types of changes in the response returned from a subsequent MONITOR SESSION query:
  • The following response fields related to the ended sessions are removed (that is, reported as NULLS because the blocking session is gone):
    • Blk_x_HostId
    • Blk_x_SessNo
    • Blk_x_UserID
    • Blk_x_LMode
    • Blk_x_OType
    • Blk_x_ObjDBId
    • Blk_x_ObjTId
    • Blk_x_Status

      For example, the following describes the information that can be inferred from the returned data:

    • Within a MONITOR SESSION, response fields show Session 1 blocked by Sessions 2 and 3.
    • After an ABORT SESSION, Session 2 is removed.
    • Within a subsequent MONITOR SESSION, response fields show Session 1 blocked by Session 3.
  • The MoreBlockers field may return data indicating there are no additional lock conflicts.

    Because removing an ended session allows another blocking session to be reported, there may be no remaining locks to report.

  • If the ended sessions are no longer blocking other sessions, the AMPState of those other sessions changes from BLOCKED to ACTIVE.
  • For sessions that have changed to ACTIVE, or that are only slowed down by the ended sessions, all resource usage fields may show a more rapid increase in resource usage. For example, the AMPCPUSec, AMPIO, and Request_AMPSpool fields may change more rapidly due to reduction in competition for those resources.

Before You Run ABORT SESSION Request

Run the MONITOR SESSION before you run the ABORT SESSION request to get a list of current sessions. Therefore, you can evaluate which sessions to end. You can use the host ID, session number, and user name returned by the MONITOR SESSION request as input data to an ABORT SESSION request.

MONITOR SESSION returns values that can help you determine which session is not actively processing or has a long-running transaction. For example, look at PEState for the session in question. If PEState is not PARSING-WAIT, PARSING, DISPATCHING, BLOCKED, or ACTIVE, that session may be a good candidate for an end. As another example, look at the ratio of AMPCPUSec to XactCount to determine which transaction is running for a long time. If this ratio is high, this session has a long-running transaction.

Relationship between ABORT SESSION and MONITOR PHYSICAL RESOURCE or MONITOR VIRTUAL RESOURCE

If you ran an ABORT SESSION request, data returned in a MONITOR PHYSICAL RESOURCE or MONITOR VIRTUAL RESOURCE request may be altered. Whether you notice the change in data depends on the scope of the ABORT SESSION request. For example, if you run an ABORT SESSION and log off the sessions associated with a specific host (or client), the PEs associated with that client report a large decrease in resource consumption. However, if the ABORT SESSION request only ends one transaction from one session, you may not notice a change in AMP or PE resource use.