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)

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

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 NOT NULL)

Returns true if the message was logged. 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'

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

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

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'

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

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

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

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

SYSADMIN.refreshMatView

Returns integer RowsUpdated. -1 indicates a load is in progress, otherwise the cardinality of the table is returned. See the Caching Guide for more.

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

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.

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

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. See the Caching Guide for more.

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

SYSADMIN.updateMatView

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

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.

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

Metadata Procedures

A MetadataRepository must be configured to make a non-temporary metadata update persistent. See the Developer's Guide Runtime Metadata Updates section for more.

SYSADMIN.setTableStats

Set statistics for the given table.

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

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.

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.

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

This will set the property 'some name'='some value' on table tab.

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