16.20 - ABORT SESSION - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

Purpose

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

Input Data

If an ABORT SESSION request is submitted when host_id, user_name, or session_no is either left blank or set to NULL, an attempt is made 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 currently 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 aborted. Otherwise, system degradation can result, especially when the abort list contains more than 1500 sessions. The slowdown occurs because all of the 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. Furthermore, in extremely rare cases, perhaps involving an abort of 10,000 sessions, the number of sessions needed 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. For more information on 2PC, see Teradata Vantage™ - Database Design, B035-1094.
  • 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 executing 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 Teradata Database in addition to aborting them.

To log session_no sessions off the Teradata Database, specify y or Y. To abort session_no sessions, specify n, N, NULL, or blank.

override VARCHAR(1) Possible values:
  • y or Y. If you specify y or Y, 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 executing 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 above criteria are ignored, and all sessions that do not fit any of the above criteria are aborted.
  • n, N, NULL, or blank. If you specify n, N, NULL, or blank, and at least one identified session fits one of the above criteria, the ABORT SESSION request will fail.

    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 the list Option

The ABORT SESSION request is different from other PM/API requests, because as soon as processing begins, the ABORT SESSION operation is not abortable.

The ABORT SESSION cannot be stopped even if list displays some 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 - ABORT SESSION

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 takes up many 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 Teradata 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 will not abort nor log off a session under any of the following conditions:
  • The session status is IN-DOUBT.
  • The session is currently being session-switched.
  • The session is a Database Query Log (DBQL) artificial internal session
  • The actual session is currently executing an ABORT SESSION request.

If you attempt to abort a session that is currently executing 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 above conditions.

Although this request may abort and log off other PM/API sessions, it ignores the session from which it was submitted.

At least one of the transactions you want to abort either cannot be aborted or already is being aborted 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 be executed can be done. Therefore, if you specify list, that list will indicate sessions that are:
    • Currently IN-DOUBT
    • Being session-switched
    • Already aborting or committing their own transactions
    • Executing an ABORT SESSION request

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

Type of Session Impact of ABORT SESSION Option Comments
A session with a transaction that is currently being committed or rolled back None with or without logoff The ABORT SESSION request does not fail. Instead, the stage 1 response from the Teradata 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 execution of Teradata Database and is always considered to be more important than any user-initiated work that may be blocked. The Teradata 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 abort 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 aborted. If you attempt to abort 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, they 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, HUTCTL, HUTPARSE, and/or DBCUTIL partition sessions Does not work without logoff Sessions within these partitions do not have transactions or locks associated with them. Also, many of these partitions do 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. As a result, when a FastLoad, MultiLoad, or Archive/Recovery operation needs to be terminated, the ABORT SESSION request typically specifies that all sessions associated with the utility job be aborted and logged off. These utility-related sessions appear in the optional list of sessions identified by this request.

It is easier to kill a FastLoad, MultiLoad, Archive/Recovery, or FastExport job by user_name instead of by session_no. These utilities have multiple sessions under one user_name and it makes little sense to abort one utility session running under that user_name and not another. However, be aware that the same user_name may be running other sessions at the same time and these sessions would also be aborted and logged off.

Processing Messages

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

Therefore, upon receipt of an ABORT SESSION request, the Teradata 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 several ABORT SESSION requests are queued and waiting to finish processing. Information is recorded in the error log.

      Teradata Database should not restart as a result of this error. Depending on the available resources, this request will be processed to completion after all the queued abort requests have completed processing.

  • Lists how many sessions have been affected by the executing request (if Y was entered into the list field).
  • Indicates the request is complete when all identified sessions have been aborted.
  • Indicates that all session have been logged off (if Y was entered into the logoff field).

CLIv2 Response Parcels

Because of the unpredictable execution time of ABORT SESSION, the Teradata Database system handles ABORT SESSION as if it were a two-statement request, with each statement generating a response.

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

Parcel Sequence Parcel Flavor 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 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://developer.teradata.com/connectivity/reference/jdbc-driver .

In the first response, the Teradata Database indicates that the request was accepted and is being executed. It also 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. 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.
AbortStatus VARCHAR(1) Information on the status of the associated session:
  • I = In-Doubt
  • A = Aborting a transaction
  • C = Committing a transaction
  • E = Executing an ABORT SESSION request
  • S = Switching
  • NULL = In some state other than the above

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

For an ABORT with LOGOFF, an I, E, or S status value indicates that the associated session cannot be aborted 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 normally and does not produce any warnings.

Statement 2

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

Sample Input - CLIv2 Request

The following example illustrates how the parcels for an ABORT SESSION request, built by CLIv2, look when sent to the Teradata 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), 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 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 Teradata Database server, see Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver .

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

Aborted Sessions

For aborted 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 aborted request, because those spool files are discarded.
  • After the abort operation has completed and if the aborted sessions are not logged off, the sessions become IDLE (PEState) while they wait for subsequent requests.

    If the aborted sessions are logged off, they are no longer tracked by subsequent MONITOR SESSION requests.

Blocked Sessions

For sessions blocked or slowed down by aborted sessions, expect the following types of changes in the response returned from a subsequent MONITOR SESSION query:
  • The following response fields related to the aborted sessions are removed (that is, they are 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 aborted session allows another blocking session to be reported, there may be no remaining locks to report.

  • If the aborted 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 aborted 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 Execute the ABORT SESSION Request

Execute the MONITOR SESSION before you execute the ABORT SESSION request to get a list of current sessions. Therefore, you can evaluate which sessions to abort. 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.

Some of the values returned by MONITOR SESSION 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 abort. 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.

It is also useful to run the QUERY SESSION request to determine how long a particular transaction is running. Execute the MONITOR SESSION and QUERY SESSION request to pinpoint the source of the problem. By running these two requests, you may not need to abort as many transactions as originally planned. For more information on QUERY SESSION, see Teradata Vantage™ - Database Utilities , B035-1102 .

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

If you executed 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 execute an ABORT SESSION and log off all of the sessions associated with a specific host (or client), the PEs associated with that client will report a large decrease in resource consumption. However, if the ABORT SESSION request only aborts one transaction from one session, you may not notice a change in AMP or PE resource use.