Sorter Transformation

Chapter 20: Sorter transformation:

• Active and Connected. Active as when we use distinct option the number of rows might reduce.
  
  
• We can use sorter to sort data in ascending or descending order according to a specified sort key.
  
  
• You can sort data from relational or flat file sources. We can also use it to pass data to aggregator transformation configured for sorted input.
  
  
• The Sorter transformation contains only input/output ports.
  
  
• Sort key could be single or multiple columns. When multiple columns are used the order the ports appear in the Ports tab determines the
  succession of sort operations.

• Sorter transformation properties:

1. 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 for each partition.

If it cannot allocate enough memory, the Integration Service fails the session.

Sorter cache size is set to 16,777,216 bytes by default.

2. Case Sensitive: When you enable the Case Sensitive property, the Integration Service sorts uppercase characters higher than lowercase characters.
3. Work Directory: 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.

4. 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.

5. 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.

6. 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.

7. Transformation Scope:

♦ 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.