CHECK_METADATA¶

Description

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.

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.

-- Defines the stored procedure "CLEAN_METADATA_CACHE", which clears the
-- in-memory cache of metadata
CREATE PROCEDURE CLEAN_METADATA_CACHE
    CLASSNAME='com.denodo.vdb.contrib.storedprocedure.CleanMetadataCache';

-- Execute the procedure "CLEAN_METADATA_CACHE"
CALL CLEAN_METADATA_CACHE();

Syntax

CHECK_METADATA(
      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.

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.

Privileges Required

Only global administrators can invoke this procedure.

Examples

Example 1

SELECT database_name
    ,type
    ,name
    ,reference_to_problematic_element
    ,problem_type
    ,problem_description
FROM CHECK_METADATA()

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

Example 2

SELECT database_name
    ,type
    ,name
    ,reference_to_problematic_element
    ,problem_type
    ,problem_description
FROM CHECK_METADATA()
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
    ,type
    ,name
    ,reference_to_problematic_element
    ,problem_type
    ,problem_description
FROM CHECK_METADATA()
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.