Transaction Control Mapping

Version 5
Created by Vidya on Jun 25, 2012 10:29 PM. Last modified by Vidya on Mar 18, 2015 7:39 AM.

    Purpose

    Demonstrate the usage of the transaction control transformation.

    Usage

    A transaction is the set of rows bound by commit or roll back rows. You can define a transaction based on a varying number of input rows. You might want to define transactions based on a group of rows ordered on a common key, such as employee ID or order entry date. Informatica’s PowerCenter lets you control commit and roll back transactions based on a set of rows that pass through a Transaction Control transformation.

    Overview

    This transaction control transformation helps in setting up user-defined transactions as opposed to the normal source or target based commits. Using transaction control transformation ignores any commit intervals. Expressions defined in transaction control transformation enables user to setup own transaction boundaries and also enables rollback for all targets within that transaction control unit. The Commit on End of File will only be selectable if the mapping contains an "effective" Transaction Control transformation and if the session has a User Defined Commit. When the session completes If “Commit on End of File" option under session task properties is checked the rows are committed, if the option is not checked the rows are rolled back. This is important since it helps maintain referential integrity with user defined transactions.

    Transaction Control transformations are transaction generators. They define and redefine transaction boundaries in a mapping. They drop any incoming transaction boundary from an upstream active source or transaction generator, and they generate new transaction boundaries downstream.

     

    The following built-in variables can be used in the expression editor when you create a transaction control transformation:

     

    Variable Name

    Data Handling

    TC_CONTINUE_TRANSACTION

    The Integration Service   does not perform any transaction change for this row. This is the default   value of the expression.

    TC_COMMIT_BEFORE

    The Integration Service   commits the transaction, begins a new transaction, and writes the current row   to the target. The current row is in the new transaction.

    TC_COMMIT_AFTER

    The Integration Service   writes the current row to the target, commits the transaction, and begins a   new transaction. The current row is in the committed transaction.

    TC_ROLLBACK_BEFORE

    The Integration Service   rolls back the current transaction, begins a new transaction, and writes the   current row to the target. The current row is in the new transaction

    TC_ROLLBACK_AFTER

    The Integration Service   writes the current row to the target, rolls back the transaction, and begins   a new transaction. The current row is in the rolled back transaction

    Challenge Addressed

    When you run the session, PowerCenter’s  Integration Service evaluates the expression for each row that enters  the transformation. When it evaluates a commit row, it commits all rows  in the transaction to the target or targets. Similarly, when  PowerCenter’s Integration Service evaluates a rollback row, it rolls  back all rows in the transaction from the target or targets.

    Download

     

    The example contains mapping, session and workflow:

     

    • PowerCenter 9.6.x Mapping XML (See Attachments section below)
    • PowerCenter 9.1.0 Mapping XML (See Attachments section below)
    • PowerCenter 8.6.1 Mapping XML (See Attachments section below)

     

    Implementation Guidelines

     

    Use the Transaction Control transformation to define conditions to commit and roll back transactions from transactional targets. Transactional targets include relational, XML, and dynamic MQSeries targets.

     

    For Example, you want to use transaction control to write order information based on the order entry date. You want to ensure that all orders entered on any given date are committed to the target in the same transaction.  This effectively allows you to publish the orders for a given date so that when users access those records or aggregate values derived from them you know that the entire set for the day is available to them.

     

    Transaction control mapping.png

     

    Understanding the Transformations

     

    Transformation Name

    Transformation Type

    Description

    sq_ORDERS

    Source   Qualifier or Application Source Qualifier

    Selects   all records from the source to allow a complete compare with the the target.   The select statement orders the rows by ORDER_ID and ORDER_DATE, this is done   by setting the Number Of   Sorted Ports = 2 Under the properties tab.

    exp_DETERMINEDATE

    Expression

    In the   expression, the local variable is used to compare order dates between rows.

    First, it must   determine when there is a change in the order dates being evaluated.

    Second, it must   store the previous order’s date.

    The NEW_DATE port checks the current value for ORDER_DATE   against the stored value for ORDER_DATE in the variable port, PREV_DATE.

    IIF (ORDER_DATE = PREV_DATE, '0','1')

    The variable port, PREV_DATE, receives the value for ORDER_DATE   after the Integration Service evaluates the NEW_DATE port.

    The DATE_OUT port passes the flag from NEW_DATE port to the   Transaction Control transformation.

    tc_ORDERDATE

    Transaction   Control

    When the   DATE_OUT port from the expression passes a value 1, the Integration Service   commits data when a new order entry date is encountered.

     

    IIF(DATE_OUT = '1',   TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

     

    The transaction control transformation expression contains values that represent actions the Integration Service performs based on the return value of the condition. The Integration Service evaluates the condition on a row-by-row basis. The return value determines whether the Integration Service commits, rolls back, or makes no transaction changes to the row. When the Integration Service issues a commit or roll back based on the return value of the expression, it begins a new transaction.

    Configuring a Session with Transaction Control Transformation

     

    When you configure a session, you configure it for user-defined commit. During a user-defined commit session, the Integration Service commits and rolls back transactions based on a row or set of rows that pass through a Transaction Control transformation. The Integration Service evaluates the transaction control expression for each row that enters the transformation. The return value of the transaction control expression defines the commit or rollback point.

     

    Note:

    • The Mappings were originally built using PowerCenter Designer. If you are using PowerCenter Express (PCX), all the mappings cannot be imported as PCX includes only the Informatica Developer tool and not PowerCenter Designer tool.  For example, Informatica Developer doesn’t support mappings that use “Sequence Generator” transformation.
    • The objects are based on “UTF-8” codepage. If you have changed you codepage, XML may need to be edited.