Tutorial corner

Informatica,ETL,oracle,sql/plsql

Transaction Control Transformation in Informatica with example

Spread the love

Transaction Control Transformation in Informatica , is a connected and active transformation which let you control the commit and roll back of transactions for a set of input data. Example : you may define a transaction on group of employee rows ordered on a common key that is Department_no. In below article we will go through the details of Transaction Control Transformation in Informatica .We will also discuss the steps of adding /configuring Transaction Control  transformation in Informatica Mapping. We will also check  some performance tuning related guidelines for Transaction Control Transformation.

Business Purpose of Transaction Control Transformation:

Transaction Control Transformation help you control the commit and rollback on a group of data.Transaction can be control at below two level in Informatica Center.

Transaction control on Session Level :

By Default , transaction control is defined at session Level in the Session Properties.In Session’s properties, we can set the “Commit Interval” to source/Target or User Defined Level.We can choose to commit or roll back a transaction if the Integration Service fails to transform or write any row to the target.

Transaction Control at Session Level in Informatica

Transaction Control at Session Level in Informatica

When we run the session, the Integration Service evaluates the expression for each row that enters the transformation. When it evaluates a commit row, it commits all rows in the transaction to the target or targets. When the Integration Service evaluates a roll back row, it rolls back all rows in the transaction from the target or targets.

Transaction control on Mapping Level :

To control commit and rollback on mapping level , you will need to use Transaction Control Transformation before Target.With the help of Expression Transformation , we can evaluate the condition to define a transaction and give it as input to Transaction Control Transformation (which actually control it).We will explain it in more detail in below article.

Note: If the mapping has a flat file target you can generate an output file each time the Integration Service starts a new transaction. You can dynamically name each target flat file.

Properties of Transaction Control Transformation in Informatica

  • Active /Passive : Transaction Control Transformation is a Active  transformation as  it control the rows passing through it.
  • Connected/Unconnected Transformation : Transaction Control Transformation is a connected Transformation.
  • Types of ports in Transaction Control Transformation:
    • Input :  For reading input Data
    • Output : For providing output Data

Component of Transaction Control Transformation in Informatica

When you configure a Transaction Control transformation, define the following components:

Component Description
Transformation tab You can rename the transformation and add a description
Port Tab You can define the various Input/Output Port for the Transformation.It can be added by dragging it from other input Transformation
Properties tab You can control the transaction by adding it in the “Transaction control expression”. You can also set the tracing level here.
Meta Data You can add metadata information here.

Configuring Transaction Control Transformation Ports:

You can create input/ouput ports by copying them from another transformation or by manually creating it.

Port Details:

  • Port name:  Add the name of the port.
  • Datatype, precision, and scale:  Configure the datatype and set the precision and scale for each port.
  • Default Value: In case port data is null , you can define its default value..

Configuring Transaction Control Transformation Properties:

Modify the Transaction Control Transformation properties by clicking on the Properties tab.

Property Description
Transaction control expression It enables you to control the Transaction based on the input condition.

Example : IIF(DEPT_NO >4 , TC_COMMIT_AFTER,TC_CONTINUE_TRANSACTION)

Tracing Level Amount of detail displayed in the session log for this transformation. The options are Terse, Normal, Verbose Data, and Verbose Initialization.

Check here for Joiner transformation in informatica

Transaction control expression:

This is the most important part of Transaction Control Transformation , where we define the condition to control the transaction to be performed.You can use  IIF function to test your input row and accordingly issue the transaction.

IIF(DEPT_NO >4 , TC_COMMIT_AFTER,TC_CONTINUE_TRANSACTION)

The Integration Service evaluates the condition on a row-by-row basis. The return value determines whether the Integration Service commits, rolls back, or makes no transaction changes to the row. When the Integration Service issues a commit or roll back based on the return value of the expression, it begins a new transaction.
Below are the list of Transaction variable which you can use.

  • TC_CONTINUE_TRANSACTION:  The Integration Service does not perform any transaction change for this row. This is the default value of the expression.
  • TC_COMMIT_BEFORE: The Integration Service  first commits the current transaction, begins a new transaction, and writes the current row to the  new Transaction.So current will not be committed till now.
  • TC_COMMIT_AFTER:  The 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 Integration Service rolls back the current transaction, begins a new transaction, and writes the current row to the new Transaction.
  • TC_ROLLBACK_AFTER. The Integration Service writes the current row to the target, rolls back the transaction, and begins a new transaction. The current row is in the rolled back transaction.

Note : If the transaction control expression evaluates to a value other than above ones ,then the Integration Service fails the session.

How to use Transaction Control transformation in Informatica:

Use the following procedure to create a Transaction Control transformation in Informatica mapping.

Steps to create an Transaction Control transformation:

  • In the Mapping Designer, open a Mapping.
  • Click Transformation > Create. Then Select Transaction Control transformation.
  • You can also select Transformation by clicking  function  button on Informatica Designer
Transaction Control Transformation Shortcut

Transaction Control Transformation Shortcut

  • Enter a name and click Done.
  • Create ports in Transaction Control transformation  by selecting and dragging it from input transformation to Transaction Control transformation.You can manually create input ports on the Transaction Control Ports tab by double clicking on it.
    Transaction Control Transformation Step 2

    Transaction Control Transformation Step 2

  • Click the Transformation tab and configure transformation properties.
  • Click on expression editor for “Transaction Control Condition” add your condition as shown below.

    Transaction Control Transformation Step 3

    Transaction Control Transformation Step 3

  • Click OK.
  • Connect output ports of Transaction Control Transformation to other transformation or target

Example  of Transaction Control Transformation:

Check here for Example of Transaction control in informatica

Transaction Control Transformation rules and Guidelines:

  • In a mapping, either all targets or none of the targets in the mapping should be connected to Transaction Control transformation.
  • You can connect multiple Target Transformation to the same Transaction control Transformation.
  • You can connect only one effective Transaction Control transformation to a single target.
  • You cannot place a Transaction Control transformation in a pipeline branch that starts with a Sequence Generator transformation.
  • If the mapping includes an XML or File Type target, and you choose to append or create a new document on commit, the input groups must receive data from the same transaction control point.
  • Transaction Control transformations connected to any target other than relational, XML, or dynamic MQSeries targets are ineffective for those targets.
  • If you use a dynamic Lookup transformation and a Transaction Control in the same mapping, a rolled-back transaction might result in unsynchronized target data.
  • A Transaction Control transformation may be effective for one target and ineffective for another target. If each target is connected to an effective Transaction Control transformation, the mapping is valid.

Check here for Aggregator transformation example in informatica

So , in this article we have gone through properties of Transaction Control Transformation in Informatica , component/Properties of Transaction Control Transformation , scenarios where to use it , steps to create it and  performance related guidelines. Hope you enjoyed the article. Please feel free to give your feedback.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme