Data Transformation Manager (DTM) process

The PowerCenter Integration Service process starts the Data Transformation Manager process to run a session. The DTM process is also known as the pmdtm process. Data Transformation Manager is the process associated with the session task.

Read the Session Information: The PowerCenter Integration Service process provides the DTM with session instance information when it starts the DTM. Data Transformation Manager retrieves the mapping and session metadata from the repository and validates it.

Perform Pushdown Optimisation: If the session is configured for pushdown optimisation, the DTM runs an SQL statement to push transformation logic to the source or target database.

Create Dynamic Partitions:  Data Transformation Manager adds partitions to the session if you configure the session for dynamic partitioning. The DTM scales the number of session partitions based on factors such as source database partitions or the number of nodes in a grid.

Form Partition Groups: 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.

Expand Variables and Parameters: 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.

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

Validate Code Pages: The PowerCenter Integration Service processes data internally using the UCS­2 character set. When you disable data code page validation, 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.

Verify Connection Object Permissions: 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.

Start Worker DTM Processes: 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.

Run Pre­Session Operations: After verifying connection object permissions, the DTM runs pre­-session shell commands. The DTM then runs pre­-session stored procedures and SQL commands.

Run the Processing Threads: After initialising 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.

Run Post­Session Operations: After Data Transformation Manager runs the processing threads, it runs post-session SQL commands and stored procedures. Then it runs post­-session shell commands.

Send Post­Session Email: 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.