Aborts any outstanding request or transaction of one or more sessions, and optionally logs those sessions off the database system.
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.
|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
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.
|logoff||VARCHAR(1)||Indicator of whether to log session_no sessions off the database in addition to aborting them.
To log session_no sessions off the database, specify y or Y. To abort session_no sessions, specify n, N, NULL, or blank.
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.
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.
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.
- Teradata Vantage™ - Database Administration, B035-1093
- Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100
- Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html
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 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.
- 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.
- 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 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 always 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 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, 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 or MultiLoad 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, 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.
Unlike other PM/API requests, ABORT SESSION has an unpredictable execution time and could take hours to roll back a transaction.
- 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.
The 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 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.|
||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.|
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.
In the first response, the 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.
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.
CHARACTER SET UNICODE
|User name of the session.|
|Number of the current session. Together with a given host ID, a session number uniquely identifies a session on the database system. This value is assigned by the host (or client) at logon time.|
|AbortStatus||VARCHAR(1)||Information on the status of the associated
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.
In the second response, the 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 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.
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.
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.
- 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.
- The following response fields related to the aborted sessions are removed
(that is, they are reported as NULLS because the blocking session is gone):
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
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.