Metadata Procedures

SYSADMIN.getGeneratedColumnNames

This procedure exposes column name generation as we do for materialized tables. Also, it exposes column name generation defined by set of column names and data types.

You need to set the sourceDs and sourceTableOrProc parameters to use this procedure in the first way, or set the sourceSet parameter for the second way. If all parameters are set, the procedure uses the first way.

SQL

SYSADMIN.getGeneratedColumnNames(IN sourceDs string, IN sourceTableOrProc string, IN sourceSet Array, IN targetDs string NOT NULL) 
 RETURNS (position integer NOT NULL, "origName" string NOT NULL, "name" string NOT NULL, "dvType" string NOT NULL, "targetType" string NOT NULL)

Parameters:

Parameter	Description
`sourceDs`	Source data source name
`sourceTableOrProc`	Source table, view, or procedure name
`sourceSet`	Set of column names and data types. Example: `Array('name1', 'integer','name2', 'string(32)')`
`targetDs`	Target data source name

Resultset columns:

Column	Description
`position`	Column position (1..n)
`origName`	Original canonical (upper case) column name
`name`	Destination column name, renamed if needed
`dvType`	DV column type
`targetType`	Native column type for the target data source, can be used for comparison. It may not be exactly the same as in the `CREATE TABLE` query, but it is the same as after refresh

SYSADMIN.setProperty

This procedure sets an extension metadata property for the given record. Extension metadata is typically used by translators.

SQL

SYSADMIN.setProperty(OUT OldValue clob(2097152) NOT NULL RESULT, IN UID string(50) NOT NULL, IN Name string NOT NULL, IN "Value" clob(2097152))

Setting a value to NULL will remove the property.

Here is an example in which the property 'some name' is set to 'some value' on the table tab:

SQL

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

Please note that the use of this procedure will not trigger the replanning of associated prepared plans.

SYSADMIN.translateColumnName

This helper procedure translates column names from a source table to names which will be created for destination DBMS in a materialized table. One column may be passed as a string, but multiple columns should be passed as an array of strings. It is used in the historyUpdate(), upsert()And batchUpdate() procedures.

SQL

SYSADMIN.translateColumnName(IN sourceSchema string NOT NULL, IN sourceTable string NOT NULL, IN destSchema string NOT NULL, IN columns object NOT NULL, OUT translatedColumns object NOT NULL RESULT)

SYSADMIN.translateColumnExpr

This procedure does almost the same as translateColumnName above, but the columns are replaced within an expression. It can be used in the historyUpdate, upsert, and batchUpdate procedures.

SQL

 SYSADMIN.translateColumnExpr(IN sourceSchema string NOT NULL, IN sourceTable string NOT NULL, IN destSchema string NOT NULL, IN expr string NOT NULL, OUT translatedExpr string NOT NULL RESULT)

SYSADMIN.getTypeOfResource

This procedure returns the type of the resource, which can be one of the following:

TABLE
VIEW
PROCEDURE
COLUMN
UNDEFINED

SQL

SYSADMIN.getTypeOfResource(IN name string NOT NULL) 
 RETURNS ("type" string NOT NULL)

name is the name of the resource: model.[view/table.column].