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' ], [ 'on_step_failure_action'], [;]

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).

‘on_step_failure’

Optional name of the action to use when job step fails during execution. When set to ‘GoToNextStep’, failure of step execution will be ignored and execution will continue to next step. The default value is ‘QuitWithFailure’, which in case of step failure, will stop execution and fail the job.

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

See Also#