System Tables

Page edited by Kylin Soong


Changes (1)

...
| UID | string | Key unique ID |
| OID | integer | Unique ID (see note below) |
| ClobValue | clob | Clob Value |

h1. Table Metadata
...

Full Content

VDB, Schema, and Properties

SYSADMIN.VDBResources

This table provides the current VDB contents.

Column Name Type Description
resourcePath string The path to the contents.
contents blob The contents as a blob.

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

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
OID integer Unique ID (see note below)
Description string Description
PrimaryMetamodelURI string URI for the primary metamodel describing the model used for this schema

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
OID integer Unique ID (see note below)
ClobValue clob Clob Value

Table Metadata

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
OID integer Unique ID (see note below)
Cardinality integer Approximate number of rows in the group
Description string Description
IsSystem boolean True if in system table

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
OID integer Unique ID (see note below)
Description string Description

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)
UID string Key unique ID
OID integer Unique ID (see note below)

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
OID integer Unique ID (see note below)
Position integer Position in key

SYSADMIN.Triggers

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

SYSADMIN.Views

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
Name string View name
Body clob View Definition Body (SELECT ...)
UID string Table Unique ID

SYSADMIN.MatViews

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

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
Name string Short group name
TargetSchemaName string Name of the materialized table schema
TargetName string Name of the materialized table
Valid boolean True if materialized table is currently valid.
LoadState boolean The load state, can be one of NEEDS_LOADING, LOADING, LOADED, FAILED_LOAD
Updated timestamp The timestamp of the last full refresh.
Cardinality integer The number of rows in the materialized view table.

SYSADMIN.Usage

This table supplies information about how views / procedures are defined.

Column Name Type Description
VDBName string VDB name
UID string Object UID
object_type string Type of object (StoredProcedure, View, etc.)
Name string Object Name or parent name if ElementName is not null
ElementName string Name of column or parameter
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 if Uses_ElementName is not null
Uses_ElementName string Used column or parameter name

Spatial Metadata

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

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

Procedure Metadata

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
OID integer Unique ID (see note below)
Description string Description

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

SYSADMIN.StoredProcedures

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
Name string Procedure name
Body clob Procedure Definition Body (BEGIN ...)
UID string Unique ID

Function Metadata

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

Datatype Metadata

SYS.DataTypes

This table supplies information on datatypes.

Column Name Type Description
Name string Teiid design-time type name
IsStandard boolean Always false
IsPhysical boolean Always false
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
OID integer Unique ID (see note below)
RuntimeType string Teiid runtime data type name
BaseType string Base type
Description string Description of type
The OID column is no longer used on system tables.  You should use UIDs instead.
Stop watching space | Change email notification preferences
View Online | View Changes | Add Comment