Transaction Control Transformation in Informatica

Transaction control transformation allows us to commit or rollback transactions during the execution of the mapping.

Commit and rollback operations are of significant importance as it guarantees the availability of data.

When processing a high volume of data, there can be a situation when to commit the data to the target.

If a commit is performed too frequently, then it will be an overhead to the system. If a commit is performed too late then in case of failure there are chances of data loss.

So to provide flexibility Transaction control transformation is provided. There are five in built variables available in this transformation to handle the operation.

1. TC_CONTINUE_TRANSACTION
   In tc_continue_transaction there are no operations performed, the process of data load continues as it is
2. TC_COMMIT_BEFORE
   In tc_commit_before when this flag is found set, a commit is performed before the processing of current row.
3. TC_COMMIT_AFTER
   In tc_commit_after the current row is processed then a commit is performed.
4. TC_ROLLBACK_BEFORE
   In tc_rollback_before, rollback is performed first then data is processed to write
5. TC_ROLLBACK_AFTER
   In tc_rollback_after data is processed then the rollback is performed.

In this example, we will commit data to the target when dept no =20 condition is found true

Step 1 – Create a mapping with EMP as source and EMP_TARGET as target

Step 2 – Create a new transformation using transformation menu, then

1. Select a transaction control as the new transformation
2. Enter transformation name "tc_commit_dept20"
3. Select create option

Step 3 – The transaction control transformation will be created, select done button

Step 4 - Drag and drop all the columns from source qualifier to the transaction control transformation then link all the columns from transaction control transformation to the target table

Step 5 – Double click on the transaction control transformation and then in the edit property window

1. Select property tab
2. Click on the transaction control editor icon

Step 6 –in the expression editor enter the expression –

"iif(deptno=20,tc_commit_before,tc_continue_transaction)" and select OK

It means if deptno 20 is found then commit transaction in target, else continue the current processing.

Step 7 – Select OK in the previous window

Now save the mapping and execute it after creating session and workflows. This mapping will commit the data to the target whenever department number 20 is found in the data.