Demonstrate the usage of the transaction control transformation.
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.
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:
The Integration Service does not perform any transaction change for this row. This is the default value of the expression.
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.
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.
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
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
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.
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)
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.
Understanding the Transformations
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.
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.
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.
- 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.