USER MANUALS

MAINTAIN_METADATA_TABLES

Description

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:

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

  2. This procedure removes unused rows from the database, while COMPACT_METADATA_TABLES reclaims unused space by the local catalog database.

Important

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.

Syntax

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.

Example

SELECT *
FROM MAINTAIN_METADATA_TABLES()
WHERE
    INPUT_ACTION = 'PURGE_ROWS';

Privileges Required

Only administrators can invoke this procedure.

Add feedback