Before we learn about partitioning, note that partitioning is a high availability feature that you need to purchase separately from Informatica. If you enable the high availability feature, you can make use of the partitioning functionality.
By default, a mapping containing the source, target, and transformations has a single partition. A single partition means a single record can flow from the source to the target at a time. By adding multiple partitions, you logically divide the mapping into multiple sections, and each section can pass a record at a time. So, if you make three partitions in the mapping, three records can pass through the mapping, thus reducing your runtime by one-third. When you add a partition at any stage of the mapping, Integration Service adds partitions at other stages of the mapping. You need to make sure that you have sufficient memory space and system capacity to handle the processing of multiple records at a time.
If you have 1000 records to process, and you supposedly created four partitions, Integration Service will process four records at a time, and the total time required to process 1000 records will be reduced to a fourth.
To enable partitions, you need to set the partitioning properties in the session task.
To enable partitioning, you need to define the following attributes.
You can define the partition points in a pipeline. By default, Integration Service sets the partition at various transformations. You can define the partition at any stage in the mapping.
Based on your system capability, you can increase or decrease the partitions. When you add a partition at any stage of the pipeline, Integration Service adds the same number of partitions at other stages of the mapping. The number of partitions in a mapping should be equal to the number of database connections on the source and target side. When you create partitions, Integration Service processes the data concurrently. Suppose you created three partitions, then Integration Service reads three records from the source, passes three records to transformations, and concurrently loads three records to target.
Informatica supports multiple types of partitions to distribute the data. The partition type controls how you wish to divide the data among the partitions you created in the mapping. If you have the high availability feature available, you can define the type of partition at different stages of the mapping. You can define the type of partitioning in session properties. The different types of partitions are mentioned as follows:
- Pass-through: In the pass-through type, Integration Service does not distribute the data among partitions. The data in the particular partition stays in the partition after passing through the partition point.
- Round-robin: In round-robin partitioning, Integration Service distributes the data evenly among the partitions. This makes an equal amount of data to pass through each partition.
- Key range: In key range partitioning, Integration Service distributes the data on the basis of ports, sets, or defined ports. You also define the range of values for each port. When the source and target are partitioned by the key range, select this type of partitioning.
- Database partition: This type of partitioning is possible with the Oracle or DB2 database. When you select database partitioning, Integration Service reads the partitioning information from the nodes in the database.
- Hash auto-keys: In hash auto-key partitioning, Integration Service divides the data based on the partition key using the hash function. All the grouped and sorted ports in transformations are used as partition keys. This type of partition can be used in Rank, Sorter, and Aggregator transformations.
- Hash user keys: Similar to hash auto-keys, Integration Service in this portioning uses the hash function to partition the data. You need to manually define the number of ports for the partition key.