The stored procedure
MAINTAIN_METADATA_TABLES executes maintenance operations on the tables of the database that stores the metadata of Virtual DataPort. This applies when Virtual DataPort stores the metadata in the embedded Derby database (default option) and also, when it is stored on an external database.
When an element (a view, a data source…) is removed from Virtual DataPort, occasionally some references to this element are marked as deleted but left on the metadata database. This procedure purges these rows.
This procedure is different than COMPACT_METADATA_TABLES in that:
This procedure can be used when an external database is configured for storing metadata and “COMPACT_METADATA_TABLES” is only useful when the metadata is in the embedded Derby.
This procedure removes unused rows from the database, while
COMPACT_METADATA_TABLESreclaims unused space by the local catalog database.
When metatada is stored on an external database and there are several Virtual DataPort servers pointing to that database make sure that all nodes are started before executing this procedure.
MAINTAIN_METADATA_TABLES ( input_action : action type ) <action type> ::= 'PURGE_ROWS'
action type: the action for being executed against catalog tables. Currently, the only supported value is
PURGE_ROWS(removes the rows marked as deleted).
The output schema has the following fields:
ACTION: the action executed against catalog tables.
ELEMENT: the catalog element processed.
AFFECTED: number of affected rows. For instance, the number of purged tuples.
ERROR: the error message in case a problem happened processing the current element.
SELECT * FROM MAINTAIN_METADATA_TABLES() WHERE INPUT_ACTION = 'PURGE_ROWS';
Only administrators can invoke this procedure.