SYS Schema

Tables/Views

SYS.Columns

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

Column Name Type Description

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

Teiid 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 Name Type Description

Name

string

Teiid 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

Teiid 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 Name Type Description

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 Name Type Description

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 Name Type Description

VDBName

string

VDB name

SchemaName

string

Schema Name

ProcedureName

string

Procedure name

Name

string

Parameter name

DataType

string

Teiid 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

SYS.Procedures

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

Column Name Type Description

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 Name Type Description

VDBName

string

VDB name

SchemaName

string

Schema Name

FunctionName

string

Function name

FunctionUID

string

Function UID

Name

string

Parameter name

DataType

string

Teiid 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 Name Type Description

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 Name Type Description

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 Name Type Description

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 Name Type Description

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, etc) in the virtual database.

Column Name Type Description

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 Name Type Description

Name

string

The name of the VDB

Version

string

The version of the VDB

Description

string

The description of the VDB

SYS.spatial_sys_ref

See also the PostGIS Documentation

Column Name Type Description

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 Name Type Description

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 will be set automatically by the relevant importer. If they are not set, they will be reported as 2 and 0 respectively. If client logic expects actual values, such as integration with GeoServer, then you may need to set these values manually.

Procedures

SYS.getXMLSchemas

Returns a resultset with a single column, schema, containing the schemas as xml.

SYS.getXMLSchemas(IN document string NOT NULL) RETURNS TABLE (schema xml)

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

results matching ""

    No results matching ""