Sorter Transformation

Use the Sorter transformation to sort data in ascending or descending order according to specified sort criteria. You can configure a Sorter transformation to achieve case-sensitive sorting and unique output. Sorter transformation is a passive transformation. You can use the Sorter transform to improve the performance of other transforms. For example, you can sort data that passes through a Lookup or Aggregator transformation in Informatica PowerCenter that is configured to use sorted inbound fields. When you create a Sorter transformation, you specify fields as sort criteria and configure each sort field to sort in ascending or descending order. You can use parameters in sort criteria and define values ​​for the parameters when configuring a mapping task.

Sort conditions

Set sort conditions to specify the sort field and sort order. Mapping tasks use sort criteria to sort data. Sort field is one or more fields used as sort criteria. Configure the sort order to sort data in ascending or descending order. In advanced mode, you can use advanced session properties to override the sort order when you schedule a mapping task. If you specify multiple sort conditions, the mapping task sorts each condition in turn. The mapping task treats each subsequent sort condition as a second sort of the previous sort condition. You can set the order of sort conditions. If you use sort criteria parameters, you define the sort field and sort order when you run the mapping or configure the mapping task.

Sorter caches

The mapping task passes all incoming data to the Sorter transformation before performing the sort operation. The mapping task uses cache memory to process the sorter transformation. If the allocation task cannot allocate enough memory, the allocation will fail. By default, the allocation task determines the cache size at runtime. Before the sorting process begins, the allocation task allocates the amount of memory configured for the sorter cache size. Configure the Sorter cache size to a value smaller than the amount of physical RAM available on the computer hosting the Secure Agent. To sort data using the Sorter transformation, allocate at least 16 MB (16,777,216 bytes) of physical memory. When allocating memory for the sorter cache, consider the amount of data for other transformations and mapping tasks in the mapping. If the amount of incoming data is larger than the size of the sorter cache, the mapping task temporarily stores the data in the working directory. If you save data to the Sorter transformation working directory, the mapping task requires at least twice the amount of disk space as the amount of incoming data. When you set the trace level to Standard, the mapping task writes the amount of memory used by the sort transformation to the session log.

Case Sensitive

The case-sensitive property determines whether the Integration Service distinguishes between uppercase and lowercase letters when it sorts data. When you enable the Case Sensitive property, the Integration Service sorts uppercase letters higher than lowercase letters.

Distinct Output Rows:

we can configure the Sorter transformation to treat output rows as distinct. When we configure the Sorter transformation for different output rows, Mapping Designer configures all ports as part of the sort key in informatica PowerCenter.

When PowerCenter Server runs a session, it discards duplicate rows that were compared during the sorting process.

Tracing Level:

Configure the sorter conversion trace level to control the number and type of sorter error and status messages that the integration service writes to the session log.

At normal tracing level, PowerCenter Server writes the size of the rows passed to the Sorter transformation and the amount of memory that the Sorter transformation allocates for sort operations.

PowerCenter Server also writes the time and date when it passes the first and last input rows to the Sorter transformation. When you set the tracing level for a sorter transformation to verbose, the PowerCenter server writes the time when the sorter transformation completes and passes all the data to the next transformation in the pipeline.

PowerCenter Server also writes times to the session log when the Sorter transformation frees memory resources and deletes temporary files from the working directory.

Null Treated Low:

we can configure how the Sorter transformation handles NULL values. Enable this property if you want the Integration Service to treat null values ​​as lower than all other values ​​when it performs sorting. Disable this option if you want the Integration Service to treat null values ​​as higher than all other values.

Transformation Scope:

Transactions. Applies transformation logic to all rows within a transaction. Select Transactional if the data row depends on all rows in the same transaction, but not on rows in other transactions.

All inputs. Applies transformation logic to all incoming data. When you select All Input, PowerCenter clears the boundaries of incoming transactions. Select All Inputs if the data row depends on all rows in the source.

Note: To improve performance, use the Sort transformation to sort the data before the Aggregator and Joiner transformations.

For More details about Informatima PowerCenter Click on Informatica PowerCenter Training.