Transaction Control Transformation
A Transaction Control transformation is an active and connected transformation. It allows us to commit and rollback transactions based on a set of rows that pass through a Transaction Control transformation.
Commit and rollback operations are of significant importance as it guarantees the availability of data.
A transaction is the set of rows bound by commit or rollback rows. We can define a transaction based on the varying number of input rows. We can also identify transactions based on a group of rows ordered on a common key, such as employee ID or order entry date.
When processing a high volume of data, there can be a situation to commit the data to the target. If a commit is performed too quickly, 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 losing the data. So the Transaction control transformation provides flexibility.
In PowerCenter, the transaction control transformation is defined in the following levels, such as:
When we run the session, then the Integration Service evaluates the expression for each row that enters the transformation. When it evaluates a committed row, then it commits all rows in the transaction to the target or targets. When the Integration Service evaluates a rollback row, then it rolls back all rows in the transaction from the target or targets.
If the mapping has a flat-file as the target, then the integration service can generate an output file for a new transaction each time. We can dynamically name the target flat files. Here is the example of creating flat files dynamically - Dynamic flat-file creation.
TCL COMMIT & ROLLBACK Commands
There are five in-built variables available in the transaction control transformation to handle the operation.
How to Create Transaction Control Transformation
Follows the following steps to create transaction control transformation, such as:
Step 1: Go to the mapping designer.
Step 2: Click on transformation in the toolbar, and click on the Create button.
Step 3: Select the transaction control transformation.
Step 4: Then, enter the name and click on the Create button.
Step 5: Now click on the Done button.
Step 6: We can drag the ports into the transaction control transformation, or we can create the ports manually in the ports tab.
Step 7: Go to the properties tab.
Step 8: And enter the transaction control expression in the Transaction Control Condition.
Configuring Transaction Control Transformation
Here are the following components which can be configuring in the transaction control transformation, such as:
Transaction Control Expression
We can enter the transaction control expression in the Transaction Control Condition option in the properties tab.
The transaction control expression uses the IIF function to check each row against the condition.
Here is the following syntax for the Transaction Control transformation expression, such as:
In the following example, we will commit data to the target when dept no =10, and this condition is found true.
Step 1: Create a mapping with EMP as a source and EMP_TARGET as the target.
Step 2: Create a new transformation using the transformation menu, then
Step 3: The transaction control transformation will be created, then click on the done button.
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.
Step 5: Double click on the transaction control transformation and then in the edit property window:
Step 6: In the expression editor enter the following expression:
Step 7: Click on the OK button in the previous window.
Now save the mapping and execute it after creating sessions and workflows. When the department number 10 is found in the data, then this mapping will commit the data to the target.