Bert Swope
I’m finding that I’m using the get_views() stored procedure more frequently to investigate views in denodo Virtual DataPort (VDP). So, I thought I would take a minute and provide a few ways that get_views stored procedure can help you gather information about denodo VDP views.
Field List of the get_views Stored procedure
The get_views stored procedure provides these fields, in case you are looking for what fields the denodo get_views() procedure has:
- input_database_name
- input_name
- input_user_creator
- input_last_user_modifier
- input_init_create_date
- input_end_create_date
- input_init_last_modification_date
- input_end_last_modification_date
- input_view_type
- input_swap_active
- input_cache_status
- input_description
- input_retrieve_invalid_views_only
- database_name
- name
- type
- user_creator
- last_user_modifier
- create_date
- last_modification_date
- description
- view_type
- swap_active
- cache_status
- folder
- view_status
Find Broken Views
Finding broken views can be very helpful and here is a simple VQL to find broken (Invalid) views. Frequently find that this use of get_views helps with determining unexpected data movement failures or finding views that have been broken by system and or promotion changes to an environment.
Example Denodo VQL:
select database_name, name View_name, View_status
from get_views()
where view_status <= ‘INVALID’;
Find Denodo VDP Views Changed Today
Sometimes you just need to know what view was changed today or on a given date. This can be accomplished easily using a VQL against the get_views() stored procedure. Here is a simple VQL example pulling views which were changed today.
Example VQL:
Select database_name, name
, Last_Modification_Date
, current_date() today
from get_views()
where cast(Last_Modification_Date as date) = current_date();
Finding Who Modified A Denodo VDP View Last
This is an easy what to find out who changed a view last and when it was changed last.
Example VQL:
Select database_name
, name as view_name
, last_user_modifier as last_modifed_By
, last_modification_date as last_modification_date
from get_views()
where name like ‘bv%’;
A Quick List Of Cached Denodo VDP Views
Looking for an easy way to list which views are cached, this VQL will tell that regardless of the cache type. You can limited to a specific database by adding a input_database_name condition to the where clause.
Example Denodo VQL:
select name as view_name
, cache_status
from get_views()
where cache_status <> 0;
Find Denodo VDP Views Without A Description
If you are using VDP to populate you data catalog, you may want to know which views do not have a description, so, that they can be updated. Here a quick to find views without a metadata description. You can limited to a specific database by adding a input_database_name condition to the where clause.
Example Denodo VQL:
SELECT distinct name
FROM GET_VIEWS()
WHERE len(trim(description)) = 0;
This article provides a very limited list of examples of the goodness to be had by taking advantage of the denodo VDP get_views() stored procedure with some simple VQL statement.
I hope you have found this short article helpful in your denodo Virtual DataPort journey.
Great stuff!!