This document describes how to detect changes in data sources combining the GET_SOURCE_CHANGES and GET_VIEWS stored procedures and how to automate the detection using Denodo Scheduler. This functionality can be used from the Denodo VDP Administration Tool clicking on the Source Refresh button of any base view.
Important note: The instructions provided below have been tested in Denodo 7.0 and 8.0 versions. Please review the section: “Compatibility with Denodo 6.0” to get alternatives for the Denodo 6.0 version.
Denodo provides a stored procedure called GET_SOURCE_CHANGES that detects the differences between the current schema of a base view and its underlying data source. The syntax is:
GET_SOURCE_CHANGES (database_name : text, table_name : text)
This stored procedure returns a row for each field of the view and also a row for each field that is present in the data source, but not in the base view. If a field is an array or a register, there will also be a row for each one of its subfields.
Sometimes, if the virtual database has a lot of base views and the data source schemas are changing quite frequently, the manual execution of this stored procedure for every base view can be cumbersome.
You can create a new view database_change that will show the modified fields in all the base views of your VDP server. The following VQL can be executed to create that database_change view:
CREATE VIEW database_change AS
SELECT source_change.db_name AS db_name, source_change.table_name AS table_name, source_change.field AS field, source_change.type AS type, source_change.old_type AS old_type, source_change.modification AS modification, source_change.depth AS depth
FROM GET_VIEWS() AS base_view
NESTED INNER JOIN GET_SOURCE_CHANGES() AS source_change ON (base_view.input_view_type =0 and base_view.database_name = source_change.db_name AND base_view.name = source_change.table_name)
WHERE source_change.modification <> '';
The next steps explain the operations done in the database_change view that has just been created executing the VQL:
Once we have a view that returns the list of changes in the data sources of Virtual DataPort database, we can create a Scheduler job to automate the detection of these changes. Go to the Scheduler Administration Tool and follow these steps:
SELECT table_name, field, type, old_type, modification, depth
In order to send emails, Denodo Scheduler needs to be also properly configured with the values of an email server. You can find more details in the Mail configuration section of the Scheduler Administration Guide.
The Denodo 6.0 version does not include the GET_SOURCE_CHANGES and GET_VIEWS stored procedures. As an alternative, you can use SOURCE_CHANGES and CATALOG_VIEWS stored procedures. Keep in mind that the syntax for using these procedures may be different from the one explained in the document.
In addition, the procedures available in Denodo 6.0 only return results from the views of the database where the query is executed. If you want to automatically detect changes for different databases, you need to create different data sources in Scheduler pointing to every database and then create a new job for each data source.
Virtual DataPort VQL Guide: GET_SOURCE_CHANGES
Virtual DataPort VQL Guide: GET_VIEWS
Scheduler Administration Guide: Server Configuration - Mail configuration
Scheduler Administration Guide: Time-based Job Scheduling Section
Denodo Connects: Denodo Distributed File System Custom Wrapper - User Manual
Virtual DataPort VQL Guide: CATALOG_VIEWS (Denodo 6.0)
Virtual DataPort VQL Guide: SOURCE_CHANGES (Denodo 6.0)