Transaction Control Transformation in Informatica

  • Updated
  • Posted in Programming
  • 6 mins read


Transaction Control is an lively and related transformation. The transaction management transformation is used to regulate the commit and rollback of transactions. You can outline a transaction based mostly on various variety of enter rows. As an instance, you may outline a transaction on a bunch rows in the staff information utilizing the division Id as a key.

In the informatica energy heart, you may outline the transaction on the following ranges:

  • Mapping degree: Use the transaction management transformation to outline the transactions.
  • Session degree: You can specify the “Commit Type” possibility in the session properties tab. The totally different choices of “Commit Type” are Target, Source and User Defined. If you will have used the transaction management transformation in the mapping, then the “Commit Type” will all the time be “User Defined”

When you run a session, the combination service evaluates the expression for every row in the transaction management transformation. When it evaluates the expression as commit, then it commits all of the rows in the transaction to the goal(s). When the combination service evaluates the expression as rollback, then it roll again all of the rows in the transaction from the goal(s).

When you will have flat file because the goal, then the combination service creates an output file for every time it commits the transaction. You can dynamically identify the goal flat recordsdata. Look on the instance for creating flat recordsdata dynamically – Dynamic flat file creation.

Creating Transaction Control Transformation

Follow the beneath steps to create transaction management transformation:

  • Go to the mapping designer, click on on transformation in the toolbar, Create.
  • Select the transaction management transformation, enter the identify and click on on Create after which Done.
  • You can drag the ports in to the transaction management transformation or you may create the ports manually in the ports tab.
  • Go to the properties tab. Enter the transaction management expression in the Transaction Control Condition.

Configuring Transaction Control Transformation

You can configure the next parts in the transaction management transformation:

  • Transformation Tab: You can rename the transformation and add an outline.
  • Ports Tab: You can create enter/output ports
  • Properties Tab: You can outline the transaction management expression and tracing degree.
  • Metadata Extensions Tab: You can add metadata data.

Transaction Control Expression

You can enter the transaction management expression in the Transaction Control Condition possibility in the properties tab. The transaction management expression makes use of the IIF operate to check every row towards the situation. Use the next syntax for the expression

Syntax: 
IIF (situation, value1, value2)

Example:
IIF(dept_id=10, TC_COMMIT_BEFORE,TC_ROLLBACK_BEFORE)

Use the next built-in variables in the expression editor of the transaction management transformation:

  • TC_CONTINUE_TRANSACTION: The Integration Service doesn’t carry out any transaction change for this row. This is the default worth of the expression.
  • TC_COMMIT_BEFORE: The Integration Service commits the transaction, begins a brand new transaction, and writes the present row to the goal. The present row is in the brand new transaction.
  • TC_COMMIT_AFTER: The Integration Service writes the present row to the goal, commits the transaction, and begins a brand new transaction. The present row is in the dedicated transaction.
  • TC_ROLLBACK_BEFORE: The Integration Service rolls again the present transaction, begins a brand new transaction, and writes the present row to the goal. The present row is in the brand new transaction.
  • TC_ROLLBACK_AFTER: The Integration Service writes the present row to the goal, rolls again the transaction, and begins a brand new transaction. The present row is in the rolled again transaction.

If the transaction management transformation evaluates to a price apart from the commit, rollback or proceed, then the combination service fails the session.

Transaction Control Transformation in Mapping

Transaction management transformation defines or redefines the transaction boundaries in a mapping. It creates a brand new transaction boundary or drops any incoming transaction boundary coming from upstream lively supply or transaction management transformation.

Transaction management transformation could be efficient or ineffective for the downstream transformations and targets in the mapping. The transaction management transformation can grow to be ineffective for downstream transformations or targets when you have used transformation that drops the incoming transaction boundaries after it. The following transformations drop the transaction boundaries.

  • Aggregator transformation with Transformation scope as “All Input”.
  • Joiner transformation with Transformation scope as “All Input”.
  • Rank transformation with Transformation scope as “All Input”.
  • Sorter transformation with Transformation scope as “All Input”.
  • Custom transformation with Transformation scope as “All Input”.
  • Custom transformation configured to generate transactions
  • Transaction Control transformation
  • A a number of enter group transformation, similar to a Custom transformation, related to a number of upstream transaction management factors.

Mapping Guidelines and Validation

Use the next guidelines and tips while you create a mapping with a Transaction Control transformation:

  • If the mapping contains an XML goal, and also you select to append or create a brand new doc on commit, the enter teams should obtain information from the identical transaction management level.
  • Transaction Control transformations related to any goal apart from relational, XML, or dynamic MQSeries targets are ineffective for these targets.
  • You should join every goal occasion to a Transaction Control transformation.
  • You can join a number of targets to a single Transaction Control transformation.
  • You can join just one efficient Transaction Control transformation to a goal.
  • You can’t place a Transaction Control transformation in a pipeline department that begins with a Sequence Generator transformation.
  • If you employ a dynamic Lookup transformation and a Transaction Control transformation in the identical mapping, a rolled-back transaction would possibly consequence in unsynchronized goal information.
  • A Transaction Control transformation could also be efficient for one goal and ineffective for one more goal. If every goal is related to an efficient Transaction Control transformation, the mapping is legitimate.
  • Either all targets or not one of the targets in the mapping ought to be related to an efficient Transaction Control transformation.

Recommended Reading:


SQL Transformation
Informatica Real Time Scenarios – Solutions
Update Strategy Transformation in Informatica
Stored Procedure Transformation in Informatica
Lookup Transformation in Informatica

If you want this text, then please share it or click on on the google +1 button.

Leave a Reply