Transaction Control Transformation to Control Commit and Rollback in Your ETL
In a typical Informatica PowerCenter workflow data is committed to the target table after a predefined number of rows are processed into target, which is specified in the session properties. But there are scenarios in which you need more control on the commits and rollbacks. In this article, lets see how we can achieve this using Transaction Control Transformation.
What is Transaction Control Transformation
A transaction is the set of rows bound by commit or roll back rows. The Transaction Control Transformation lets you control the commit and rollback transactions based on an expression or logic defined in the mapping. For example, you might want to define transactions based on a group of rows ordered on a common key, such as employee ID or order entry date.
When you run the session, the Integration Service evaluates the expression defined in the transformation for each row that enters the transformation. When it evaluates a commit row, it commits all rows in the transaction to the target. When the Integration Service evaluates a roll back row, it rolls back all rows in the transaction from the target.
Configuring Transaction Control Transformation
Transaction Control Transformation can be created and used as any other active transformations. All the required properties to configure this transformation can be provided in the Properties tab as shown in below image.
You can enter the transaction control expression in the Transaction Control Condition field. The transaction control expression uses the IIF function to test each row against the condition. 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.
You can use the following built-in variables in the Expression Editor when you create a transaction control expression.
- 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 commits the transaction, begins a new transaction. The current row is in the new transaction.
- 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. The current row is in 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.
Transaction Control Transformation Use Case
Lets consider an ETL Job loading data into an OLTP application. The application data is being accessed by the system real time. This means the data loaded into the target table should confirm the consistency and integrity.
To be more specific about the use case, Sales order data loaded into the OLTP Application target table need to be committed after all the order items in a sales order is loaded into the target table.
Solution : Here lets create a Transaction Control Transformation, which is connected in the mapping pipeline after all the ETL logic is complete. The logic to define the commit points can be provided in the Transaction Control Transformation.
Step 1 :- Once the required transformation logic is build in the mapping, you create create a sorter transformation to group all the order items with in a sales order together based on ORDER_ID as shown in below.
Step 2 :- Create an expression transformation and add new ports with below expression. This step will let you identify, when all records in an order is complete processing.
- V_NEXT_ORDER_FLAG (Variable) :- IIF(ORDER_ID = V_PRIOR_ORDER_ID, 'N', 'Y')
- V_PRIOR_ORDER (Variable) :- ORDER_ID
- NEXT_ORDER_FLAG (Output) :- V_NEXT_ORDER_FLAG
Step 3 :- Now you can create the Transaction Control Transformation like any other active transformation and connect to the upstream transformation as shown below. Provide the expression to define the commit logic, below given is the expression per our use case.
- IIF(NEXT_ORDER_FLAG = 'N',TC_CONTINUE_TRANSACTION,TC_COMMIT_BEFORE)
Step 4 :- Now you connect all the ports from Transaction Control transformation to the target definition.
Note :- While configuring the session, be sure to set the "Commit Type" Property as "User Defined"
Note :- While configuring the session, be sure to set the "Commit Type" Property as "User Defined"
Hope this tutorial was useful for your project. Please leave you questions and commends, We will be more than happy to help you.