Transaction Control Transformation

Transaction Control is an Active and Connected transformation.

PowerCenter lets us to control commit and roll back transactions based on a set of rows that pass through a Transaction Control transformation. A transaction is the set of rows bound by commit or roll back rows. We can define a transaction based on a varying number of input rows. We might want to define transactions based on a group of rows ordered on a common key, such as employee ID or order entry date.

In PowerCenter, you define transaction control at the following levels:

Within a mapping. We use the Transaction Control transformation to define a transaction using an expression in a Transaction Control transformation. Based on the return value of the expression, we can choose to commit, roll back, or continue without any transaction changes.
Within a session. When we configure a session, we configure it for user-defined commit. We can choose to commit or roll back a transaction if the Integration Service fails to transform or write any row to the target.

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.

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.

Note: You can also use the transformation scope in other transformation properties to define transactions.

Transaction Control transformation to define conditions to commit and roll back transactions from transactional targets. Transactional targets include relational, XML, and dynamic MQSeries targets. Define these parameters in a transaction control expression on the Properties tab.

Properties Tab

On the Properties tab, you can configure the following properties:

Transaction control expression
Tracing level

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. Use the following syntax for the expression:

IIF (condition, value1, value2)

The expression contains values that represent actions the Integration Service performs based on the return value of 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. When the Integration Service issues a commit or roll back based on the return value of the expression, it begins a new transaction. 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, and writes the current row to the target. 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, and writes the current row to the target. 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.

If the transaction control expression evaluates to a value other than commit, roll back, or continue, the Integration Service fails the session.

Mapping Guidelines and Validation

Use the following rules and guidelines when you create a mapping with a Transaction Control transformation:

If the mapping includes an XML 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.
You must connect each target instance to a Transaction Control transformation.
You can connect multiple targets to a single Transaction Control transformation.
You can connect only one effective Transaction Control transformation to a target.
You cannot place a Transaction Control transformation in a pipeline branch that starts with a Sequence Generator transformation.
If you use a dynamic Lookup transformation and a Transaction Control transformation 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.
Either all targets or none of the targets in the mapping should be connected to an effective Transaction Control transformation.

Sample Mapping:

Here we are taking EMP table as source and generate a department wise file.

Like emp_10.txt, emp_20.txt....etc to do this we need one expression to build the logic and one transaction control transformation for generator individual output files department wise and their salaries+comm.

1. Connect and Open the folder if not already opened.

2. Select Tools --> Mapping Designer

3. Select Mappings --> Create

It will pop-up "Mapping Name". Enter the mapping name of your choice" and Click on 'OK'. Example: m_emp_dept_wise_files

4. Drag the Source and Target definitions into workspace if they are already exist. If not click here to know how to create or import Tabledefinitions.

5. Please follow the link to create expressiontransformation 'exp_get_dept_filenames'.

Select 'exp_get_dept_filenames' expression and right click on it to edit -->Go to Port tab:

a) Add a port 'SALARY' as 'SAL+COMM', un-check output port for 'SAL' and 'COMM' as show below:

b) Add a variable port 'v_FILE_NAME' to generate department wise file names, then click on open browser:

c) click on 'Validate' and 'OK'.

d) Add a variable port 'v_FILE_NAME_Flag' to compare the current records below to which file.

e) click on 'Validate' and 'OK'

f) Add a output port 'FILE_NAME_Flag' to store file name flags.

g) Click on 'Validate' and 'OK'.

h) Add a output port 'FILE_NAME' to send out each record file name with department number.

i) Click on 'Validate' and 'OK'.

j) Add a variable port 'v_Pre_FILE_NAME' to store previous file record file name.

k) Click on 'Validate' and 'OK'.

6. Select 'Transformation' from Menu --> Create

a) That will appear you 'Select the transformation type to create:'

b) Select 'Transaction Control' from drop down and 'Enter a new name for this transformation:' as "tct_deptno_files"

c) Click 'Create' and 'Done'

a) Click on Transaction Control Transformation icon marked below in below snapshot.

b) Click in the workspace in Mapping Designer.

c) Select TCTRANS in workspace and Right Click --> Edit.

d) In Transformation tab --> Click on 'Rename' highlighted above which will pop-up 'Rename Transformation'. Enter the Transformation Name: "tct_deptno_files"

e) Click on 'OK'

f) Click on 'Apply' and 'OK'.