Transaction Control Variables
Transaction control variables define conditions to commit or rollback transactions during the processing of database rows. You use these variables in transaction control expressions that you build in the Expression Editor. Transaction control expressions use the IIF function to test each row against a condition. Depending on the return value of the condition, the PowerCenter Integration Service commits, rolls back, or makes no transaction changes for the row.
The following example uses transaction control variables to determine where to process a row:
IIF (NEWTRAN=1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)
If NEWTRAN=1, the TC_COMMIT_BEFORE variable causes a commit to occur before the current row processes. Otherwise, the TC_CONTINUE_TRANSACTION variable forces the row to process in the current transaction.
Use the following variables in the Expression Editor when you create a transaction control expression:
- TC_CONTINUE_TRANSACTION - The PowerCenter Integration Service does not perform any transaction change for the current row. This is the default transaction control variable value.
- TC_COMMIT_BEFORE – The PowerCenter 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 PowerCenter 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 PowerCenter 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.