Transaction Control Transformation in Informatica (Example)

What is Transaction Control Transformation?

Transaction Control is an active and connected transformation which 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 the case of failure there are chances of data loss.

So to provide flexibility Transaction control transformation is provided.

TCL COMMIT & ROLLBACK Commands

There are five in-built variables available in this transformation to handle the operation.

TC_CONTINUE_TRANSACTION

In tc_continue_transaction there are no operations performed, the process of data load continues as it is.

TC_COMMIT_BEFORE

In tc_commit_before when this flag is found set, a commit is performed before the processing of current row.

TC_COMMIT_AFTER

In tc_commit_after the current row is processed then a commit is performed.

TC_ROLLBACK_BEFORE

In tc_rollback_before, rollback is performed first then data is processed to write.

TC_ROLLBACK_AFTER

In tc_rollback_after data is processed then the rollback is performed.

Example:

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

Transaction Control Transformation in Informatica with EXAMPLE

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

Transaction Control Transformation in Informatica with EXAMPLE

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

Transaction Control Transformation in Informatica with EXAMPLE

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

Transaction Control Transformation in Informatica with EXAMPLE

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

Transaction Control Transformation in Informatica with EXAMPLE

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.

Transaction Control Transformation in Informatica with EXAMPLE

Step 7 – Select OK in the previous window

Transaction Control Transformation in Informatica with EXAMPLE

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.