Data Transformation Manager (DTM) process in Informatica

Data Transformation Manager (DTM) Process in Informatica PowerCenter

The Data Transformation Manager (DTM) process is the Informatica PowerCenter process that runs a session. When the PowerCenter Integration Service starts a session, it creates a DTM process, also known as the pmdtm process, to read session metadata, initialize resources, run session threads, write data to targets, and complete post-session activities.

In simple terms, the Integration Service coordinates the workflow, while the DTM process does the session-level execution work. It prepares the mapping, expands parameters, checks connections, creates the session log, starts reader, transformation, and writer threads, and reports the final session status.

Where the DTM process fits in an Informatica session run

A session task in PowerCenter does not directly move data by itself. The Integration Service starts the DTM for that session, and the DTM uses the session configuration and mapping metadata to perform the extraction, transformation, and loading work.

Workflow: Contains tasks such as sessions, commands, decisions, and emails.
Session task: Defines how a mapping should run, including connections, parameters, partitions, and properties.
Integration Service: Starts and manages workflow and session execution.
DTM process: Executes the session logic and manages the session threads.

Main responsibilities of the Data Transformation Manager process

The DTM process performs several operations before, during, and after the actual data movement. These responsibilities explain why DTM messages are important when you troubleshoot Informatica session failures.

Read session information and validate mapping metadata

The PowerCenter Integration Service process provides the DTM with session instance information when it starts the DTM. The Data Transformation Manager retrieves mapping and session metadata from the repository and validates the session before data processing begins.

This stage helps confirm that the session has usable mapping logic, valid session properties, and the required metadata to continue execution.

Perform pushdown optimization for eligible session logic

If the session is configured for pushdown optimization, the DTM can run SQL statements that push eligible transformation logic to the source or target database. In this case, the database performs part of the transformation work instead of the Integration Service handling all logic in the pipeline.

Pushdown optimization depends on the mapping logic, database support, session configuration, and connection details. If the logic cannot be pushed down, the DTM continues with normal session processing for that part of the pipeline.

Create dynamic partitions for partitioned Informatica sessions

Data Transformation Manager adds partitions to the session if you configure the session for dynamic partitioning. The DTM can scale the number of session partitions based on factors such as source database partitions or the number of available nodes in a grid.

Partitioning allows the session pipeline to process data in parallel. The number of partitions affects how many reader, transformation, and writer thread sets the DTM needs to manage during the session run.

Form partition groups when sessions run on an Informatica grid

If you run a session on a grid, the DTM forms partition groups. A partition group is a group of reader, writer, and transformation threads that runs in a single DTM process. The DTM process forms partition groups and distributes them to worker DTM processes running on nodes in the grid.

This is useful in grid execution because the work can be divided across available nodes. In a non-grid session, the DTM normally runs the required session threads in the DTM process started for that session.

Expand session parameters, mapping parameters, and variables

If the workflow uses a parameter file, the PowerCenter Integration Service process sends the parameter file to the Data Transformation Manager when it starts the DTM. The DTM creates and expands session-level, service-level, and mapping-level variables and parameters.

For example, a parameter file may provide source file paths, database connection values, target table names, date filters, or environment-specific values. If parameter values are missing or incorrectly defined, the session log usually shows the related DTM initialization error.

Create the Informatica session log for DTM execution

Data Transformation Manager creates logs for the session. The session log contains a history of the session run, including initialization, transformation, status, and error messages. You can use information in the session log along with the PowerCenter Integration Service log and the workflow log to troubleshoot system or session problems.

A typical DTM-related troubleshooting check is to open the session log and identify where the session failed: metadata validation, connection permission check, pre-session command, reader thread, transformation thread, writer thread, post-session command, or email notification.

Session initialization started
DTM process started for session
Reading session parameters
Starting reader, transformation, and writer threads
Writer finished loading target rows
Session completed successfully

Validate code pages before session data processing

The PowerCenter Integration Service processes data internally using the UCS-2 character set. When data code page validation is disabled, the PowerCenter Integration Service verifies that the source query, target query, lookup database query, and stored procedure call text convert from the source, target, lookup, or stored procedure data code page to the UCS-2 character set without loss of data in conversion.

If the PowerCenter Integration Service encounters an error when converting data, it writes an error message to the session log. Code page issues are common when source and target systems use different character sets or when data contains special characters that cannot be converted safely.

Verify connection object permissions used by the session

After validating the session code pages, the DTM verifies permissions for connection objects used in the session. Data Transformation Manager verifies that the user who started or scheduled the workflow has execute permissions for connection objects associated with the session.

If the session user does not have required permissions, the session can fail before reader or writer threads begin. In that case, the session log should be checked for connection object names and permission-related messages.

Start worker DTM processes for grid sessions

The DTM sends a request to the PowerCenter Integration Service process to start worker DTM processes on other nodes when the session is configured to run on a grid.

Worker DTM processes help execute assigned partition groups on grid nodes. If a grid node is unavailable or a worker process cannot start, the session log and Integration Service log should be reviewed together.

Run pre-session commands, SQL, and stored procedures

After verifying connection object permissions, the DTM runs pre-session shell commands. The DTM then runs pre-session stored procedures and SQL commands.

Pre-session operations are usually used to prepare files, truncate staging tables, create audit records, or validate environment conditions before the actual mapping pipeline starts.

Run reader, transformation, and writer threads in the DTM pipeline

After initializing the session, the DTM uses reader, transformation, and writer threads to extract, transform, and load data. The number of threads the DTM uses to run the session depends on the number of partitions configured for the session.

DTM thread type	Role in the Informatica session	Common issues to check
Reader thread	Reads data from relational sources, files, or other configured sources.	Source query errors, file path errors, source connection failures, missing permissions.
Transformation thread	Applies mapping transformation logic between source and target.	Expression errors, lookup failures, rejected rows, data conversion issues.
Writer thread	Writes processed data to target tables, files, or other configured targets.	Target constraint errors, duplicate keys, rejected rows, commit failures, target permissions.

These threads form the core of DTM processing. When a session fails after initialization, the session log usually shows whether the failure came from the reader side, transformation logic, or the writer side.

Run post-session SQL, stored procedures, shell commands, and email

After Data Transformation Manager runs the processing threads, it runs post-session SQL commands and stored procedures. Then it runs post-session shell commands.

When the session finishes, the DTM composes and sends email that reports session completion or failure. If the Data Transformation Manager terminates abnormally, the PowerCenter Integration Service process sends post-session email.

DTM buffer size and DTM buffer block size in Informatica sessions

The DTM uses memory buffers while moving data through the session pipeline. Two commonly discussed session properties are DTM buffer size and DTM buffer block size. These settings affect how much memory is available for the DTM buffer and how the buffer is divided into blocks for data movement.

These values should not be changed randomly. If a session fails with buffer allocation errors, runs slowly because of very large rows, or processes heavy partitions, review the session log and environment memory before changing DTM buffer settings. Increasing buffer values without checking available memory can create pressure on the Integration Service node.

DTM process lifecycle in Informatica PowerCenter

The following order summarizes the normal DTM lifecycle during a session run:

The workflow reaches a session task.
The PowerCenter Integration Service starts the DTM process for the session.
The DTM reads session information and retrieves mapping metadata.
The DTM expands parameters and variables.
The DTM creates the session log.
The DTM validates code pages and verifies connection object permissions.
The DTM runs pre-session commands, SQL, and stored procedures.
The DTM starts reader, transformation, and writer threads.
The DTM runs post-session SQL, stored procedures, and commands.
The DTM sends post-session email or reports status back to the Integration Service.

How to troubleshoot DTM process failures in Informatica

When a session fails with a DTM-related message, avoid looking only at the final error line. The useful information is usually earlier in the session log, close to the first warning or first fatal error. Use the session phase to narrow down the cause.

Failure point in DTM execution	What to inspect first
DTM does not start	Integration Service status, node availability, repository connectivity, session configuration.
Fails while reading metadata	Mapping validity, session properties, reusable transformation metadata, repository access.
Fails while expanding parameters	Parameter file path, parameter names, workflow/session scope, missing values.
Fails during connection check	Connection object permissions, database credentials, source or target availability.
Fails during reader thread	Source query, source file, source connection, source code page.
Fails during writer thread	Target table, constraints, target permissions, commit interval, rejected rows.
Fails after data load	Post-session SQL, stored procedures, shell commands, email configuration.

QA checklist for reviewing an Informatica DTM tutorial

Does the article clearly explain that the Integration Service starts the DTM process for a session?
Does it mention that the DTM process is also known as the pmdtm process?
Does it separate DTM initialization tasks from reader, transformation, and writer thread execution?
Does it explain where parameters, variables, session logs, connection permissions, and code pages fit in the DTM lifecycle?
Does it avoid confusing the DTM process with the Repository Service, Designer, Workflow Manager, or Integration Service?
Does it give practical troubleshooting guidance based on the session log rather than only listing DTM tasks?

FAQs on Data Transformation Manager in Informatica

What is DTM in Informatica?

DTM in Informatica means Data Transformation Manager. It is the process started by the PowerCenter Integration Service to run a session. The DTM reads session information, initializes resources, starts processing threads, creates the session log, and performs pre-session and post-session operations.

Is the DTM process the same as the Integration Service?

No. The Integration Service manages workflow and session execution at the service level. The DTM process is started for a session and performs the session-level work, including data extraction, transformation, loading, logging, and session operations.

What are the main DTM threads in Informatica PowerCenter?

The main DTM processing threads are reader threads, transformation threads, and writer threads. Reader threads read source data, transformation threads apply mapping logic, and writer threads load data into targets. Partitioned sessions can have multiple sets of these threads.

What is the use of DTM buffer size in Informatica?

DTM buffer size controls the memory available for the DTM buffer during session data movement. It is relevant when sessions process large rows, many partitions, or heavy data volumes. Buffer settings should be changed only after reviewing session logs, row size, partitioning, and available system memory.

Where can I find DTM errors for a failed Informatica session?

DTM errors are usually found in the session log. For complete troubleshooting, compare the session log with the workflow log and the Integration Service log. The first serious error in the session log is often more useful than the final failure message.

TutorialKart.com

Data Transformation Manager (DTM) process in Informatica