Order By Clause or Explicit Sort Stage

abhilashnair · Post by **abhilashnair** » Thu Dec 07, 2006 3:18 am

1) I specified hash partition in the input of Sort Stage. There is check box item "Perform Sort" with two sub properties "Stable" and "Unique".
What are those?

2)What should I specify in Join Stage Partition tab. Currently it is Auto

kumar_s · Post by **kumar_s** » Thu Dec 07, 2006 3:34 am

Stable sort will try to maintain the position of the input data as it is within the same group. Performance point of view, this can be avioded unless requried otherwise. Unique will try to aviod the repeated keys.
Perform sort option will to inline sort before the sort stage. No need to check if you are using Explict sort stage.
Since the data has been partitioned on the previous sort stage by hash, you can select 'Same' as partition method in Join stage.

thebird · Post by **thebird** » Thu Dec 07, 2006 11:14 am

Bu default it is a Stable Sort that is performed. As kumar has mentioned set the partitioning to "Same" mode in the Join. If it is kept to Auto, DataStage will repartition the data (to HASH). If it is SAME then no repartition takes place, and the same sort order and the same partitioning mode is propagated down to the Join stage.

Aneesh

Nageshsunkoji · Post by **Nageshsunkoji** » Thu Dec 07, 2006 11:35 am

thebird wrote:Bu default it is a Stable Sort that is performed. As kumar has mentioned set the partitioning to "Same" mode in the Join. If it is kept to Auto, DataStage will repartition the data (to HASH). If it is SAME then no repartition takes place, and the same sort order and the same partitioning mode is propagated down to the Join stage.

Aneesh

I believe that abhilash is asking about the options in the Input/Partition tab and kumar has been explained the same. Stable sort is True in the Sort stage properties box by default. Its not in the Input/Partition tab. Here Mr Bird is confusing the abhilash by mentioning stable sort is 'True' by default.

In case of Input/Partition tab in any stage, stable and unique sort will come into picture when you changed the partition type from AUTO and that too, you have to select the option of Perform sort. Explained by kumar already.