Informatica PowerCenter — Study Materials

Performance Tuning in Informatica

This section addresses systematic approaches to identifying and resolving performance bottlenecks in Informatica PowerCenter environments.

Data Transformation Manager (DTM)

When a session executes, the Integration Service initialises the DTM, which manages three critical threads:

• Reader thread — retrieves data from sources
• Transformation thread — processes data according to mapping logic
• Writer thread — connects to targets and loads data

Delays in any thread cascade throughout the session.

Bottleneck identification

Source bottlenecks. Slow source reads delay buffer filling, causing transformation and writer threads to idle. Test methodology: add a filter with FALSE conditions immediately after the Source Qualifier. Comparing execution times reveals whether the source is the constraint.

Target bottlenecks. When the writer thread cannot free buffer space quickly, reader and transformer threads stall. Substitute flat files for relational targets to isolate the issue — significant time reduction indicates a target bottleneck.

Mapping bottlenecks. Complex or inefficient mapping logic slows the transformation thread, creating upstream and downstream congestion.

Session bottlenecks occur with incorrect session memory configuration — resulting in small cache sizes, low buffer memory, or inadequate commit intervals.

System bottlenecks. Integration Service resource constraints affect transformation processing and cache file creation for aggregators, joiners, lookups, sorters, XML and ranking transformations.

Thread statistics analysis

Session logs provide runtime metrics:

• Run Time — total thread execution duration
• Idle Time — waiting periods for other threads
• Busy Time — calculated as (run time – idle time) / run time × 100
• Thread Work Time — processing percentage per transformation

Optimisation strategies

Source optimisation: use database optimiser hints for complex multi-table queries; increase network packet sizes for Oracle, Sybase ASE and Microsoft SQL Server.

Target optimisation: bulk loading bypasses database logs, improving speed but eliminating rollback capability; external loaders support Oracle, DB2, Sybase and Teradata; drop indexes and constraints before loading and rebuild afterward; increase network packet sizes.

Mapping optimisation: minimise transformation count and delete unnecessary links; eliminate redundant datatype conversions; factor aggregate function calls (SUM(COL_A + COL_B) outperforms SUM(COL_A) + SUM(COL_B)); replace repeated expressions with local variables using VARIABLE PORT; prioritise numeric over string operations; use operators instead of functions (|| beats CONCAT); disable unused output ports.

Transformation-specific tips

Lookup transformation:

• Enable caching for repeated lookups on identical tables
• Prioritise equality conditions in multi-condition scenarios
• Use SQL override to reduce cached data volume
• Index cached lookup tables on ORDER BY columns
• Replace large lookups with source-qualifier joins when feasible

Filter transformation: apply early in the data flow; use source-qualifier SQL override instead of filter transformations; consolidate multiple filters into routers.

Source Qualifier: retrieve only necessary columns; avoid ORDER BY clauses.

Aggregate transformation: group by simpler, preferably numeric columns; use sorted input to decrease cache requirements; apply early in workflow; filter before aggregating; limit ports to reduce cache volume.

Sequence Generator: create reusable instances across mappings; adjust cached-value property appropriately.

Joiner transformation: assign the smaller table as master; perform joins in source qualifier when database execution is faster; pass sorted data to reduce disk usage; use normal joins when possible.

Common problems and solutions

Problems: excessive transformations · unused port links · unnecessary datatype conversions · overloaded aggregators, rankers and lookups.

Solutions: single-pass reading for multiple mappings using the same source · optimise datatypes (use integers for comparisons) · implement early filtering with simple conditions · use sorted input for aggregators · increase cached values in reusable sequence generators · apply shared logic before data-stream branching.

Pushdown Optimization and Partitioning

Pushdown Optimization — overview

"Informatica Pushdown Optimization Option increases performance by providing the flexibility to push transformation processing to the most appropriate processing resource."

The feature converts transformation logic into SQL statements that execute directly on databases, minimising data movement between servers.

How it works

When a session runs with pushdown optimization enabled, the Integration Service examines the mapping and transformations to determine which logic can be pushed to the database. It then generates and sends SQL statements to the source or target database for data transformation.

Three types of Pushdown Optimization

1. Source-side pushdown optimization. The Integration Service generates SELECT statements based on transformation logic, pushes valid transformations to the database, then reads and continues processing results.

2. Target-side pushdown optimization. The service generates INSERT, DELETE or UPDATE statements. It processes transformation logic up to the pushable point, then executes the generated SQL against the target.

3. Full pushdown optimization. "The Integration Service pushes as much transformation logic as possible to both source and target databases." This requires source and target to be on the same database.

Session Partitioning — purpose

The partitioning option increases performance through parallel data processing by splitting large datasets into smaller subsets processed concurrently.

Five partition types

1. Database partitioning — queries database partition information and reads partitioned data from corresponding nodes.
2. Round-Robin Partitioning — distributes data evenly among all partitions when grouping is not required.
3. Hash Auto-Keys Partitioning — uses hash functions with all grouped / sorted ports as compound partition keys; useful before Rank, Sorter and Aggregator transformations.
4. Hash User-Keys Partitioning — uses hash functions based on user-defined partition keys.
5. Key Range Partitioning — specifies ports forming compound keys, distributing data by defined ranges.

Access partition settings via the Mapping → Partitions menu in sessions.

Informatica Version Differences

PowerCenter 8 vs PowerCenter 9

Key enhancements introduced in version 9:

• Developer and Analyst Tools — new client applications added to the platform.
• Advanced Lookup capabilities — lookups became active transformations capable of "returning Multiple Rows" for matching conditions.
• Session Log Management — users gained the ability to "limit the size of session logs for real-time sessions" by time or file size.
• Unified Administration — single console consolidated Data Quality, PowerCenter, PowerExchange and Data Services.
• Legacy System Support — enhanced connectivity to mainframe systems including IMS, DB2, VSAM and others.
• Web Services Integration — added support for open interfaces enabling BPM tool integration.
• Concurrent Workflow Execution — maintained capability to run identical workflows simultaneously.

PowerCenter 9 vs PowerCenter 10

Version 10 delivered substantial modernisation across several dimensions.

Platform features: redesigned UI emphasising modern, intuitive navigation; cloud platform integration (AWS, Microsoft Azure); big-data support (Hadoop, Spark, NoSQL); enhanced data quality with improved profiling and cleansing; real-time data streaming capabilities; performance and scalability improvements.

Client tool enhancements:

• Mapping Designer — drag-and-drop interface with new big-data connectors.
• Workflow Manager — pause / resume and real-time monitoring functionality.
• Repository Manager — bulk operations and import / export capabilities.
• Metadata Manager — new tool providing enterprise-wide metadata visibility and lineage tracking.
• Cloud Connector — streamlined cloud-integration configuration interface.

Update Target Table Without Update Strategy

Overview

This section explains how to update a target table in Informatica PowerCenter without using the Update Strategy transformation, which can improve session performance when dealing with large tables.

The problem

"A user might have come across an ETL scenario, where you need to update a huge table with few records and occasional inserts." Using a traditional approach with Lookup transformations and Update Strategy may cause performance degradation as lookup-table size increases.

Session configuration — Treating All Rows

During session setup, you can specify one operation for all rows via the "Treat Source Rows As" setting:

• Insert — all rows as inserts
• Delete — all rows as deletes
• Update — all rows as updates
• Data Driven — follow Update Strategy flags in each row

Individual row operations

You can also configure specific behaviour for each target row:

• Insert — add row to target table
• Delete — remove row from table
• Update as Update — modify existing rows
• Update as Insert — insert flagged rows
• Update else Insert — update if exists; otherwise insert
• Truncate Table — clear target before loading

Implementation strategy

Create a simple insert-only mapping without Lookup or Update Strategy transformations. Then configure the session to treat rows as "Update else Insert" at the target level, enabling both insert and update operations efficiently.