The Denodo server maintains a reference between all its elements. That is, the metadata keeps a reference between data sources and their base views and between the base views and their data source, between views and web services and vice versa, etc. The stored procedure CHECK_METADATA analyses the metadata elements of the Virtual DataPort to guarantee the correctness of all these references.

Inconsistencies found in the metadata information with the CHECK_METADATA stored procedure can also be fixed.

This procedure can be executed not only when the catalog database is the Apache Derby database that is embedded in Virtual DataPort, but also when the feature to store the metadata on an external database is enabled.


To correct inconsistencies in the metadata, this procedure switches the server to single user mode.

Therefore, try to run this procedure in fix mode at a time when the load on the server is very low or non-existent.

This procedure differs from MAINTAIN_METADATA_TABLES in that it detects and resolves inconsistencies in the metadata information instead of reclaiming unused space by removing unused rows from the catalog database.


Requirement in cluster environments with shared metadata

To use this procedure in a cluster environment with shared metadata, execute the commands below first:

-- Defines the stored procedure "CLEAN_METADATA_CACHE", which clears the
-- in-memory cache of metadata

-- Execute the procedure "CLEAN_METADATA_CACHE"


      input_database_name  : int
    , input_databases_list : array
    , input_remove_broken_references : boolean
    , input_remove_broken_views_on_cascade : boolean
    , input_remove_broken_jar_references : boolean
    , input_remove_privileges : boolean
  • input_database_name (optional): name of the database in which the stored procedure will search for inconsistencies.

  • input_databases_list (optional): array of databases that the procedure will check for inconsistencies. You cannot execute the procedure with both the input_database_name and input_databases_list input parameters. If neither of these parameters is defined, the stored procedure will process all of the metadata.

  • input_remove_broken_references (optional): if true, the procedure fixes the inconsistencies found. If false, it lists them. Default value: false.

  • input_remove_broken_views_on_cascade (optional): if true, the procedure removes on cascade the views and stored procedures that does not find in the metadata. Default value: false.

  • input_remove_broken_jar_references (optional): if true, the procedure analyzes whether the extensions (jars) referenced by data sources exist. Default value: false.

  • input_remove_privileges (optional): if true, the procedure analyzes whether the existing privileges are defined for existing items. Default value: true.


Database parameters do not guarantee that other databases are affected by the fixes applied. That is, if an element in a database that is not in the input_database_name or input_databases_list refers to elements that are not found in the databases where the stored procedure is executed, it could be removed on cascade if the option input_remove_broken_views_on_cascade is selected.

The output of this procedure is the list of broken references. If the parameter input_remove_broken_references is true, the procedure will update the references to invalid identifiers with the correct ones and it will include the details of this update in the output. If a valid identifier cannot be found, it will remove the reference and may need to invalidate or remove the element with the inconsistency.

The output schema has the following fields:

  • database_name: name of the database where the inconsistency has been found.

  • type: type of the element with the inconsistency.

  • name: name of the element that contains the broken reference or the inconsistency.

  • reference_to_problematic_element: identifier of the non-existent element.

  • problem_type: type of the inconsistency.

  • problem_description: detailed description of the inconsistency.


The number of rows in the output varies depending on the input_remove_broken_references parameter. If it is true, fixing some problems might cause other inconsistencies to be automatically fixed and those will not be included in the output, although they were listed in the execution with this parameter set to false.


Recommendations to execute the procedure:

  • It is recommended to backup the metadata before executing this stored procedure.

  • Before executing the stored procedure, ensure that you have selected the Retrieve all rows option.

  • Consider to add the queryTimeout context parameter in environments with a big number of virtual databases.

Privileges Required

Only global administrators can invoke this procedure.


Example 1

SELECT database_name

The procedure returns the inconsistencies found in the metadata but it does not modify any element in the server.

Example 2

SELECT database_name
WHERE input_databases_list = { ROW('customer_report'), ROW('operations') }
    AND input_remove_broken_references = true

The procedure returns the inconsistencies found in the databases customer_report and operations, and fixes these inconsistencies.

Example 3

SELECT database_name
WHERE input_remove_broken_references = true
    AND input_remove_broken_jar_references = true
    AND input_remove_broken_views_on_cascade = true
CONTEXT ('queryTimeout'='1800000')

The procedure returns the inconsistencies found in the metadata and fixes all the possible inconsistencies with a query timeout of 30 minutes.

Add feedback