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 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	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 name	Type	Description
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 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	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 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	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 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, and so forth) 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
LoadingTimestamp	timestamp	The timestamp loading began
ActiveTimestamp	timestamp	The timestamp when the vdb became active.

SYS.spatial_sys_ref

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

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

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

Example: SYSADMIN.Triggers

SELECT * FROM SYSADMIN.Triggers

SYSADMIN.Views

The following table provides the views in the virtual database.

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

Example: SYSADMIN.Views

SELECT * FROM SYSADMIN.Views

SYSADMIN.StoredProcedures

The following table provides the StoredProcedures in the virtual database.

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

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 name	Type	Description
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 name	Type	Description
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 name	Type	Description
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.

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.setColumnStats, SYSADMIN.setProperty, SYSADMIN.setTableStats are Metadata Procedures.

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.

Chapter 8. System schema

8.1. SYS schema

8.2. SYSADMIN schema

8.2.1. SYSADMIN.refreshMatView

8.2.2. SYSADMIN.refreshMatViewRow

8.2.3. SYSADMIN.refreshMatViewRows

8.2.4. SYSADMIN.setColumnStats

8.2.5. SYSADMIN.setProperty

8.2.6. SYSADMIN.setTableStats

Learn

Try, buy, & sell

Communities

About Red Hat Documentation

Making open source more inclusive

About Red Hat

Red Hat legal and privacy links

Red Hat legal and privacy links