What is a dynamic session partition in Informatica?

Question

Is it possible to create dynamic sql operator in Informatica using SQL Transformation. For eg.

SELECT p.id 
FROM products p
WHERE p.weight ?operator? '30'
where ?operator? can have values: <, > , =

or even: in, not in

Answer 1

Dynamic Partitioning:

If the volume of data grows or you add more CPUs, session run time increases. In dynamic partitioning, Integration Service determines the number of partitions to create at run time based on factors such as source database partitions or the number of nodes in a grid.

Types of Dynamic Partitioning:

1. Based on Source Partitioning - Determines the number of partitions using database partition information. The number of partitions is the maximum of the number of partitions at the source.

2. Based on number of CPUs - Sets the number of partitions equal to the number of CPUs on the node that prepares the session. If the session is configured to run on a grid, dynamic partitioning sets the number of partitions equal to the number of CPUs on the node that prepares the session multiplied by the number of nodes in the grid.

3. Based on number of Nodes in Grid - Sets the partitions to the number of nodes in the grid running the session. If you configure this option for sessions that do not run on a grid, the session runs in one partition and logs a message in the session log.

4. Based on No. of Partitions - Sets the partitions to a number that you define in the Number of Partitions attribute. Use the $DynamicPartitionCount session parameter, or enter a number greater than 1.

Description:

Informatica PowerCenter provides enhanced dynamic pipeline partitioning capabilities that when appropriately configured, can increase pipeline parallelism, resulting in greater throughput and a significant performance improvement.

Benefits:

Enhance load performance, resulting in greater throughput and a significant performance improvement.

Note: - Dynamic partitioning can be used if the source qualifier not includes a SQL query or source filter.