8.8. System Procedures
8.8. System Procedures SYS.getXMLSchemas Returns a result set with a single column, schema, containing the schemas as clobs. SYS.getXMLSchemas(document in string) returns schema string SYSADMIN.logMsg Log a message to the underlying logging system. SYSADMIN.logMsg(logged RETURN boolean, level IN string, context IN string, msg IN object) 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' SYSADMIN.isLoggable Tests if logging is enabled at the given level and context. SYSADMIN.isLoggable(loggable RETURN boolean, level IN string, context IN string) 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' SYSADMIN.refreshMatView Returns integer RowsUpdated. -1 indicates a load is in progress, otherwise the cardinality of the table is returned. SYSADMIN.refreshMatView(RowsUpdated return integer, ViewName in string, Invalidate in boolean) SYSADMIN.refreshMatViewRow 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. SYSADMIN.refreshMatViewRow(RowsUpdated return integer, ViewName in string, Key in object) 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. SYSADMIN.refreshMatViewRows(OUT RowsUpdated integer NOT NULL RESULT, IN ViewName string NOT NULL, VARIADIC Key object[] NOT NULL) Using SAMPLEMATVIEW as an example and assuming the primary key only contains one column, id, here is how you update all rows: Assuming the primary key contains more columns, id, a and b compose of the primary key, here is how you update all rows: SYSADMIN.updateMatView Use the updateMatView procedure to update a subset of an internal or external materialized table based on the refresh criteria. The refresh criteria may reference the view columns by qualified name, but all instances of '.' in the view name are replaced by '_' as an alias is actually being used. This returns the integer RowsUpdated . -1 indicates that the materialized table is currently invalid. -3 indicates there was an exception when performing the update. 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) This produces two rows, 'b', and 'd':
EXEC SYSADMIN.refreshMatViewRows('TestMat.SAMPLEMATVIEW', ('100',), ('101',), ('102',)) EXEC SYSADMIN.refreshMatViewRows('TestMat.SAMPLEMATVIEW', ('100', 'a0', 'b0'), ('101', 'a1', 'b1'), ('102', 'a2', 'b2')) SYSADMIN.updateMatView(IN schemaName string NOT NULL, IN viewName string NOT NULL, IN refreshCriteria string) RETURNS integer select array_get(cast(x.col as string[]), 2) from (exec arrayiterate((('a', 'b'),('c','d')))) x