Applies to:
Denodo 8.0
Last modified on: 16 Jun 2021
Tags:
Administration
Connectivity
Error handling
Monitoring
Scheduler jobs
Stored procedures
VQL
View creation
This document describes how to check the status (UP, DOWN or Timeout) of the data sources created in Denodo Virtual DataPort using the stored procedures, PING_DATA_SOURCE and GET_ELEMENTS, and Denodo Scheduler in order to automate the data source status check and notify project stakeholders proactively in case any of the sources are down for any reason.
Denodo provides many predefined stored procedures out of the box that allow automating many common tasks. In this article, we will explain how to leverage the PING_DATA_SOURCE stored procedure that checks whether a specified data source is accessible from Virtual DataPort or not.
CALL PING_DATA_SOURCE ( database_name : text ,data_source_type : <ping_data_source_type> ,data_source_name : text ,timeout : long ) |
<ping_data_source_type> can be any one of the following types:
'JDBC', 'ODBC', 'LDAP', 'OLAP', 'SAPBWBAPI', 'SAPERP', 'SALESFORCE' |
To automate the data sources status check we will combine PING_DATA_SOURCE with another predefined stored procedure called GET_ELEMENTS.
The GET_ELEMENTS stored procedure will list all the elements of a Virtual DataPort server. We will use this to get all the data source elements which will be passed as inputs to the PING_DATA_SOURCE stored procedure.
CALL GET_ELEMENTS ( input_database_name : text , input_name : text , input_type : element type , input_user_creator : text , input_last_user_modifier : text , input_init_create_date : date , input_end_create_date : date , input_init_last_modification_date : date , input_end_last_modification_date : date , input_description : text ) |
You can create a view called data_source_status_check that will show the status of all the data sources in your Virtual DataPort server. The following VQL can be executed to create that data_source_status_check view:
CREATE OR REPLACE VIEW data_source_status_check AS SELECT data_source_status.database_name AS database_name , data_source_status.data_source_name AS data_source_name , data_source_status.data_source_type AS data_source_type , elements.description AS description , data_source_status.status AS status , data_source_status.timeout AS timeout , data_source_status.start_time AS start_time , data_source_status.duration AS duration , data_source_status.down_cause AS down_cause FROM get_elements() AS elements NESTED INNER JOIN ping_data_source() AS data_source_status ON ( elements.database_name = data_source_status.database_name AND elements.subtype = data_source_status.data_source_type AND elements.name = data_source_status.data_source_name ) WHERE ( data_source_status.timeout=15000 AND elements.input_type = 'datasources' AND elements.subtype in ( 'jdbc', 'odbc', 'ldap', 'olap', 'sapbwbapi', 'saperp', 'salesforce' ) ) |
Tree View representation of the data_source_status_check view
The following steps explain the execution flow of the data_source_status_check view just created:
Once we have a view that checks the status of the data sources, we can create a Scheduler job to query the view periodically and send notification through email in case any of the data sources is in DOWN or Timeout status. Log in to the Scheduler Web Administration Tool and follow these steps:
SELECT database_name, data_source_name, data_source_type, description, status, timeout, start_time, duration, down_cause FROM operations.data_source_status_check where status in ('DOWN','TIMEOUT') |
Denodo Email Exported Files Custom Handler - User Manual