Next >> << Prev

12. Run Variables: Access to Runtime Information

Run Variables are a very useful feature that gives you access to runtime information about a group or job while it's running.

The Run Variables tab is located within the Advanced Parameters form. It's accessible from any group or job by clicking the Advanced Parameters link in the group or job parameters section.

Advanced Params (Run Variables)

The top part of the form shows Custom Run Variables.

The bottom part of the form shows Predefined Run Variables.

 

12.1. Principles: How do Run Variables Work?


Run Variables give you access to runtime information about a group or job while it's running.
You can run variables throughout DMT i.e.:

  • Any SQL statements (Export, Run Pre-Conditions, Pre/Post Processes, etc)
  • Load jobs (in the value Expression)
  • File Run Pre-Conditions
  • Emails

Run variables are placeholders for information. Each variable contains specific information. When DMT runs an export or load, it automatically substitutes run variable names with their respective values.

They typically look like this: $RV_[NAME]

  • RV is the type of run variable (RV for predefined run variables and RVx for custom run variables)
  • NAME is the name of the run variable

Run variables are specific to each group or job. All groups and jobs have the same set of variables (i.e. they all have the same names) but with specific values for each group or job.

Example

For instance you might want to save runtime information in a RUN_LOG table every time a job runs. To achieve this you can use run variables in a pre and a post process.

The pre process inserts a record with the job name and the start date. The SQL statement would be:

INSERT INTO RUN_LOG 
    (NAME, RUN_NUM, BEGIN_DT) 
VALUES 
    ($RV_[GJ NAME], $RV_[RUN NUMBER], $RV_[RUN BEGIN DT], 'Run Started...')

When the job runs, the SQL statement is executed and Run Variable placeholders are substituted by their values. e.g. if our job is called "Test Job 1" the Run Variable $RV_[GJ NAME] will be substituted by the value "Test Job 1".

The post process updates the record after the job has finished running with the run result, the end date, the number of rows, the number of errors and the number of warnings. The SQL statement would be:

UPDATE RUN_LOG SET 
    COMMENTS = 'Run Ended',
    RESULT   = $RV_[RUN RESULT],
    END_DT   = $RV_[RUN END DT], 
    ROWS     = $RV_[NUM ROWS],
    ERRORS   = $RV_[NUM ERRORS],
    WARNINGS = $RV_[NUM WARNINGS]
WHERE NAME = $RV_[GJ NAME] AND RUN_NUM = $RV_[RUN NUMBER]

Note: Run variables are easily differentiated from standard column names with their $RV_ or $RVx_ prefix (e.g. $RV_[RUN NUMBER]). You don't need to know or type the run variable names manually, just right click in the SQL statement area to open a context menu showing all the run variables you can choose from. Select the run variable you wish to use in the menu and it will be automatically inserted in the SQL statement.

 

12.2. Custom Run Variables


Each group or job has 3 custom run variables.

They are called $RV_[CUSTOM1], $RV_[CUSTOM2], $RV_[CUSTOM3].

Custom variables are writable, you can set then with any string or number value of your choice. To set the value of a custom variable just enter it in the Value column next to the run variable name.

 

12.3. Predefined Run Variables


All groups and jobs have the same set of predefined run variables (they have the same names but different values for each group or job).

Predefined run variables are read only. The value column shows the value of each run variable but it cannot be changed.

The list of predefined run variables is as follow:

NameTypeDescription
$RV_[GJ NAME]StringName of the Group or Job that's running
$RV_[GJ TYPE EL]StringType of Group or Job that's running
Possible Values are: Export or Load
$RV_[GJ TYPE GJ]StringType of Group or Job that's running
Possible Values are: Group or Job
$RV_[GROUP TYPE]StringType of Group that's running.
This run variable is only set for groups.
Possible Values are: Logic, Schedule, Data Schedule + Data, Multi Table
$RV_[DATAFILE NAME]StringName of the data file containing data exported or loaded by the group or job.
$RV_[DATAFILE TYPE]StringType of the data file containing data exported or loaded by the group or job.
$RV_[RUN CONTEXT]StringRun context in which the Group or Job is running.
Possible Values are: UI, Scheduler
$RV_[RUN NUMBER]NumberRun number for the Group or Job. It automatically counts the number of times a group or job has been run. It's reset to 0 when a group or job is reset.
$RV_[LINE NUM]NumberLine number (row number) currently written in or read from the data file. This is the only run variable who's value changes while a group or job is running.
$RV_[RUN BEGIN DT]StringData and time when the group or job has started running.
$RV_[RUN END DT]StringData and time when the group or job has finished running.
$RV_[LAST RUN BEGIN DT]StringData and time when the group or job last run has started.
$RV_[LAST RUN END DT]StringData and time when the group or job last run has ended.
$RV_[RUN RESULT]StringResult of the group or job run.
Possible Values are: Success, Warning, Errors, Failed, Cancelled
$RV_[NUM ROWS]NumberNumber or rows exported or loaded by the group or job. Unlike the :LINE_NUM run variable, it's only updated at the end of the run.
$RV_[NUM ERRORS]NumberNumber of errors that occurred during the group or job run.
$RV_[NUM WARNINGS]NumberNumber of warning that occurred during the group or job run.