Transaction Control Transformation in Informatica Cloud (IICS)

1. What is a Transaction?

A Transaction is a set of rows grouped together based on a common key field such as Department ID of employees or transaction date of a product. The number of rows in each transaction can vary.

For example in an employee table there is data of 100 employees belonging to 12 different departments. If the department ID is used to group the records, then there will be 12 different sets of employee data. Each set is considered as a transaction and each set may have varying number of records.

2. What is a Transaction Control Transformation?

The Transaction Control transformation is an active and connected transformation that commits or rolls back transactions (sets of rows) during a mapping run.

The Transaction Control transformation identifies the transactions based on transaction control condition defined in the transformation. Based on whether the condition is met, you can choose to either commit/roll back rows or continue processing data without changing the transaction boundaries.

3. Where Transaction Control Transformation is useful?

• When you are processing large amounts of data you can use transaction control transformation to commit the data at regular intervals not simply based on the number of rows processed so far but also when a transaction changes to prevent the data loss.

• If the mapping has a flat file target that is created at run time, you can generate an output file each time Data Integration starts a new transaction. This helps in dynamic output files generation based on input transactions. The output files also can be dynamically named each time they are created based on any input field(s).

4. How to implement Transaction control transformation in a mapping?

We have an employee table with employees belonging to different departments. You want to generate a different target file for each department. This can be accomplished by below mapping.

4.1 Configuring Source Transformation

Connect to the oracle Employee table belonging to HR schema.

4.2 Configuring Sorter Transformation

Sort the records based on the Department ID.

Incoming data must be sorted by the fields that you use in the transaction condition

4.3 Configuring Transaction Control Transformation

Configure the transaction control condition on the Transaction Control tab. The transaction control condition tests each row from source to determine whether to commit rows, roll back rows, or continue without any transaction changes.

The below image shows the Transaction Control tab:

Transaction Control Condition

Under Transaction Control Condition multiple options are available to set the condition for transaction boundary

4.3.1 If Field Value Changes

Use an If Field Value Changes condition when you want to change the transaction boundary when a particular field value changes. In our example we want to change the transaction boundary when the department Id changes i.e when the Department ID is changed the rows from then are considered as a new transaction.

4.3.2 Advanced

Use Advanced Transaction Control condition when you want to change the transaction boundary on a particular condition using an expression editor.

Configure the expression in the If part of the condition. The expression can use fields, parameters, built-in functions, and user-defined functions.

4.3.3 Parameterized

Use Parameterized Transaction Control condition when you want expression parameter to represent the condition. Enter the parameter value in the Mapping configuration task or pass the value from a Parameter file.

4.3.4 Then and Else condition

Transaction control provides below conditions that can be specified in the Then and Else parts of the condition.

The Then and Else parts of the condition must contain different actions.

In our example we want to commit the transaction when a new Department ID comes. So the Then condition is selected as Commit Before.

If the Department ID is not changed we .want to continue with the transaction. So the ELSE condition is selected as Continue Transaction.

4.4 Configuring Target Transformation

Select the Use a Dynamic File Name condition to include the Department ID in the file name and generate a target output file dynamic file name.

5. How Transaction output varies for various built-in transaction types?

5.1 Commit Before

Let us check out the source data from the employees table

The total employees count is 106

SELECT count(*)  as employee_count FROM employees WHERE department_id IS NOT NULL

EMPLOYEE_COUNT
-----------------
106

Below is the department wise break down of employee count

SELECT department_id, 
       count(*) as employee_count 
FROM employees 
WHERE department_id IS NOT NULL 
GROUP BY department_id

Below is the output from the Transaction control mapping

As expected for every department an output flatfile is generated.

Let us change the target from Flat File to a relational table and check the results.

I have created a new table TEMP_EMPLOYEES and selected it as a target in my mapping

CREATE TABLE HR.TEMP_EMPLOYEES   (	
	EMPLOYEE_ID NUMBER(6,0), 
	FIRST_NAME VARCHAR2(20), 
	LAST_NAME VARCHAR2(25), 
	DEPARTMENT_ID NUMBER(4,0)  
 );

When the mapping is triggered and completed successfully below is the details from IICS Monitor. Only 104 records processed out of 106 records.

Let’s check the department wise count from the target table

SELECT department_id, count(*) as employee_count FROM TEMP_EMPLOYEES GROUP BY department_id

If you notice, the last department 110 details are missing.

What happened here? Why the final department details are missing in Oracle target table?

This is a good demonstration of Transaction Control commit points.

When the record with 110 is read by the Data Integration, it commits the current transaction which is of department 100 and starts a new transaction 110. But since there is no department after 110 the condition to commit the transaction is not met.

Hence the final department details are not committed into the Oracle table.

How can this be corrected?

This issue can be avoided by adding a Trailer record with dummy department information in the source.

Then how come the output Flat File for department 110 got created in the initial example?

Flat File transactions gets auto committed. Even if the transaction are rolled off the records gets committed and output files are created.

5.2 Commit After

Let us change the Then condition to Commit After and check the results.

This time 105 out of 106 records are processed. 1 record got processed extra compared to Commit Before.

Let’s check the department wise count from the target table

SELECT department_id, count(*) as employee_count FROM TEMP_EMPLOYEES GROUP BY department_id

So the first record from the department 110 is processed.

This is because in the Commit After Transaction Control method if the If condition is met, the current record is also included in the current transaction and then committed.

Hence first record of department 110 is committed along with department 100 transaction.

Rest of the records are not committed as the condition is not met like we discussed in Commit Before Case.

If the Flat File outputs are checked it’s totally messy. The first record of every department is included in previous department output file.

There are also chances to lose some data if the previous transaction has only 1 record and dynamic file is just based on fields like ID and Name.

The reports for departments 40 and 70 are missing because they have only 1 record each and they get committed in their previous department files. Similarly 1^st record of each department gets loaded in the previous department file.

This is nowhere recommended to use in this type of scenarios. This is just for demonstration purpose to show how Commit After works.

5.3 Rollback Before and Rollback After

Let us run first by changing the Then condition to Rollback Before and check the results for Oracle target.

Below is the IICS Monitor result.

None of the records are processed as all transactions are rolled back.

You will get same result when Then condition is changed to Rollback After and tested and the reason is same.

If you test the same with a Flat File target, IICS Monitor still shows 0 records processed to target. But the output flat files are created as expected as flat file transactions are auto committed.

The outputs of Rollback Before and Rollback After will be same as Commit Before and Commit After respectively for flat file targets.

6. What is the difference in the implementation of Transaction control in Powercenter vs Cloud?

In Powercenter if you want to implement the dynamic output file generation based on input groups, you need to indicate when a new group starts by defining the condition in an Expression transformation.

The example we discussed for employees can be implemented as below.

•  create a variable DEPT_ID = V_DEPT
•  create another variable before V_DEPT as V_FLAG = IIF(V_DEPT = DEPT_ID , ‘OLD’,NEW’)
•   create a output port after V_DEPT as O_FLAG = V_FLAG

The order would be as below. When ever a new department starts the O_FLAG is updated to NEW.

V_FLAG = IIF(DEPT_ID = V_DEPT , 'OLD',NEW')
V_DEPT = DEPT_ID
O_FLAG = V_FLAG

Now take all the ports to Transaction Control Transformation. In transformation properties tab open the expression editor and give condition as below. This means when O_FLAG is NEW commit all the transaction else continue the transaction.

IIF(O_FLAG='NEW',tc_commit_before,tc_continue_transaction)

Now if you compare with Powercenter, the Transaction Control implementation is very much simplified in IICS.

The field value change logic is given in the Transaction control transformation itself.

Also the built-in variables are now provided in drop down menu in the form of If and Then conditions.

If we see the dynamic output file generation process through Transaction control, it is just opposite of Indirect File loading.

Interesting !!?

In Indirect File loading we taking varying number of source file of same structure and load into a target.

In Transaction control we load data from a source into a varying number of target files based on input groups.