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:

• Within a mapping: Within a mapping, we use the Transaction Control transformation to determine a transaction. We define transactions using an expression in a Transaction Control transformation. We can choose to commit, rollback, or continue on the basis of the return value of the expression without any transaction change.
• Within a session: We configure a session for the user-defined commit. If the Integration Service fails to transform or write any row to the target, then We can choose to commit or rollback a transaction.

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.

1. TC_CONTINUE_TRANSACTION
   The Integration Service does not perform any transaction change for the row. This is the default value of the expression.
2. TC_COMMIT_BEFORE
   The Integration Service commits the transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.
   In tc_commit_before, when this flag is found set, then a commit is performed before the processing of the current row.
3. 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.
   In tc_commit_after, the current row is processed then a commit is performed.
4. TC_ROLLBACK_BEFORE
   The Integration Service rolls back the current transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.
   In tc_rollback_before, rollback is performed first, and then data is processed to write.
5. TC_ROLLBACK_AFTER
   The Integration Service writes the current row to the target, rollback the transaction, and begins a new transaction. The current row is in the rolled-back transaction.
   In tc_rollback_after data is processed, then the rollback is performed.

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:

1. Transformation Tab: It can rename the transformation and add a description.
2. Ports Tab: It can create input or output ports.
3. Properties Tab: It can define the transaction control expression and tracing level.
4. Metadata Extensions Tab: It can add metadata information.

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.

Syntax

Here is the following syntax for the Transaction Control transformation expression, such as:

For example:

Example

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

1. Select a transaction control as the new transformation.
2. Enter transformation name tc_commit_dept10.
3. And click on the create button.

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:

1. Select the property tab.
2. Click on the transaction control editor icon.

Step 6: In the expression editor enter the following expression:

1. "iif(deptno=10,tc_commit_before,tc_continue_transaction)".
2. And click on the OK button.
3. It means if deptno 10 is found, then commit transaction in target, else continue the current processing.

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.