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 Data 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 Data Integration Service does not perform any transaction change for the current row. This is the default transaction control variable value.
- •TC_COMMIT_BEFORE. The Data 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 Data 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 Data 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.