A Transaction Control transformation is a lively and connected transformation. It allows us to commit and rollback transactions supported a group of rows that undergo a Transaction Control transformation.

Commit and rollback operations are of serious importance because it guarantees the supply of data.

A transaction is that the set of rows bound by commit or rollback rows. we will define a transaction supported the varying number of input rows. we will also identify transactions supported a gaggle of rows ordered on a standard key, like employee ID or order entry date.

When processing a high volume of data, there are often a situation to commit the info to the target. If a commit is performed too quickly, then it’ll be an overhead to the system.

If a commit is performed too late, then within the case of failure, there are chances of losing the data. therefore, the Transaction control transformation provides flexibility.

In PowerCenter, the transaction control transformation is defined within the following levels, such as:

Within a mapping: Within a mapping, we use the Transaction Control transformation to work out a transaction. We define transactions using an expression during a Transaction Control transformation. we will prefer to commit, rollback, or continue on the idea of the return value of the expression with none transaction change.
Within a session: We configure a session for the user-defined commit. If the mixing Service fails to rework or write any row to the target, then we will prefer to commit or rollback a transaction.

When we run the session, then the mixing Service evaluates the expression for every row that enters the transformation. When it evaluates a committed row, then it commits all rows within the transaction to the target or targets. When the mixing Service evaluates a rollback row, then it rolls back all rows within the transaction from the target or targets.

If the mapping features a flat-file because the target, then the mixing service can generate an computer file for a replacement transaction whenever. we will dynamically name the target flat files. Here is that the example of making flat files dynamically – Dynamic flat-file creation.

TCL COMMIT & ROLLBACK Commands

There are five in-built variables available within the transaction control transformation to handle the operation.

TC_CONTINUE_TRANSACTION

The Integration Service doesn’t perform any transaction change for the row. this is often the default value of the expression.

TC_COMMIT_BEFORE

The Integration Service commits the transaction, begins a replacement transaction, and writes the present row to the target. the present row is within the new transaction.

In tc_commit_before, when this flag is found set, then a commit is performed before the processing of the present row.

TC_COMMIT_AFTER

The Integration Service writes the present row to the target, commits the transaction, and begins a replacement transaction. the present row is within the committed transaction.

In tc_commit_after, the present row is processed then a commit is performed.

TC_ROLLBACK_BEFORE

The Integration Service rolls back the present transaction, begins a replacement transaction, and writes the present row to the target. the present row is within the new transaction.

In tc_rollback_before, rollback is performed first, then data is processed to write down .

TC_ROLLBACK_AFTER

The Integration Service writes the present row to the target, rollback the transaction, and begins a replacement transaction. the present row is within the rolled-back transaction.

In tc_rollback_after data is processed, then the rollback is performed.

How to Create Transaction Control Transformation

Follows the subsequent steps to make transaction control transformation, such as:

Step 1: attend the mapping designer.

Step 2: Click on transformation within 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 will drag the ports into the transaction control transformation, or we will create the ports manually within the ports tab.

Step 7: attend the properties tab.

Step 8: And enter the transaction control expression within the Transaction control .

Configuring Transaction Control Transformation

Here are the subsequent components which may be configuring within the transaction control transformation, such as:

Transformation Tab: It can rename the transformation and add an outline .
Ports Tab: It can create input or output ports.
Properties Tab: It can define the transaction control expression and tracing level.
Metadata Extensions Tab: It can add metadata information.

Transaction Control Expression

We can enter the transaction control expression within the Transaction control option within the properties tab.

The transaction control expression uses the IIF function to see each row against the condition.

Syntax

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

IIF (condition, value1, value2)

For example:

IIF (dept_id=11, TC_COMMIT_BEFORE,TC_ROLLBACK_BEFORE)

Example

In the following example, we’ll 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 because the target.

Step 2: Create a replacement transformation using the transformation menu, then

Select a transaction control because the new transformation.
Enter transformation name tc_commit_dept10.
and click on on the create button.

Step 3: The transaction control transformation are going to 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 then within the edit property window:

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

Step 6: within the expression editor enter the subsequent expression:

“iif(deptno=10,tc_commit_before,tc_continue_transaction)”.
and click on on the OK button.
It means if deptno 10 is found, then commit transaction in target, else continue the present processing.

Step 7: Click on the OK button within the previous window.

Now save the mapping and execute it after creating sessions and workflows. When the department number 10 is found within the data, then this mapping will commit the info to the target.

So, this brings us to the end of blog. This Tecklearn ‘Transaction Control Transformation in Informatica’ blog helps you with commonly asked questions if you are looking out for a job in Informatica. If you wish to learn Informatica and build a career in Datawarehouse and ETL domain, then check out our interactive, Informatica Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/informatica-training-and-certification/

Informatica Training

About the Course

Tecklearn’s Informatica Training will help you master Data Integration concepts such as ETL and Data Mining using Informatica PowerCenter. It will also make you proficient in Advanced Transformations, Informatica Architecture, Data Migration, Performance Tuning, Installation & Configuration of Informatica PowerCenter. You will get trained in Workflow Informatica, data warehousing, Repository Management and other processes.

Why Should you take Informatica Training?

Informatica professionals earn up to $130,000 per year – Indeed.com
GE, eBay, PayPal, FedEx, EMC, Siemens, BNY Mellon & other top Fortune 500 companies use Informatica.
Key advantages of Informatica PowerCenter: Excellent GUI interfaces for Administration, ETL Design, Job Scheduling, Session monitoring, Debugging, etc.

What you will Learn in this Course?

Informatica PowerCenter 10 – An Overview

Informatica & Informatica Product Suite
Informatica PowerCenter as ETL Tool
Informatica PowerCenter Architecture
Component-based development techniques

Data Integration and Data Warehousing Fundamentals

Data Integration Concepts
Data Profile and Data Quality Management
ETL and ETL architecture
Brief on Data Warehousing

Informatica Installation and Configuration

Configuring the Informatica tool
How to install the Informatica operational administration activities and integration services

Informatica PowerCenter Transformations

Visualize PowerCenter Client Tools
Data Flow
Create and Execute Mapping
Transformations and their usage
Hands On

Informatica PowerCenter Tasks & Workflows

Informatica PowerCenter Workflow Manager
Reusability and Scheduling in Workflow Manager
Workflow Task and job handling
Flow within a Workflow
Components of Workflow Monitor

Advanced Transformations

Look Up Transformation
Active and Passive Transformation
Joiner Transformation
Types of Caches
Hands On

More Advanced Transformations – SQL (Pre-SQL and Post-SQL)

Load Types – Bulk, Normal
Reusable and Non-Reusable Sessions
Categories for Transformation
Various Types of Transformation – Filter, Expression, Update Strategy, Sorter, Router, XML, HTTP, Transaction Control

Various Types of Transformation – Rank, Union, Stored Procedure

Error Handling and Recovery in Informatica
High Availability and Failover in Informatica
Best Practices in Informatica
Debugger
Performance Tuning

Performance Tuning, Design Principles & Caches

Performance Tuning Methodology
Mapping design tips & tricks
Caching & Memory Optimization
Partition & Pushdown Optimization
Design Principles & Best Practices

Informatica PowerCenter Repository Management

Repository Manager tool (functionalities, create and delete, migrate components)
PowerCenter Repository Maintenance

Informatica Administration & Security

Features of PowerCenter 10
Overview of the PowerCenter Administration Console
Integration and repository service properties
Services in the Administration Console (services, handle locks)
Users and groups

Command Line Utilities

Infacmd, infasetup, pmcmd, pmrep
Automate tasks via command-line programs

More Advanced Transformations – XML

Java Transformation
HTTP Transformation

Got a question for us? Please mention it in the comments section and we will get back to you.

532