Informatica Common component Source qualifier use sort port

When using a sorted port, PowerCenter adds the port to the ORDER BY clause in the default query. PowerCenter Server adds the configured port number, starting at the top of the source qualifier conversion. You can use a sorted port to improve performance when any of the following transformations are included in the map:

• Polymerization. When you configure an aggregation transformation for a sorted input, you can send sorted data using a sorted port. The group by port in the aggregation transformation must match the ordered port order in the Source qualifier transformation.

• Join. When you configure a join transformation for a sorted input, you can send sorted data using a sorted port. The order of the ordered ports is configured in the same way as the Source qualifier transformation.

Note: You can also use the Sequencer transformation to sort the relationship file and the flat file data before the aggregation and join transformations.

Only the sorted ports are used for the relational source. When you use a sorted port, the order of the source database must match the sort order that is configured for the session. PowerCenter creates an SQL query that extracts the source data, including the ORDER BY clause for the sorted port. The database server executes the query and passes the resulting data to PowerCenter. To ensure that data is sorted by PowerCenter requirements, the database sort order must be the same as the user-defined session sort order.

When you configure PowerCenter to validate the data code page, and when you run the workflow in Unicode data movement mode, PowerCenter uses the selected sort order to sort the character data.

The Source qualifier transformation includes the number of sorted ports in the default SQL query. However, if you modify the default query after selecting the number of sorted ports, PowerCenter will only use the query that is defined in the SQL query properties.

To use a sorted port:

• In Mapping Designer, open the Source qualifier transformation, and then click the Properties tab.

• Click number of sorted ports, and then enter the number of ports you want to sort.

PowerCenter Server adds the configured number of columns to the ORDER by clause, starting at the top of the source qualifier conversion.

The source database sort order must conform to the session sort order.

Tip: Sybase supports up to 16 columns in ORDER by. If your source is Sybase, do not rank more than 16 columns.

• Click OK.

Informatica Common component Source qualifier use sort port