Transaction Control

Purpose

The Transaction Control transformation is Active and Connected which lets control the commit and rollback transactions based on a set of passing-through data. A commit or rollback can be defined based on a varying number of input rows, for example a common product group key (to ensure that the entire group gets loaded) or invoice date (to avoid loaded an incomplete set of invoices for a given date into a data warehouse).

Use

A transaction is the row or set of rows bound by commit or roll back rows. The number of rows may vary for each transaction.
Transaction control can be defined at two levels:

Within a mapping - a transaction is defined using an expression in a Transaction Control transformation. Based on the return value of the expression, the decision can be made whether to commit, roll back, or continue without any transaction changes.
Within a session - when a session is configured for user-defined commit, the adminisitrator can choose to commit or roll back a transaction if the Integration Service fails to transform or write any row to the target.

The Transaction Control transformation can be used to define conditions to commit and roll back transactions from transactional targets, such as: relational databases, XML, and dynamic MQSeries targets.

The following built-in variables can be used in the Expression Editor to create a transaction control expression:

TC_CONTINUE_TRANSACTION - does not perform any transaction change for this row (the default value).

TC_COMMIT_BEFORE - commits the transaction (write rows to the target) and begins a new transaction (current row is the new transaction).

TC_COMMIT_AFTER - commits the transaction (write rows to the target) and begins a new transaction (current row is the committed transaction).

TC_ROLLBACK_BEFORE - roll backs 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 - 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.

Examples / useful tips

Let's consider source data with invoices sorted by date (INV_DATE column).
To catch date change in the invoices flow, the NEW_DATE indicator is calculated within an Expression Transformation with this expression: IIF(INV_DATE_CURRENT=INV_DATE_PREV, 0,1).
To commit all invoices for a given day the following transaction control expression might be used: IIF(NEW_DATE = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

If the mapping includes an XML target, append or create a new document can be selected on commit. The input groups must receive data from the same transaction control point.

If the transaction control expression evaluates to a value other than commit, roll back, or continue, the Integration Service fails the session.

Each target instance must be conneted to a Transaction Control transformation.

Multiple targets can be connected to a single Transaction Control transformation.

If Dynamic Lookup and Transaction Control transformations are used in the same mapping, a rolled-back transaction might result in unsynchronized target data.

Back to the Informatica tutorial