Assigning and Using Variables in a Process Flow

How to:

Reference:

Variables allow you to control the execution of a process flow by substituting values at run time. A Set Variables object lets you set the value for a variable to be used in other objects. These variables store a string of text or numbers that can be used in the process flow, passed as parameter to a data flow, or used in a procedure.

Some common uses of variables are in a SELECT statement to add a constant value to an answer set or in a WHERE condition to restrict the rows selected. Another use of a variable is to assign a name for a target table.


Top of page

x
Procedure: How to Set a Value for a Variable
  1. Right-click an application directory, select New and click Flow.

    A data flow window opens.

  2. Click the Process Flow tab to switch to that view.
  3. On the Flow tab, in the Insert group, drag the Set Variables Set Variables Button button onto the workspace.
  4. Right-click the Start icon, drag the cursor to the Set Variables icon, and release.

    The workspace should look as shown in the following image.

    Set variable

  5. Right-click the Set Variables object and select Parameter Editor.

    The Set Variables Editor opens as shown in the following image.

    Set variable editor

  6. Click the Add Parameter Add parameter button button.

    The User Variables Calculator opens.

  7. Enter a name and value for the variable, as shown in the following image.

  8. Click OK.

    The Set Variables Editor now displays the details for the parameter, as shown in the following image.

    Set variables editor

  9. Click OK to close the window.

The global variable &&RUNYEAR can now be used in any procedure added to the process flow after the Set Variables object. It can also be used in an embedded (purple) data flow, or in a data flow that is added to the process flow, provided that the Run as procedure option is selected in the properties page for that data flow.


Top of page

x
Reference: Set Variables Editor Toolbar

The Set Variables Editor allows you to assign values to variables.

The Set Variables Editor toolbar contains the following buttons.

Button

Function

Add Parameter

Opens the User Variables Calculator to assign a value to a parameter.

Delete Parameters Button

Delete Parameter

Deletes the currently selected parameter.

Edit Selected Parameter Button

Edit Selected Parameter

Opens the User Variables Calculator for the currently selected parameter.

Test Variables

Tests the variable assignments.

Move Parameter Up Button

Move transforms up

Moves the selected parameter up.

Move Parameter Down

Move transforms down

Moves the selected parameter down.

The Set Variables Editor also contains the following input areas.

Name

Name of the variable.

Command

Provides a drop-down menu with the options of SET or DEFAULT.

Scope
Provides a drop-down menu with the options of LOCAL or GLOBAL.
Expression

Value to be assigned to the variable.



x
Reference: Variable Names, Types, and Assignments

A variable name may be any combination of the characters A through Z, 0 through 9, and the underscore. The first character of the name should be a letter. Variable names are preceded by one or two ampersands depending on the type.

There are two types of variables that can be assigned values in a Process Flow.

LOCAL

Assigns a value for a variable that is used throughout the current flow. It can be used in the current process flow in conditions, email objects, and as parameters to data flows and process flows.

GLOBAL

GLOBAL assigns a value for a variable that is throughout the session. In addition to the places where a local variable can be used, global variables can also be used in the current process flow by any procedures it invokes, as well as any data or process flow that it invokes, provided that its properties has Run as procedure selected.

There are two ways of assigning a value for a variable within a process flow:

DEFAULT

Supplies an initial (default) value for a variable that had no value before the command was processed. It ensures that values are passed to variable whether or not they are provided elsewhere. If the variable already has a value, the command is ignored.

SET

Assigns a value for a variable, which overrides any previously set values.


Top of page

x
Procedure: How to Set a Global Variable

To set a global variable and its value:

  1. Open a data flow.
  2. Click the Process Flow tab.

    The process flow window opens.

  3. Right-click the line joining Start and Data flow, and select Delete.
  4. On the Flow tab, in the Insert group, click Set Variables Set Variables Button and drag it into the workspace between Start and Data Flow.
  5. Right-click Start, drag a joining line to Set Variables, and release. Do the same between Set Variables and Data Flow. Your workflow should look like the following image.

    Set global variable

  6. Double-click the Set Variables object. The Set Variables Editor opens.
  7. Click the Add Parameter button to open the calculator. Enter a name for the variable and a value in the Expression field. Click OK.

    The Set Variables Editor now shows the name and value entered.

  8. Click OK. The name and value appear on the properties page as shown in the following image.

    Properties pane


Top of page

x
Procedure: How to Pass Parameters in a Process Flow

When you are using a procedure in a process flow, you can change the value of its parameters when the flow runs.

From the Process Flow tab,

  1. Double-click the procedure.

    or

    Right-click the procedure and select Properties.

    The Properties attribute pane opens, as shown in the following image.

    Properties Attribute Pane

  2. Select the Parameters ellipsis Properties Attribute Pane button to open the Parameter Editor. Then, select the Add Parameter button to open the Drill Down Parameter Editor.

    Enter PCAT in the Parameter Name field. Leave Constant Value selected and enter Cameras as a parameter value, as shown in the following image.

    Drill Down parameter editor

  3. The value appears in the Parameter Editor.

    Repeat the process for the variable PTYPE, using a parameter value of Digital.

    The parameters can also be entered directly in the Properties pane by following the guidelines below:

    • Do not include the ampersand in the variable name.
    • Use quotes around character and date valued variables.
    • Separate multiple variables with commas.

    Parameter guidelines

  4. When done, select OK to close the Parameter Editor.

    The parameters you entered appear in the Properties page.

  5. Save and Submit the flow.

Top of page

x
Reference: DataMigrator Variables

Most DataMigrator variables start with &&CM__ (double underscores). They set the following variables in each flow:

&&CM__AUTHOR

Is the user ID that originally saved the flow.

Note: &&CM__AUTHOR is the user ID that originally saved the flow, which is not always the user ID that ran the flow. To find the user ID that ran the flow, assign a value of

GETUSER('A32') 

to a variable in the set variables object on the process flow tab.

&&CM__REQUEST

Is the name of the flow.

&&CM__TARGET

Is the name of the target table for the data flow. Note that for multi-table requests the variable CM__TARGET has a number appended to the name.

When you run a flow, these variables are automatically set and can be used in a procedure that follows. For example, you can display them in logs, embed them in reports, and use them in email messages. You can reference these variables in procedures that run before or after data flow execution.


Top of page

x
Reference: Control Variables

Control variables are typically used in process flows.

&&KILL_RPC

Shuts down all flow processing if the value is set to Y. The flow immediately branches to the end of its logic, skipping all code that follows the procedure where this setting is used. Any data flows or procedures are not executed. This may be useful in cases where a site-dependent variable (such as a program result or special file allocation) fails, and the entire extract is to be stopped. The default is N.

To reset, type:

-SET &&KILL_RPC = 'Y'; 

Top of page

x
Reference: Statistical Variables

DataMigrator supports the following statistical variables that can be referenced in procedures. Statistical variables are primarily used in process flows.

&ACCEPTS

Is the number of transactions accepted.

&CHNGD

Is the number of rows updated.

&DBMSERR

Is the number of rows rejected due to database constraint violations, or for flat file targets, format conversion errors.

&DELTD

Is the number of rows deleted.

&DUPLS

Is the number of rows rejected as a result of duplicate values in the data source.

&FOCERRNUM

Is the last error number displayed. If the last command did not generate an error, the value of &FOCERRNUM is 0.

&FORMAT

Is the number of rows rejected as a result of a format error.

&INPUT

Is the number of rows added to the data source.

&INVALID

Is the number of rows rejected as a result of a VALIDATE condition.

&NOMATCH

Is the number of rows rejected as a result of not matching a value in the data source.

&REJECTS

Is the number of rows rejected for reasons other than the ones specifically tracked by other statistical variables.

&TRANS

Is the number of rows processed.

&LINES

Is the number of lines returned in the last answer set.

&READS

Is the physical number of reads from an external file.

&RECORDS

Is the number of records retrieved in the last answer set.


Top of page

x
Reference: Slowly Changing Dimension Variables

Slowly Changing Dimension variables are used to control flows that have their target properties Load Type set to Slowly Changing Dimensions. They allow overriding the default begin and end dates when rows with type II changes are updated and inserted.

&&CM__SCDATE

Overrides today's date as the begin date and end date for Type II changes. This setting is typically used when a run was missed, and it a rerun should be run as though it were run on the earlier date. For example, the following could be used so that an effective date June 1, 2008 is used by including in a procedure that is run before the data flow.

-SET &&CM__SCDATE = 20080601;
&&CM__SCDEIDT

Overrides today's date as the end date for Type II changes for inactive rows. This setting is typically used so that the day prior to the run date becomes the end date. For example, if today is June 1, 2008, then either of the following could be included in a procedure run before the data flow so that an end date of May 31, 2008 is used.

-SET &&CM__SCDEIDT = 20080531;
-SET &&CM__SCDEIDT = AYMD(&YYMD, -1, 'I8YYMD');
&&CM__SCDEADT

Overrides a NULL as the end date to use for Type II changes for active rows. This setting is typically used with a date far in the future. For example, the following could be used in a procedure before the data flow so that January 1, 3000 is used as the end date.

-SET &&CM__SCDEADT = 30000101;

iWay Software