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.
Note: You can set a value for a variable in the set variables objects on the process flow tab. You can also specify run-time parameters directly from a batch file, shell script, or JCL. For information on setting values when executing flows from CMASAP or CMRUN, see Event-Based Scheduling.
DataMigrator variables are available from the Variables tab in the calculators. DataMigrator variables start with &&CM__ (double underscores). They include:
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.
Is the name of the flow.
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.
Control variables are typically used in process flows.
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.
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.
Is the current date (MM/DD/YY) as a character string (A8).
Example: 05/25/05
Is the current date (DD/MM/YY) as a character string (A8).
Example: 25/05/05
Is the current four-digit year date (DD/MM/CCYY) as a character string (A10).
Example: 25/05/2005
Is the current date (MM/DD/YY) as a character string (A8).
Example: 05/25/05
Is the current four-digit year date (MM/DD/CCYY) as a character string (A10).
Example: 05/25/2005
Is the current date (YY/MM/DD) as a character string (A8).
Example: 05/05/25
Is the current four-digit year date (CCYY/MM/DD) as a character string (A10).
Example: 2005/05/25
Is the current date as day, month, year. Useful for numerical comparisons.
Example: 250505
Is the current date as day, month, with a four-digit year.
Example: 25052005
Is the current date as month, day, year.
Example: 052505
Is the current date as month, day, with a four-digit year.
Example: 05252005
Is the current time. (HH.MM.SS)
Example: 17:00:00
Is the current date as year, month, day.
Example: 050525
Is the current date as a four-digit year, month, day.
Example: 20050525
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.
Is the number of transactions accepted.
Is the number of rows updated.
Is the number of rows rejected due to database constraint violations or for flat file targets due to a format error.
Is the number of rows deleted.
Is the number of rows rejected as a result of duplicate values in the data source.
Is return code value from the execution of an operating system command.
Is the number of rows rejected as a result of a format error or when a field is truncated.
Is the number of rows added to the data source.
Is the number of rows rejected as a result of an invalid condition.
Is the number of rows rejected as a result of not matching a value in the data source.
Is the number of rows rejected for reasons other than the ones specifically tracked by other statistical variables.
Is the number of rows processed.
Is the number lines returned in the last answer set.
Is the number of records retrieved in last answer set.
Note: The variables are grouped by category. To view all variables, right-click in the variables workspace and select View Alphabetical.
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.
The variable appears in the Expression column in the Transformations window.
The Variables tab is also available in the SQL and Filter calculators.
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.
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.
The Procedure Properties panel opens.
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.
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.
iWay Software |