Mapping Parameters and Mapping Variables

Mapping Parameters and Mapping Variables

Mapping parameters and variables represent values in mappings and mapplets.

Mapping Parameter

A mapping parameter represents a value that we can define before running a session. A mapping parameter retains the same value throughout the entire session.

For example, you want to use the same session to extract transaction records for each of the customers individually. Instead of creating a separate mapping for each customer account, you can create a mapping parameter to represent a single customer account. Then use the parameter in a source filter to extract only data for that customer account. Before running the session, you enter the value of the parameter in the parameter file.

If the parameter is not defined in the parameter file, the Integration Service uses the user-defined initial value for the parameter. If the initial value is not defined, the Integration Service uses a default value based on the data type of the mapping parameter.

IsExprVar: TRUE or FALSE

Determines how the Integration Service expands the parameter in an expression string. If true, the Integration Service expands the parameter before parsing the expression. If false, the Integration Service expands the parameter after parsing the expression.

Note: If you set this field to true, you must set the parameter data type to String, or the Integration Service fails the session.

The Integration Service looks for the value in the following order:

1. Value in parameter file

2. Value in pre-session variable assignment

3. Value saved in the repository

4. Initial value

5. Datatype default value

Mapping Variables

A mapping variable represents a value that can change through the session. The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session.

Use mapping variables to perform incremental reads of a source. For example, the customer accounts in the mapping parameter example above are numbered from 001 to 065, incremented by one. Instead of creating a mapping parameter, you can create a mapping variable with an initial value of 001. In the mapping, use a variable function to increase the variable value by one. The first time the Integration Service runs the session, it extracts the records for customer account 001. At the end of the session, it increments the variable by one and saves that value to the repository. The next time the Integration Service runs the session, it extracts the data for the next customer account, 002. It also increments the variable value so the next session extracts and looks up data for customer account 003.

The Integration Service holds two different values for a mapping variable during a session run:

♦Start value of a mapping variable

♦Current value of a mapping variable

Start value

The start value is the value of the variable at the start of the session. The start value could be a value defined in the parameter file for the variable, a value assigned in the pre-session variable assignment, a value saved in the repository from the previous run of the session, a user defined initial value for the variable, or the default value based on the variable data type.

Current value

The current value is the value of the variable as the session progresses. When a session starts, the current value of a variable is the same as the start value. As the session progresses, the Integration Service calculates the current value using a variable function that you set for the variable. The final current value for a variable is saved to the repository at the end of a successful session. When a session fails to complete, the Integration Service does not update the value of the variable in the repository. The Integration Service states the value saved to the repository for each mapping variable in the session log.

If a variable function is not used to calculate the current value of a mapping variable, the start value of the variable is saved to the repository.

Aggregation type

The Integration Service uses the aggregate type of a mapping variable to determine the final current value of the mapping variable.

Types

--> Count

--> Max

--> Min

You can configure a mapping variable for a Count aggregation type when it is an Integer or Small Integer. You can configure mapping variables of any data type for Max or Min aggregation types.

Variable functions

Use variable functions in an expression to set the value of a mapping variable for the next session run.

SetMaxVariable. Sets the variable to the maximum value of a group of values. It ignores rows marked for update, delete, or reject. To use the SetMaxVariable with a mapping variable, the aggregation type of the mapping variable must be set to Max.

SetMinVariable. Sets the variable to the minimum value of a group of values. It ignores rows marked for update, delete, or reject. To use the SetMinVariable with a mapping variable, the aggregation type of the mapping variable must be set to Min.

SetCountVariable. Increments the variable value by one. In other words, it adds one to the variable value when a row is marked for insertion, and subtracts one when the row is marked for deletion. It ignores rows marked for update or reject.

SetVariable. Sets the variable to the configured value. At the end of a session, it compares the final current value of the variable to the start value of the variable. Based on the aggregate type of the variable, it saves a final value to the repository. To use the SetVariable function with a mapping variable, the aggregation type of the mapping variable must be set to Max or Min. The SetVariable function ignores rows marked for delete or reject.

The Integration Service does not save the final current value of a mapping variable to the repository when any of the following conditions are true:

♦The session fails to complete.

♦The session is configured for a test load.

♦The session is a debug session.

♦The session runs in debug mode and is configured to discard session output.

Use a variable function in any of the following transformations:

♦Expression

♦Filter

♦Router

♦Update Strategy

à These will appear in the variables tab of the Expression editor.

à When you use mapping parameters and variables in a Source Qualifier transformation, the Designer expands them before passing the query to the source database

à When you create a reusable transformation in the Transformation Developer, use any mapping parameter or variable. Since a reusable transformation is not contained within any mapplet or mapping, the Designer validates the usage of any mapping parameter or variable in the expressions of reusable transformation for validation.

à Enclose string and date time parameters and variables in quotes in the SQL Editor.

à Mapping parameter and variable values in mapplets must be preceded by the mapplet name in the parameter file, as follows:

mappletname.parameter=value

mappletname.variable=value

à You cannot use variable functions in the Rank or Aggregator transformation.

Default Values for Mapping Parameters and Variables Based on Data type

String – Empty String; Numeric – 0;

Date/time – 1/1/1753 A.D

à Source qualifier filter condition: state = '$$State'

Filter transformation filter condition: state = $$State