Transaction Control Transformation

Explain about Transaction Control Transformation 


Transaction Control transformation  is an active transformation which allows you to control the transactions which are bounded by "commit" and "rollback" transaction control operations
The powercenter supports 2 different ways to control the transactions
The conditional Transaction Control expression can be developed using Transaction Control transformation at mapping level
A commit interval is the number of rows at which an integration service applies commit to the target 

1.  Using Transaction Control transformation  at mapping level
2.  Using commit interval property at session level

IFF(SAL>3000, commit, rollback)

In Properties tab

Enter the transaction control expression in the Transaction Control Condition field. The transaction control expression uses the IIF function to test each row against the condition. Use the following syntax for the expression:

IIF (condition, value1, value2)

The 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. Use the following built-in variables in the Expression Editor when you create a transaction control expression:

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.  

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


This property can be set at session level
The default commit interval is "10000"