Using Variables in a Flow

In this section:

How to:

Reference:

You can use a variable in your data flow to represent a value in a transformation. For process flows, variables are typically used in stored procedures or to control the flow.

The following types of variables can be used in a flow.


Top of page

x
Reference: DataMigrator Variables

DataMigrator variables are available from the Variables tab in the calculators. DataMigrator variables start with &&CM__ (double underscores). They include:

&&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 userid 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 flows with multiple target tables, the variable CM__TARGET has a number appended to the name.


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 stored 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 (program result, special file allocation, and so on) fails, and the entire extract is to be stopped. The default is N.

To reset, assign a value of 'Y" to the global variable KILL_RPC in a set variables object on the process flow tab.


Top of page

x
Reference: System Variables

System variables are identified by a single ampersand and an alphanumeric name, up to ten characters long. These variables can be referenced in stored procedures. Each date shown below represents May 25, 2005.

&DATE

Is the current date (MM/DD/YY) as a character string (A8).

Example: 05/25/05

&DATEDMY

Is the current date (DD/MM/YY) as a character string (A8).

Example: 25/05/05

&DATEDMYY

Is the current four-digit year date (DD/MM/CCYY) as a character string (A10).

Example: 25/05/2005

&DATEMDY

Is the current date (MM/DD/YY) as a character string (A8).

Example: 05/25/05

&DATEMDYY

Is the current four-digit year date (MM/DD/CCYY) as a character string (A10).

Example: 05/25/2005

&DATEYMD

Is the current date (YY/MM/DD) as a character string (A8).

Example: 05/05/25

&DATEYYMD

Is the current four-digit year date (CCYY/MM/DD) as a character string (A10).

Example: 2005/05/25

&DMY

Is the current date as day, month, year. Useful for numerical comparisons.

Example: 250505

&DMYY

Is the current date as day, month, with a four-digit year.

Example: 25052005

&MDY

Is the current date as month, day, year.

Example: 052505

&MDYY

Is the current date as month, day, with a four-digit year.

Example: 05252005

&TOD

Is the current time. (HH.MM.SS)

Example: 17:00:00

&YMD

Is the current date as year, month, day.

Example: 050525

&YYMD

Is the current date as a four-digit year, month, day.

Example: 20050525


Top of page

x
Reference: Statistical Variables

DataMigrator supports the following statistical variables that can be referenced in stored procedures. Statistical variables are recorded up to a value of 2,147,483,647. They 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 due to a format error.

&DELTD

Is the number of rows deleted.

&DUPLS

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

&EXITRC

Is return code value from the execution of an operating system command.

&FORMAT

Is the number of rows rejected as a result of a format error, or when a field is truncated.

&INPUT

Is the number of rows added to the data source.

&INVALID

Is the number of rows rejected as a result of an invalid 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 lines returned in the last answer set.

&RECORDS

Is the number of records retrieved in last answer set.


Top of page

x
Procedure: How to Use DataMigrator or System Variables
  1. From a calculator, select the Columns/Variables tab.

    Note: The variables are grouped by category. To view all variables, right-click in the variables workspace and select View Alphabetical.

  2. Double-click a variable to add it to an expression or condition. You can also type in the name of the variable. DataMigrator and System variables must be enclosed in single quotation marks (‘).

    Note: Match the format (alphanumeric or numeric) to the system variable in a Source or Target Transformation. &TOD and system variables that start with &DATE are alphanumeric. All others are numeric.

    Transformation Calculator

  3. When the expression is complete, click OK.

The variable appears in the Expression column in the Transformations window, as shown in the following page.

Source Transformation window

The Variables tab is also available in the SQL and Filter calculators.


Top of page

x
Using a Parameter With a Flow

How to:

Variables that are used as parameters are called local variables and are identified within a flow by a single ampersand and an alphanumeric name, up to ten characters long. Local variables can be used throughout the flow, for example, in transformations or filters. In order to use a local variable it must have a value.



x
Procedure: How to Use a Local Variable as a Passed Parameter
  1. You can use a local variable, a variable known only to a particular procedure, as a passed parameter.
  2. Type the variable name into an expression or condition in one of the calculators. The variable name must be enclosed in single quotes if it contains alphanumeric or data values, as shown in the following image.

    Filter Calculator

  3. Click OK.


x
Procedure: How to Test a Local Variable
  1. Open the expression in a calculator.
  2. Click the Test SQL Statement button.
  3. DataMigrator prompts you to enter a value for the variable.

    Note that when entering a date value, no quotes are required, and the date must be entered in the format used in a where condition, as shown in the following image.

    Variable prompt window

  4. Click Run.


x
Procedure: How to Pass a Parameter Using a Local Variable
  1. Create a process flow.
  2. In the Process Flow tab, drag the Data Flow that uses the local variable into the workspace, to the right of the Start object.

    Process flow

  3. Double-click the Data Flow object (ibisamp/dmvar), or right-click the object and select Properties.

    The Properties panel opens.

  4. Enter the value for the parameter, as shown in the following image.

    Properties

    Note that you do not need to manually enter the parameter name and value. Clicking on the ellipsis button to the right will bring up a dialog box where you can enter values for the parameters. If you choose to enter a date value manually, no quotes are required, and the date must be entered in the format used in a where condition. Multiple parameters can be separated by commas. Also note that checking Run as Stored Procedure is not required.

  5. Click OK.
  6. Save and run the flow.


x
Procedure: How to Pass a Local Variable as a Parameter in a Parallel Group
  1. Create a process flow.
  2. Add a Parallel Group.
  3. Drag the Data Flows that you want to run inside the Parallel Group.

    Parallel Group

  4. Double-click the data flow that requires a parameter, or right-click it and select Properties.
  5. Enter the value for the parameter, as shown in the following image.

    Value information window

    Note that you do not need to manually enter the parameter name and value. Clicking on the ellipsis button to the right will bring up a dialog box where you can enter values for the parameters. If you choose to enter a date value manually, no quotes are required and the date must be entered in the format used in a where condition. Multiple parameters can be separated by commas.

  6. Alternatively, the value for the local variable can be a (global) variable set in a stored procedure, as shown in the following image.

    Value information window

  7. Click OK.
  8. Save and run the flow.

iWay Software