Sorter transformation in Informatica is used to sort the data in an ascending or descending order based on single or multiple keys. This is the type an active transformation which sorts the data either in ascending order or descending order. The port which is participated in sor operation is defined as “key”. Sorter transformation is used for “eliminating duplicates” hence the sorted transformation is called as active transformation the sorter functions as “order by clause” in SQL.
A sample mapping showing Sorter transformation is displayed in the below screenshot.
In this mapping, we wish to sort the data based on the
DEPARTMENT_ID field. To achieve this, mark the key port for the
DEPARTMENT_ID columns in the Sorter transformation and select from the drop-down list what you wish to have as the Ascending or Descending sorting, as shown in the below screenshot
Informatica Sorter transformation properties
Sorter Cache Size: The Integration Service uses the Sorter Cache Size property to determine the maximum amount of memory it can allocate to perform the sort operation. The Integration Service passes all incoming data into the Sorter transformation before it performs the sort operation. You can configure a numeric value for the Sorter cache, or you can configure the Integration Service to determine the cache size at run time. If you configure the Integration Service to determine the cache size, you can also configure a maximum amount of memory for the Integration Service to allocate to the cache.
If the Integration Service runs a partitioned
session, it allocates the specified amount of Sorter cache memory
If it cannot allocate enough memory, the Integration Service fails the session.
Sorter cache size is set to 16,777,216 bytes by default.
Case Sensitive: When you enable the Case Sensitive property, the Integration Service sorts upper case characters higher than lower case characters
You must specify a work directory the Integration Service uses to create temporary
files while it sorts data. After the Integration Service sorts the data, it deletes the temporary files. By default, the Integration Service uses the value specified for the $PMTempDir process variable.
Distinct Output Rows: You can configure the Sorter transformation to treat output rows as distinct. You can configure the Sorter transformation to treat output rows as distinct.
Tracing Level: Configure the Sorter transformation tracing level to control the number and type of Sorter error and status messages the Integration Service writes to the session log.
Null Treated Low:
Enable this property if you want the Integration Service to treat null values as lower than any other value when it performs the sort operation
. Disable this option if you want the Integration Service to treat null values as higher than any other value.
Transaction Applies the transformation
logic to all rows in a transaction. Choose Transaction when a row of data depends on all rows in the same transaction but does not depend on rows in other transactions.
All Input. Applies the transformation logic on all incoming data. When you choose All Input, the PowerCenter drops incoming transaction boundaries. Choose All Input when a row of data depends on all rows in the source.
Read about Source Qualifier Transformations here
For in-depth understanding of Informatica, click on