There is two function ABORTSESSIONS
and ABORTLISTSESSIONS that we can use to abort session and list status of abort
session.
Abort Session:
REPLACE FUNCTION SYSLIB.ABORTSESSIONS
(HostIdIn SMALLINT,
UserNameIn TD_ANYTYPE,
SessionNoIn INTEGER,
LogoffSessions VARCHAR(1) CHARACTER SET LATIN,
UserOverride VARCHAR(1) CHARACTER SET LATIN)
RETURNS INTEGER
SPECIFIC AbortSessions
LANGUAGE C
NO SQL
NO EXTERNAL DATA
PARAMETER STYLE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME 'SL!api!F!AbortSessions_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)