Uses Of The Denodo GET_VIEWS Stored Procedure

Uses Of The Denodo GET_VIEWS Stored Procedure

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.

Denodo References

Denodo / User Manuals / Virtual DataPort VQL Guide / Stored Procedures / Predefined Stored Procedures / GET_VIEWS

Denodo / User Manuals / Virtual DataPort VQL Guide / Stored Procedures / Predefined Stored Procedures

1 Comment

Leave a Reply

%d bloggers like this: