Chapter 8. System schema

The built-in SYS and SYSADMIN schemas provide metadata tables and procedures against the current virtual database.

By default, a system schema for ODBC metadata pg_catalog is also exposed. — however, that should be considered for general use.

Metadata visibility

The SYS system schema tables and procedures are always visible and accessible.

When data roles are in use, users can view only the tables, views, and procedure metadata entries that they have permissions to access. All columns of a key must be accessible for an entry to be visible.

Note

To make all metadata visible to any authenticated user, set the environment/system property org.teiid.metadataRequiresPermission to false.

Note

If you use data roles, visibility of entries can be affected by the caching of system metadata.

8.1. SYS schema

System schema for public information and actions.

SYS.Columns

This table supplies information about all the elements (columns, tags, attributes, etc) in the virtual database.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema name

TableName

string

Table name

Name

string

Element name (not qualified)

Position

integer

Position in group (1-based)

NameInSource

string

Name of element in source

DataType

string

Data Virtualization runtime data type name

Scale

integer

Number of digits after the decimal point

ElementLength

integer

Element length (mostly used for strings)

sLengthFixed

boolean

Whether the length is fixed or variable

SupportsSelect

boolean

Element can be used in SELECT

SupportsUpdates

boolean

Values can be inserted or updated in the element

IsCaseSensitive

boolean

Element is case-sensitive

IsSigned

boolean

Element is signed numeric value

IsCurrency

boolean

Element represents monetary value

IsAutoIncremented

boolean

Element is auto-incremented in the source

NullType

string

Nullability: "Nullable", "No Nulls", "Unknown"

MinRange

string

Minimum value

MaxRange

string

Maximum value

DistinctCount

integer

Distinct value count, -1 can indicate unknown

NullCount

integer

Null value count, -1 can indicate unknown

SearchType

string

Searchability: "Searchable", "All Except Like", "Like Only", Unsearchable"

Format

string

Format of string value

DefaultValue

string

Default value

JavaClass

string

Java class that will be returned

Precision

integer

Number of digits in numeric value

CharOctetLength

integer

Measure of return value size

Radix

integer

Radix for numeric values

GroupUpperName

string

Upper-case full group name

UpperName

string

Upper-case element name

UID

string

Element unique ID

Description

string

Description

TableUID

string

Parent Table unique ID

TypeName

string

The type name, which may be a domain name

TypeCode

integer

JDBC SQL type code

ColumnSize

string

If numeric, the precision, if character, the length, and if date/time, then the string length of a literal value.

SYS.DataTypes

This table supplies information on datatypes.

Column nameTypeDescription

Name

string

Data Virtualization type or domain name

IsStandard

boolean

True if the type is basic

Type

String

One of Basic, UserDefined, ResultSet, Domain

TypeName

string

Design-time type name (same as Name)

JavaClass

string

Java class returned for this type

Scale

integer

Max scale of this type

TypeLength

integer

Max length of this type

NullType

string

Nullability: "Nullable", "No Nulls", "Unknown"

IsSigned

boolean

Is signed numeric?

IsAutoIncremented

boolean

Is auto-incremented?

IsCaseSensitive

boolean

Is case-sensitive?

Precision

integer

Max precision of this type

Radix

integer

Radix of this type

SearchType

string

Searchability: "Searchable", "All Except Like", "Like Only", "Unsearchable"

UID

string

Data type unique ID

RuntimeType

string

Data Virtualization runtime data type name

BaseType

string

Base type

Description

string

Description of type

TypeCode

integer

JDBC SQL type code

Literal_Prefix

string

literal prefix

Literal_Prefix

string

literal suffix

SYS.KeyColumns

This table supplies information about the columns referenced by a key.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema name

TableName

string

Table name

Name

string

Element name

KeyName

string

Key name

KeyType

string

Key type: "Primary", "Foreign", "Unique", etc

RefKeyUID

string

Referenced key UID

UID

string

Key UID

Position

integer

Position in key

TableUID

string

Parent Table unique ID

SYS.Keys

This table supplies information about primary, foreign, and unique keys.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema name

Table name

string

Table name

Name

string

Key name

Description

string

Description

NameInSource

string

Name of key in source system

Type

string

Type of key: "Primary", "Foreign", "Unique", etc

IsIndexed

boolean

True if key is indexed

RefKeyUID

string

Referenced key UID (if foreign key)

RefTableUID

string

Referenced key table UID (if foreign key)

RefSchemaUID

string

Referenced key table schema UID (if foreign key)

UID

string

Key unique ID

TableUID

string

Key Table unique ID

SchemaUID

string

Key Table Schema unique ID

ColPositions

short[]

Array of column positions within the key table

SYS.ProcedureParams

This supplies information on procedure parameters.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema name

ProcedureName

string

Procedure name

Name

string

Parameter name

DataType

string

Data Virtualization runtime data type name

Position

integer

Position in procedure args

Type

string

Parameter direction: "In", "Out", "InOut", "ResultSet", "ReturnValue"

Optional

boolean

Parameter is optional

Precision

integer

Precision of parameter

TypeLength

integer

Length of parameter value

Scale

integer

Scale of parameter

Radix

integer

Radix of parameter

NullType

string

Nullability: "Nullable", "No Nulls", "Unknown"

Description

string

Description of parameter

TypeName

string

The type name, which may be a domain name

TypeCode

integer

JDBC SQL type code

ColumnSize

string

If numeric, the precision, if character, the length, and if date/time, then the string length of a literal value.

DefaultValue

string

Default value

SYS.Procedures

This table supplies information about the procedures in the virtual database.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema name

Name

string

Procedure name

NameInSource

string

Procedure name in source system

ReturnsResults

boolean

Returns a result set

UID

string

Procedure UID

Description

string

Description

SchemaUID

string

Parent Schema unique ID

SYS.FunctionParams

This supplies information on function parameters.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema name

FunctionName

string

Function name

FunctionUID

string

Function UID

Name

string

Parameter name

DataType

string

Data Virtualization runtime data type name

Position

integer

Position in procedure args

Type

string

Parameter direction: "In", "Out", "InOut", "ResultSet", "ReturnValue"

Precision

integer

Precision of parameter

TypeLength

integer

Length of parameter value

Scale

integer

Scale of parameter

Radix

integer

Radix of parameter

NullType

string

Nullability: "Nullable", "No Nulls", "Unknown"

Description

string

Description of parameter

TypeName

string

The type name, which may be a domain name

TypeCode

integer

JDBC SQL type code

ColumnSize

string

If numeric, the precision, if character, the length, and if date/time, then the string length of a literal value.

SYS.Functions

This table supplies information about the functions in the virtual database.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema name

Name

string

Function name

NameInSource

string

Function name in source system

UID

string

Function UID

Description

string

Description

IsVarArgs

boolean

Does the function accept variable arguments

SYS.Properties

This table supplies user-defined properties on all objects based on metamodel extensions. Normally, this table is empty if no metamodel extensions are being used.

Column nameTypeDescription

Name

string

Extension property name

Value

string

Extension property value

UID

string

Key unique ID

ClobValue

clob

Clob Value

SYS.ReferenceKeyColumns

This table supplies informaton about column’s key reference.

Column nameTypeDescription

PKTABLE_CAT

string

VDB name

PKTABLE_SCHEM

string

Schema name

PKTABLE_NAME

string

Table/View name

PKCOLUMN_NAME

string

Column name

FKTABLE_CAT

string

VDB name

FKTABLE_SCHEM

string

Schema name

FKTABLE_NAME

string

Table/View name

FKCOLUMN_NAME

string

Column name

KEY_SEQ

short

Key Sequence

UPDATE_RULE

integer

Update Rule

DELETE_RULE

integer

Delete Rule

FK_NAME

string

FK name

PK_NAME

string

PK Nmae

DEFERRABILITY

integer

 

SYS.Schemas

This table supplies information about all the schemas in the virtual database, including the system schema itself (System).

Column nameTypeDescription

VDBName

string

VDB name

Name

string

Schema name

IsPhysical

boolean

True if this represents a source

UID

string

Unique ID

Description

string

Description

PrimaryMetamodelURI

string

URI for the primary metamodel describing the model used for this schema

SYS.Tables

This table supplies information about all the groups (tables, views, documents, and so forth) in the virtual database.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema Name

Name

string

Short group name

Type

string

Table type (Table, View, Document, …)

NameInSource

string

Name of this group in the source

IsPhysical

boolean

True if this is a source table

SupportsUpdates

boolean

True if group can be updated

UID

string

Group unique ID

Cardinality

integer

Approximate number of rows in the group

Description

string

Description

IsSystem

boolean

True if in system table

SchemaUID

string

Parent Schema unique ID

SYS.VirtualDatabases

This table supplies information about the currently connected virtual database, of which there is always exactly one (in the context of a connection).

Column nameTypeDescription

Name

string

The name of the VDB

Version

string

The version of the VDB

Description

string

The description of the VDB

LoadingTimestamp

timestamp

The timestamp loading began

ActiveTimestamp

timestamp

The timestamp when the vdb became active.

SYS.spatial_sys_ref

See also the PostGIS Documentation

Column nameTypeDescription

srid

integer

Spatial Reference Identifier

auth_name

string

Name of the standard or standards body

auth_srid

integer

SRID for the auth_name authority

srtext

string

Well-Known Text representation

proj4text

string

For use with the Proj4 library

SYS.GEOMETRY_COLUMNS

See also the PostGIS Documentation

Column nameTypeDescription

F_TABLE_CATALOG

string

catalog name

F_TABLE_SCHEMA

string

schema name

F_TABLE_NAME

string

table name

F_GEOMETRY_COLUMN

string

column name

COORD_DIMENSION

integer

Number of coordinate dimensions

SRID

integer

Spatial Reference Identifier

TYPE

string

Geometry type name

Note: The coord_dimension and srid properties are determined from the {http://www.teiid.org/translator/spatial/2015}coord_dimension and {http://www.teiid.org/translator/spatial/2015}srid extension properties on the column. When possible, these values are set automatically by the relevant importer. If the values are not set, they will be reported as 2 and 0, respectively. If client logic expects actual values, such as integration with GeoServer, you can set these values manually.

SYS.ArrayIterate

Returns a resultset with a single column with a row for each value in the array. 

SYS.ArrayIterate(IN val object[]) RETURNS TABLE (col object)

Example: ArrayIterate

select array_get(cast(x.col as string[]), 2) from (exec arrayiterate((('a', 'b'),('c','d')))) x

This will produce two rows - 'b', and 'd'.

8.2. SYSADMIN schema

System schema for administrative information and actions.

SYSADMIN.Usage

The following table supplies information about how views and procedures are defined.

Column nameTypeDescription

VDBName

string

VDB name

UID

string

Object UID

object_type

string

Type of object (StoredProcedure, ForeignProcedure, Table, View, Column, etc.)

Name

string

Object Name or parent name

ElementName

string

Name of column or parameter, may be null to indicate a table/procedure. Parameter level dependencies are currently not implemented.

Uses_UID

string

Used object UID

Uses_object_type

string

Used object type

Uses_SchemaName

string

Used object schema

Uses_Name

string

Used object name or parent name

Uses_ElementName

string

Used column or parameter name, may be null to indicate a table/procedure level dependency

Every column, parameter, table, or procedure referenced in a procedure or view definition will be shown as used. Likewise every column, parameter, table, or procedure referenced in the expression that defines a view column will be shown as used by that column. No dependency information is shown for procedure parameters. Column level dependencies are not yet inferred through intervening temporary or common tables.

Example: SYSADMIN.Usage

SELECT * FROM SYSADMIN.Usage

Recursive common table queries can be used to determine transitive relationships.

Example: Finding all incoming usage

with im_using as (
    select 0 as level, uid, Uses_UID, Uses_Name, Uses_Object_Type, Uses_ElementName
      from usage where uid = (select uid from sys.tables where name='table name' and schemaName='schema name')
    union all
    select level + 1, usage.uid, usage.Uses_UID, usage.Uses_Name, usage.Uses_Object_Type, usage.Uses_ElementName
      from usage, im_using where level < 10 and usage.uid = im_using.Uses_UID) select * from im_using

Example: Finding all outgoing usage

with uses_me as (
    select 0 as level, uid, Uses_UID, Name, Object_Type, ElementName
      from usage where uses_uid = (select uid from sys.tables where name='table name' and schemaName='schema name')
    union all
    select level + 1, usage.uid, usage.Uses_UID, usage.Name, usage.Object_Type, usage.ElementName
      from usage, uses_me where level < 10 and usage.uses_uid = uses_me.UID) select * from uses_me

SYSADMIN.MatViews

The following table supplies information about all the materailized views in the virtual database.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema Name

Name

string

Short group name

TargetSchemaName

string

Name of the materialized table schema. Will be null for internal materialization.

TargetName

string

Name of the materialized table

Valid

boolean

True if materialized table is currently valid. Will be null for external materialization.

LoadState

boolean

The load state, can be one of NEEDS_LOADING, LOADING, LOADED, FAILED_LOAD. Will be null for external materialization.

Updated

timestamp

The timestamp of the last full refresh. Will be null for external materialization.

Cardinality

integer

The number of rows in the materialized view table. Will be null for external materialization.

Valid, LoadState, Updated, and Cardinality may be checked for external materialized views with the SYSADMIN.matViewStatus procedure.

Example: SYSADMIN.MatViews

SELECT * FROM SYSADMIN.MatViews

SYSADMIN.VDBResources

The following table provides the current VDB contents.

Column NameTypeDescription

resourcePath

string

The path to the contents.

contents

blob

The contents as a blob.

Example: SYSADMIN.VDBResources

SELECT * FROM SYSADMIN.VDBResources

SYSADMIN.Triggers

The following table provides the triggers in the virtual database.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema Name

TableName

string

Table name

Name

string

Trigger name

TriggerType

string

Trigger Type

TriggerEvent

string

Triggering Event

Status

string

Is Enabled

Body

clob

Trigger Action (FOR EACH ROW …)

TableUID

string

Table Unique ID

Example: SYSADMIN.Triggers

SELECT * FROM SYSADMIN.Triggers

SYSADMIN.Views

The following table provides the views in the virtual database.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema Name

Name

string

View name

Body

clob

View Definition Body (SELECT …)

UID

string

Table Unique ID

Example: SYSADMIN.Views

SELECT * FROM SYSADMIN.Views

SYSADMIN.StoredProcedures

The following table provides the StoredProcedures in the virtual database.

Column nameTypeDescription

VDBName

string

VDB name

SchemaName

string

Schema Name

Name

string

Procedure name

Body

clob

Procedure Definition Body (BEGIN …)

UID

string

Unique ID

Example: SYSADMIN.StoredProcedures

SELECT * FROM SYSADMIN.StoredProcedures

SYSADMIN.Requests

The following table provides active requests against the virtual database. 

VDBName string(255) NOT NULL,
Column nameTypeDescription

VDBName

string

VDB name

SessionId

string

session identifier

ExecutionId

long

execution identifier

Command

clob

The query being executed

StartTimestamp

timestamp

Start timestamp

TransactionId

string

transaction identifier as reported by the Transaction Manager

ProcessingState

string

processing state, can be one of PROCESSING, DONE, CANCELED

ThreadState

string

thread state, can be one of RUNNING, QUEUED, IDLE

SYSADMIN.Sessions

The following table provides the Sessions active for the virtual database.

Column nameTypeDescription

VDBName

string

VDB name

SessionId

string

session identifier

UserName

string

username

CreatedTime

timestamp

timestamp of when the session was created

ApplicationName

string

application name as reported by the client

IPAddress

string

IP Address as reported by the client

SYSADMIN.Transactions

The following table provides the active Transactions.

Column nameTypeDescription

TransactionId

string

transaction identifier as reported by the Transaction Manager

SessionId

string

session identifier if a session is currently associated with the transaction

StartTimestamp

timestamp

start time of the transaction

Scope

string

scope of the transaction, can be one of GLOBAL, LOCAL, REQUEST, INHERITED. INHERITED means that a Transaction was already associated with the calling thread (embedded usage).

Note: Transactions that are not associated with a given session will always be shown. Transactions that are associated with a session must be for a session with the current VDB.

SYSADMIN.isLoggable

Tests if logging is enabled at the given level and context. 

SYSADMIN.isLoggable(OUT loggable boolean NOT NULL RESULT, IN level string NOT NULL DEFAULT 'DEBUG', IN context string NOT NULL DEFAULT 'org.teiid.PROCESSOR')

Returns true if logging is enabled. level can be one of the log4j levels: OFF, FATAL, ERROR, WARN, INFO, DEBUG, TRACE. level defaults to 'DEBUG' and context defaults to 'org.teiid.PROCESSOR'

Example: isLoggable

IF ((CALL SYSADMIN.isLoggable(context=>'org.something'))
BEGIN
   DECLARE STRING msg;
   // logic to build the message ...
   CALL SYSADMIN.logMsg(msg=>msg, context=>'org.something')
END

SYSADMIN.logMsg

Log a message to the underlying logging system. 

SYSADMIN.logMsg(OUT logged boolean NOT NULL RESULT, IN level string NOT NULL DEFAULT 'DEBUG', IN context string NOT NULL DEFAULT 'org.teiid.PROCESSOR', IN msg object)

Returns true if the message was logged. level can be one of the log4j levels: OFF, FATAL, ERROR, WARN, INFO, DEBUG, TRACE. The level defaults to 'DEBUG' and context defaults to 'org.teiid.PROCESSOR'. A null msg object will be logged as the string 'null'.

Example: logMsg

CALL SYSADMIN.logMsg(msg=>'some debug', context=>'org.something')

The preceding example will log the message 'some debug' at the default level DEBUG to the context org.something.

8.2.1. SYSADMIN.refreshMatView

Full refresh/load of an internal materialized view. Returns integer RowsUpdated. -1 indicates a load is in progress, otherwise the cardinality of the table is returned. See the Caching Guide for more information.

See also SYSADMIN.loadMatView 

SYSADMIN.refreshMatView(OUT RowsUpdated integer NOT NULL RESULT, IN ViewName string NOT NULL, IN Invalidate boolean NOT NULL DEFAULT 'false')

8.2.2. SYSADMIN.refreshMatViewRow

Refreshes a row in an internal materialized view.

Returns integer RowsUpdated. -1 indicates the materialized table is currently invalid. 0 indicates that the specified row did not exist in the live data query or in the materialized table. See the Caching Guide for more information. 

SYSADMIN.CREATE FOREIGN PROCEDURE refreshMatViewRow(OUT RowsUpdated integer NOT NULL RESULT, IN ViewName string NOT NULL, IN Key object NOT NULL, VARIADIC KeyOther object)

Example: SYSADMIN.refreshMatViewRow

The materialized view SAMPLEMATVIEW has 3 rows under the TestMat Model as below:

images/refresh_matview_rows_example.png

Assuming the primary key only contains one column, id, update the second row: 

EXEC SYSADMIN.refreshMatViewRow('TestMat.SAMPLEMATVIEW', '101')

Assuming the primary key contains more columns, a and b, update the second row: 

EXEC SYSADMIN.refreshMatViewRow('TestMat.SAMPLEMATVIEW', '101', 'a1', 'b1')

8.2.3. SYSADMIN.refreshMatViewRows

Refreshes rows in an internal materialized view.

Returns integer RowsUpdated. -1 indicates the materialized table is currently invalid. Any row that does not exist in the live data query or in the materialized table will not count toward the RowsUpdated. For more information, see the Teiid Caching Guide. 

SYSADMIN.refreshMatViewRows(OUT RowsUpdated integer NOT NULL RESULT, IN ViewName string NOT NULL, VARIADIC Key object[] NOT NULL)

Example: SYSADMIN.refreshMatViewRows

Continuing use the SAMPLEMATVIEW in Example of SYSADMIN.refreshMatViewRow. Assuming the primary key only contains one column, id, update all rows: 

EXEC SYSADMIN.refreshMatViewRows('TestMat.SAMPLEMATVIEW', ('100',), ('101',), ('102',))

Assuming the primary key comtain more columns, id, a and b compose of the primary key, update all rows: 

EXEC SYSADMIN.refreshMatViewRows('TestMat.SAMPLEMATVIEW', ('100', 'a0', 'b0'), ('101', 'a1', 'b1'), ('102', 'a2', 'b2'))

8.2.4. SYSADMIN.setColumnStats

Set statistics for the given column. 

SYSADMIN.setColumnStats(IN tableName string NOT NULL, IN columnName string NOT NULL, IN distinctCount long, IN nullCount long, IN max string, IN min string)

All stat values are nullable. Passing a null stat value will leave corresponding metadata value unchanged.

8.2.5. SYSADMIN.setProperty

Set an extension metadata property for the given record. Extension metadata is typically used by Translators. 

SYSADMIN.setProperty(OUT OldValue clob NOT NULL RESULT, IN UID string NOT NULL, IN Name string NOT NULL, IN "Value" clob)

Setting a value to null will remove the property.

Example: Property Set

CALL SYSADMIN.setProperty(uid=>(SELECT uid FROM TABLES WHERE name='tab'), name=>'some name', value=>'some value')

The preceding example will set the property 'some name'='some value' on table tab.

Note

The use of this procedure will not trigger replanning of associated prepared plans.

Properties from built-in teiid_* namespaces can be set using the the short form - namespace:key form.

8.2.6. SYSADMIN.setTableStats

Set statistics for the given table. 

SYSADMIN.setTableStats(IN tableName string NOT NULL, IN cardinality long NOT NULL)
Note

SYSADMIN.matViewStatus

matViewStatus is used to retrieve the status of materialized views via schemaName and viewName.

Returns tables which contains TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, OnErrorAction. 

SYSADMIN.matViewStatus(IN schemaName string NOT NULL, IN viewName string NOT NULL) RETURNS TABLE (TargetSchemaName varchar(50), TargetName varchar(50), Valid boolean, LoadState varchar(25), Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction varchar(25))

SYSADMIN.loadMatView

loadMatView is used to perform a complete refresh of an internal or external materialized table.

Returns integer RowsInserted. -1 indicates the materialized table is currently loading. And -3 indicates there was an exception when performing the load. See the Caching Guide for more information. 

SYSADMIN.loadMatView(IN schemaName string NOT NULL, IN viewName string NOT NULL, IN invalidate boolean NOT NULL DEFAULT 'false') RETURNS integer

Example: loadMatView

exec SYSADMIN.loadMatView(schemaName=>'TestMat',viewname=>'SAMPLEMATVIEW', invalidate=>'true')

SYSADMIN.updateMatView

The updateMatView procedure is used to update a subset of an internal or external materialized table based on the refresh criteria.

The refresh criteria might reference the view columns by qualified name, but all instances of . in the view name will be replaced by _, because an alias is actually being used.

Returns integer RowsUpdated. -1 indicates the materialized table is currently invalid. And-3 indicates there was an exception when performing the update. See the Caching Guide for more information. 

SYSADMIN.updateMatView(IN schemaName string NOT NULL, IN viewName string NOT NULL, IN refreshCriteria string) RETURNS integer

SYSADMIN.updateMatView

Continuing use the SAMPLEMATVIEW in Example of SYSADMIN.refreshMatViewRow. Update view rows: 

EXEC SYSADMIN.updateMatView('TestMat', 'SAMPLEMATVIEW', 'id = ''101'' AND a = ''a1''')

SYSADMIN.cancelRequest

Cancel the user request identified by execution id for the given session.

See also SYSADMIN.REQUESTS 

SYSADMIN.cancelRequest(OUT cancelled boolean NOT NULL RESULT, IN SessionId string NOT NULL, IN executionId long NOT NULL)

Example: Cancel

CALL SYSADMIN.cancelRequest('session id', 1)

SYSADMIN.terminateSession

Terminate the session with the given identifier.

See also SYSADMIN.SESSIONS 

SYSADMIN.terminateSession(OUT terminated boolean NOT NULL RESULT, IN SessionId string NOT NULL)

Example: Termination

CALL SYSADMIN.terminateSession('session id')

SYSADMIN.terminateTransaction

Terminate the transaction associated with a session by marking the transaction as rollback only.

See also SYSADMIN.TRANSACTIONS 

SYSADMIN.terminateTransaction(IN sessionid string NOT NULL)
Note

You cannot only cancel transactions that are associated with a session.

Example: Terminate

CALL SYSADMIN.terminateTransaction('session id')