Post Session Variable Assignment Informatica Corp

In the Designer, you can create mapping variables in a mapping or mapplet. After you create a mapping variable, it appears in the Expression Editor. You can then use it in any expression in the mapping or mapplet. You can also use mapping variables in a source qualifier filter, user-defined join, or extract override, and in the Expression Editor of reusable transformations.

Unlike mapping parameters, mapping variables are values that can change between sessions. The Integration Service saves the latest value of a mapping variable to the repository at the end of each successful session.

During the next session run, it evaluates all references to the mapping variable to the saved value. You can override a saved value with the parameter file. You can also clear all saved values for the session in the Workflow Manager.

You might use a mapping variable to perform an incremental read of the source. For example, you have a source table containing timestamped transactions and you want to evaluate the transactions on a daily basis. Instead of manually entering a session override to filter source data each time you run the session, you can create a mapping variable, $$ IncludeDateTime. In the source qualifier, create a filter to read only rows whose transaction date equals IncludeDateTime, such as:

TIMESTAMP = $$IncludeDateTime

In the mapping, use a variable function to set the variable value to increment one day each time the session runs.

If you set the initial value of $$IncludeDateTime to 8/1/2004, the first time the Integration Service runs the session, it reads only rows dated 8/1/2004. During the session, the Integration Service sets $$IncludeDateTime to 8/2/2004. It saves 8/2/2004 to the repository at the end of the session. The next time it runs the session, it reads only rows from August 2, 2004.

Variable Values

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 datatype. The Integration Service looks for the start 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

For example, you create a mapping variable in a mapping or mapplet and enter an initial value, but you do not define a value for the variable in a parameter file. The first time the Integration Service runs the session, it evaluates the start value of the variable to the configured initial value. The next time the session runs, the Integration Service evaluates the start value of the variable to the value saved in the repository. If you want to override the value saved in the repository before running a session, you need to define a value for the variable in a parameter file. When you define a mapping variable in the parameter file, the Integration Service uses this value instead of the value saved in the repository or the configured initial value for the variable.

Note: When you use a mapping variable ('$$MAPVAR') in an expression, the expression always returns the start value of the mapping variable. If the start value of MAPVAR is 0, then $$MAPVAR returns 0. To return the current value of the mapping variable, use the following expression: SETVARIABLE($$MAPVAR, NULL).

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. Unlike the start value of a mapping variable, the current value can change as the Integration Service evaluates the current value of a variable as each row passes through the mapping. 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.

Note: 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.

Variable Datatype and Aggregation Type

When you declare a mapping variable in a mapping, you need to configure the datatype and aggregation type for the variable.

The datatype you choose for a mapping variable allows the Integration Service to pick an appropriate default value for the mapping variable. The default is used as the start value of a mapping variable when there is no value defined for a variable in the parameter file, in the repository, and there is no user defined initial value.

The Integration Service uses the aggregate type of a mapping variable to determine the final current value of the mapping variable. When you have a pipeline with multiple partitions, the Integration Service combines the variable value from each partition and saves the final current variable value into the repository.

You can create a variable with the following aggregation types:

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 datatype for Max or Min aggregation types.

To keep the variable value consistent throughout the session run, the Designer limits the variable functions you use with a variable based on aggregation type. For example, use the SetMaxVariable function for a variable with a Max aggregation type, but not with a variable with a Min aggregation type.

The following table describes the available variable functions and the aggregation types and datatypes you use with each function:

Variable Functions

Variable functions determine how the Integration Service calculates the current value of a mapping variable in a pipeline. Use variable functions in an expression to set the value of a mapping variable for the next session run.

The transformation language provides the following variable functions to use in a mapping:

  • 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. To use the SetCountVariable with a mapping variable, the aggregation type of the mapping variable must be set to Count.
  • 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.

Use variable functions only once for each mapping variable in a pipeline. The Integration Service processes variable functions as it encounters them in the mapping. The order in which the Integration Service encounters variable functions in the mapping may not be the same for every session run. This may cause inconsistent results when you use the same variable function multiple times in a mapping.

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.

Mapping Variables in Mapplets

When you declare a mapping variable for a mapplet and use the mapplet multiple times within the same mapping, the same mapping variable value is shared across all mapplet instances.

Using Mapping Variables

To use mapping variables, complete the following steps:

  1. Create a mapping variable.
  2. Use the variable and set the variable value.You might use a mapping variable to perform an incremental read of the source. For example, you have a source table containing timestamped transactions and you want to evaluate the transactions on a daily basis. Instead of manually entering a session override to filter source data each time you run the session, you can create a mapping variable,
  3. Override or clear the variable value.

Step 1. Create a Mapping Variable

You can create a mapping variable for any mapping or mapplet. You can create as many variables as you need.Once created, use the variable in the mapping or mapplet.

To create a mapping variable:

  1. In the Mapping Designer, click Mappings > Parameters and Variables. Or, in the Mapplet Designer, click Mapplet > Parameters and Variables.
  2. Click the Add button.
  3. Specify the variable information.
  4. The following table describes the options on the Declare Parameters and Variables dialog box:

  5. Click OK.

Step 2. Set a Mapping Variable Value

After you declare a variable, use it in any expression in the mapping or mapplet. You can also use a mapping variable in a Source Qualifier transformation or reusable transformation.

In a Source Qualifier transformation, mapping variables appear on the Variables tab in the SQL Editor. When using mapping variables in a Source Qualifier transformation follow these rules:

  • Enclose string variables in string identifiers, such as single quotation marks, to indicate the variable is a string.
  • When necessary, change the format of the datetime variable to match the format in the source. The Integration

Service converts dates from the PowerCenter default date format to the default date format of the source system.

In other transformations in a mapplet or mapping, mapping variables appear in the Expression Editor. When you write expressions that use mapping variables, you do not need string identifiers for string variables.

Use mapping variables in reusable transformations. When you validate the expression, the Designer treats the variable as an Integer datatype.

You can also use mapping variables in transformation overrides in the session properties. You can override properties such as a filter or user-defined join in a Source Qualifier transformation.

When you use a mapping variable, you have to determine how to set the value of the mapping variable. Use a variable function to set a variable value. Use a variable function in any of the following transformations:

  • Expression
  • Filter
  • Router
  • Update Strategy

Step 3. Override or Clear Saved Values

After a session completes successfully, the Integration Service saves the final value of each variable in the repository. When you do not want to use that value the next time you run the session, you can override the value in the parameter file or the pre-session variable assignment in the session properties.

When you do not want to use any of the variable values saved for a session, you can clear all saved values. You can clear variable values for a session using the Workflow Manager. After you clear variables values from the repository, the Integration Service runs the session as if for the first time.

Guys, 

Do we have any clue to this issue if it got resolved. We tried checking Informatica/ network  etc.
Many blogs suggst its issue at the clinet side i.e configuration etc, but still no luck. The issue is now speadingly widely across multiple application for different enviroement. We had some similar issue an year back, same CLI error , not  extactly 207, but things went fine after restarting the Integration services at Informatica end.
I am still stuck at the issue. :(

Severity               Timestamp         Node     Thread  Message Code  Message
INFO      30/05/2016 10:00:58        node01_ITSUSMPL00311              WRITER_1_*_1 TPTWR_32605   [INFO] DML statements on table [CTM_S.PLW_PLW_CODE_PRIM_INDIC_DIM] for system operator [TD_LOAD] built successfully.

INFO      30/05/2016 10:01:02        node01_ITSUSMPL00311              WRITER_1_*_1 TPTWR_24516   [INFO] [CTM_S.PLW_PLW_CODE_PRIM_INDIC_DIM] Table has been Truncated using [ODBC Connection].

INFO      30/05/2016 10:02:18        node01_ITSUSMPL00311                              TPTWR_35120   [INFO] A error has occured for instance [1] for target [1].

INFO      30/05/2016 10:02:18        node01_ITSUSMPL00311                              TPTWR_34210   [DEBUG] Formatting TPT API error message...

ERROR  30/05/2016 10:02:18        node01_ITSUSMPL00311                              TPTWR_207        [ERROR] Type:(Teradata CLI Error), Error: (CLI error 207 (no message text available))
ERROR  30/05/2016 10:02:18        node01_ITSUSMPL00311                              TPTWR_21211   [ERROR] Plug-in failed to Initiate Teradata database connection.

ERROR  30/05/2016 10:02:18        node01_ITSUSMPL00311              WRITER_1_*_1 SDKS_38502       Plug-in #315000's target [PLW_PLW_CODE_PRIM_INDIC_DIM: Partition 1] failed in method [init].
ERROR  30/05/2016 10:02:18        node01_ITSUSMPL00311              WRITER_1_*_1 WRT_8068           Writer initialization failed. Writer terminating.
INFO      30/05/2016 10:02:19        node01_ITSUSMPL00311                              TPTWR_32203   [INFO] Teradata database connection terminated successfully.

ERROR  30/05/2016 10:02:19        node01_ITSUSMPL00311              WRITER_1_*_1 SDKS_38502       Plug-in #315000's target [PLW_PLW_CODE_PRIM_INDIC_DIM: Partition 1] failed in method [deinit].



One other log for the TPT trace.

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_33304 : [TRACE] Retrieving Target Driver for given Target index...

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_33403 : [TRACE] Retrieving Group Driver for given Group index...

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_32502 : [INFO] De-Initialized Teradata PT Writer Group Driver.

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_35118 : [INFO] TPTSyncManager for Target [1] is deintialized.

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_32402 : [INFO] De-Initialized Teradata PT Writer Target Driver.

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : SDKS_38510 : SDK target and group deinitialized with status [-1].

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_32302 : [INFO] De-Initialized Teradata PT Writer Plug-In.

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : SDKS_38023 : Plug-in 315000's [deinit] method invocation successful.

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : SDKS_38025 : Plug-in 315000 deinitialized and unloaded with status [-1].

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : SDKS_38019 : Writer SDK plug-ins deinitialized with status [-1].

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TM_6018 : The session completed with [0] row transformation errors. 

2016-06-09 13:22:07 : INFO : (53768 | MANAGER) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : PETL_24002 : Parallel Pipeline Engine finished.

2016-06-09 13:22:07 : INFO : (53768 | DIRECTOR) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : PETL_24013 : Session run completed with failure.

2016-06-09 13:22:07 : INFO : (53768 | DIRECTOR) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TM_6846 : Ready to execute post-session variable assignment.

2016-06-09 13:22:07 : INFO : (53768 | DIRECTOR) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TM_6847 : Post-session variable assignment executed.

2016-06-09 13:22:07 : INFO : (53768 | DIRECTOR) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TM_6022 : 

Categories: 1

0 Replies to “Post Session Variable Assignment Informatica Corp”

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *