qua_generate_deep_refresh_job#

Creates or updates a job that performs a deep refresh of a set of materialized views, thus automating the tracking of dependencies between objects and orderly refresh of data in depending objects. A deep refresh is a set of actions (steps) executed to refresh data in a view, for example, a bottom-up refresh of data in all materialized dependencies of a view.

Let’s consider a scenario where the following top-down hierarchy of objects exists:

  • Top_View, view, materialized

  • Level1_View, view, not materialized

  • Level0_View, view, materialized

  • Base_Table, base table

Assume that the view Top_View depends on Level1_View, which depends on Level0_View, which depends on the Base_Table. To refresh data in the Top_View, one would need to manually create a job that orderly refreshes all of the materialized views in the hierarchy. In our case, that would be a refresh of the Level0_View, followed by a refresh of the Top_View.

Note

Base tables are not displayed nor considered because they are not materializable by design.

Syntax#

qua_generate_deep_refresh_job 'job_name', 'root_object_names' [, 'excluded_object_names' ] [, 'what_if' ] [;]

Arguments#

job_name

Name of the job to create or update, that will hold all of the necessary steps to refresh data in dependent objects.

root_object_names

Pipe-separated (‘|’) list of names of top-level views that procedure should process dependecies of.

excluded_object_names

Optional, pipe-separated (‘|’) list of names of views that the procedure should ignore, even if an object is a direct or transitive dependency and would have a refresh job step generated.

what_if

Optional boolean value that controls the execution by enabling or disabling a WHAT-IF mode. When set to 1 (true), the results of the analysis are only reported but not saved. Default value is 0 (false).

Result Set#

Return a single-colum result set with a list of information messages describing the execution of the procedure, like a what-if mode status and ignored objects. One of the messages may be of a particular interest, because it holds the definition of the effective graph created and traversed. Graph definition can be easily visualized, for example, by using a Graphviz online.

Permissions#

Procedure operates on a set of metadata accessible to the user, so no special permission are needed.

Example#

Code below creates a My Deep Refresh Job job that refreshes all dependencies of the materialized view Top_View, ignoring the Level0_View that otherwise would be refreshed.

EXEC qua_generate_deep_refresh_job @job_name = 'My Deep Refresh Job'
                                 , @root_object_names     = 'Top_View'
                                 , @excluded_object_names = 'Level0_View'
                                 ;

The result set returned:

message
-------------------------------------------------------------------------------------------------
Targeting a new job 'My Deep Refresh Job'
Adding job step for [Top_View]
Ignoring dependency '[Level0_View]'
Saving job 'My Deep Refresh Job'
The effective dependency graph considered, in DOT format:
digraph G {
0 [tooltip="FullReload", shape=box, label="[Top_View]"];
1 [tooltip="none",       shape=box, label="[Level1_View]"];
2 [tooltip="FullReload", comment="ignored", shape=box, label="[Level0_View]"];
0 -> 1;
1 -> 2;
}
To visualize graph's definition in Graphviz navigate to:
https://dreampuf.github.io/GraphvizOnline