You can translate the question and the replies:

Export with dependency changing underlying values of ping_data_source function

I have a union on the connection status of several data sources. If I export just that union with the dependencies it changes values in the underlying vql in the export. If I export the entire database that bug does not occur. Here's the value it should be: `ping_data_source('insight','JDBC','submission_ucon',10000)` But here is what is in the dependency export: `ping_data_source('insight','JDBC','txs',10000)` It pings the wrong source. If I try to import in that vql in higher environments it screws up the ping data source with the wrong value. It switches the ping_data_source value to the last table that was dropped into the union.
user
03-08-2020 14:11:20 -0400

3 Answers

Hi, Based on your description, I believe that you are trying to use the [PING_DATA_SOURCE()](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/stored_procedures/predefined_stored_procedures/ping_data_source) stored procedure and have created a view to check the connection status of several data sources. I tried using a query like below to achieve the same, ``` SELECT status from PING_DATA_SOURCE('database','type','name of the data source1',timeout) UNION SELECT status from PING_DATA_SOURCE('database','type','name of the data source2',timeout); ``` After this, I created a view for the same using the below query, ``` CREATE VIEW view_name as SELECT status from PING_DATA_SOURCE('database','datasource_type','name of the data source1',timeout) UNION SELECT status from PING_DATA_SOURCE('database','datasource_type','name of the data source2',timeout); ``` When I executed the view and exported the VQL, the VQL sentences contained the proper full query. Hope this helps!
Denodo Team
04-08-2020 08:06:31 -0400
Here's my union: ` CREATE OR REPLACE VIEW union_all_latest_refresh_dates FOLDER = '/4_union' AS SELECT latest_refresh_date, source, real_time_status FROM (SELECT * FROM source7 UNION SELECT real_time_status, source, latest_refresh_date FROM interface_source1 UNION SELECT real_time_status, latest_refresh_date, source FROM interface_source2 UNION SELECT source, real_time_status, latest_refresh_date FROM source3 UNION SELECT source, real_time_status, latest_refresh_date FROM source4 UNION SELECT latest_refresh_date, source, real_time_status FROM source5 UNION SELECT source, real_time_status, latest_refresh_date FROM source6) CONTEXT ('formatted' = 'yes'); ` Here's one of the bad base views exported: ` CREATE OR REPLACE VIEW bv_source1 FOLDER = '/1_base_views' AS SELECT * FROM ping_data_source('insight','JDBC','source2',10000); CREATE OR REPLACE VIEW dv_source1 FOLDER = '/2_derived_views' AS SELECT bv_source1.database_name AS database_name, bv_source1.data_source_type AS data_source_type, bv_source1.data_source_name AS data_source_name, bv_source1.timeout AS timeout, case WHEN (bv_source1.status <> 'UP') THEN bv_source1.status ELSE NULL END AS status, bv_source1.start_time AS start_time, bv_source1.duration AS duration, bv_source1.down_cause AS down_cause, case WHEN (lower(status) = 'up') THEN 'Source 1' ELSE NULL END AS source FROM bv_source1; ALTER VIEW dv_source1 LAYOUT (bv_source1 = [20, 20, 249, 105]); CREATE OR REPLACE INTERFACE VIEW interface_source1 ( latest_refresh_date:date (sourcetypedecimals = '3', sourcetypesize = '23', sourcetypeid = '93'), source:text, real_time_status:text ) SET IMPLEMENTATION dv_source1( latest_refresh_date = start_time, source = source, real_time_status = status ) FOLDER = '/3_interfaces'; ALTER VIEW interface_source1 LAYOUT (dv_source1 = [600, 20, 242, 206]); ` You can see the ping_data_source switched to source2 at some point. If I export just the base view it is correct: ` CREATE OR REPLACE FOLDER '/1_Base_Views' ; CREATE OR REPLACE VIEW bv_source1 FOLDER = '/1_base_views' AS SELECT * FROM ping_data_source('insight','JDBC','source1',10000); `
user
04-08-2020 10:09:02 -0400
Hi, I was able to implement the query using the stored procedure and the results were as expected. If you are a valid Support user of Denodo, I will suggest you raise a case describing the issue along with the whole VQL of the view, as that would be helpful to understand the background better, reproduce the scenario and address the issue. Hope this helps!
Denodo Team
14-08-2020 03:34:34 -0400
You must sign in to add an answer. If you do not have an account, you can register here