Transaction Control Transformation in Informatica

Transaction Control is an active and connected transformation. Transaction control allows us to commit or rollback transactions during the execution of the mapping. We can define transactions based on varying number of input rows, for example we can define transaction on group rows in the employees data using DEPTNO as key.  
                  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.
Transaction control can be defined at two levels
In a Mapping Level: Transaction is defined by writing an expression in the transaction control transformation.
In a Session Level: In the session properties we have "Commit type" option is there.In the commit type we have  source commit, target commit and user defined commit options are there. If we used transaction control transformation in the mapping the commit type will always "User defined".
Creating Transaction Control Transformation
  • Go to the mapping designer, Click Transformations
  • Crete Transformation
  • Select Transaction Control-> Specify name-> OK
  • Drag the ports from source qualifier to transaction control or manually we can create ports.
  • Go to Properties-> write expression in expression editor.
The following built-in variables can be used in the Expression Editor to create a transaction control expression
TC_CONTINUE_TRANSACTION: No operations are performed. It is the default value of expression.
TC_COMMIT_BEFORE: 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: Commits the transaction, begins a new transaction, and writes the current row to the target. The current row is in the committed transaction.
TC_ROLLBACK_BEFORE: Rollback is performed first then data is processed to write.
TC_ROLLBACK_AFTER: First data is processed then Rollback is performed

The transaction control transformation evaluates other than Rollback, Commit, Continue operations, then it is failed.

Implementing Transaction Control Transformation

  • Create source EMP table and Target EMP_Trans table..
  • Drag both source and target to the mapping designer. 
  • Create Transaction control transformation and drag the ports from source qualifier to transaction control.
  • Edit the transaction control transformation and write expression on expression editor. 
  • Connect all ports from transaction control to target table 

Powered by Blogger.