Abort Sessions and List status of Abort Session in Teradata

There is two function ABORTLISTSESSIONS and ABORTLISTSESSIONS that we can use to abort session and list status of abort session.

 

Abort Session:

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(1) CHARACTER SET LATIN

 SPECIFIC AbortListSessions

 LANGUAGE C

 NO SQL

 NO EXTERNAL DATA

 PARAMETER STYLE SQL

 NOT DETERMINISTIC

 CALLED ON NULL INPUT

 EXTERNAL NAME 'SL!api!F!AbortListSessions_U';

 

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   Y = Log off and abort sessions.

•N  N or NULL = Do not log sessions off.

UserOverride

Indicator of whether to override an ABORT SESSION failure:

Y = Override the ABORT SESSION request to fail in any of the following cases:

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

 

Example:

Abort all session for user

SELECT AbortSessions (1, '<USERNAME>', 0, 'N', 'N');

Abort single session for a user

SELECT AbortSessions (1, '<USERNAME>', <SESSION_NUMBER>, 'N', 'N');

 

List status of Abort Session

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(1) CHARACTER SET LATIN

 SPECIFIC AbortListSessions

 LANGUAGE C

 NO SQL

 NO EXTERNAL DATA

 PARAMETER STYLE SQL

 NOT DETERMINISTIC

 CALLED ON NULL INPUT

 EXTERNAL NAME 'SL!api!F!AbortListSessions_U';

 

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.

 

SELECT * FROM TABLE (AbortListSessions(1, 'User1', 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  USER1
     1         1011  USER1
     1         1010  USER1
     1         1009  USER1
     1         1008  USER1


Please visit to know how to kill user or session by using Teradata Global Utility (gtwglobal) 


Comments