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

Purpose

Aborts any outstanding request or transaction of one or more sessions, and optionally logs those sessions off the 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.

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.

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.

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.

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

Processing Messages

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

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.

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

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.

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.

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

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

Blocked Sessions

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.