AbortListSessions - Teradata Database - Teradata Vantage NewSQL Engine

Application Programming Reference

Product

Teradata Database

Teradata Vantage NewSQL Engine

Release Number

16.20

Published

March 2019

Language

English (United States)

Last Update

2019-05-02

dita:mapPath

vwf1492987142269.ditamap

dita:ditavalPath

changebar_rev_16_20_exclude_audience_ie.ditaval

dita:id

B035-1090

lifecycle

Product Category

Teradata Vantage™

Purpose

Returns the status information of the aborted sessions.

Definition

REPLACE FUNCTION SYSLIB.AbortListSessions
   (HostIdIn   SMALLINT,
   UserNameIn TD_ANYTYPE,
   SessionNoIn INTEGER,
   LogoffSessions VARCHAR(1) CHARACTER SET LATIN,
   UserOverride VARCHAR(1) CHARACTER SET LATIN
  )
  RETURNS TABLE
    (HostId SMALLINT,
    SessionNo INTEGER,
    UserName VARCHAR(128) CHARACTER SET UNICODE,
    AbortStatus CHAR CHARACTER SET LATIN
    )
           .
           .
           . 
;

Input Parameters

Parameter	Description
HostIdIn	Logical ID of a host (or client) with sessions logged on. A value of -1 indicates all hosts.
UserNameIn	User name of the session. An asterisk (*) or NULL indicates all users.
SessionNoIn	ID of the session to abort. A value of zero indicates all sessions.
LogoffSessions	Indicator of whether to log off sessions to Teradata Database in addition to aborting them: Y = Log off and abort sessions. N or NULL = Do not log sessions off.
UserOverride	Indicator of whether to override an ABORT SESSION failure:
UserOverride (continued)	Y = Override 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. N or NULL = Do not override.

Usage Notes - AbortListSessions

This table function is only supported in Constant Mode.

AbortListSessions cannot be used to abort delayed utility sessions because these sessions are not completely logged on.

This function provides similar functionality to the PMPC ABORT SESSION request with ListSessions set to ‘N’ or ‘No’.

Result Rows

Column Name	Description
HostId	Logical host ID of (or client) the aborted sessions were logged on to. For a PE, HostId 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.
SessionNo	Number of the session that was aborted. 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	User name of the session that was aborted.
AbortStatus	Status of the sessions aborted: I = In-Doubt A = Aborting a transaction C = Committing a transaction E = Executing an ABORT SESSION request S = Switching An empty string = In some state other than the above. This value returns when issuing an SQL function. 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.

Example: Using AbortListSessions

SELECT * FROM TABLE (AbortListSessions(1, 'User14', 0, 'Y', 'Y')) AS t1;
 *** Query completed. 5 rows found. 4 columns returned.
 *** Total elapsed time was 4 seconds.
HostId    SessionNo  UserName    AbortStatus
------  -----------  ----------- -----------
     1         1007  USER14
     1         1011  USER14
     1         1010  USER14
     1         1009  USER14
     1         1008  USER14