SORT STAGE BEFORE JOIN STAGE

G SHIVARANJANI · Post by **G SHIVARANJANI** » Mon Jul 16, 2007 11:55 pm

Hi,

Is it necessary to put a sort stage before join stage,

If i am not wrong Join stage does sorting as well.

please correct me if i am wrong

thanks,

Maveric · Post by **Maveric** » Tue Jul 17, 2007 12:16 am

It is better to use external Sort stage to sort both the left and right link data. It improves the performance especially if the data volume is high. The sorting performance is not good in Join stage.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Tue Jul 17, 2007 12:28 am

But it gives a warning of

STG_JOIN_6: When checking operator: User inserted sort "STG_SORT_5" does not fulfill the sort requirements of the downstream operator "APT_JoinSubOperator in STG_JOIN_6"

Maveric wrote:It is better to use external Sort stage to sort both the left and right link data. It improves the performance especially if the data volume is high. The sorting performance is not good in Join stage.

madhukar · Post by **madhukar** » Tue Jul 17, 2007 12:49 am

Check the sort keys and the join keys...it should be same

Maveric · Post by **Maveric** » Tue Jul 17, 2007 1:08 am

The sort and join keys should be the same and in the same order. If it still gives the warning, then try with APT_DISABLE_SORT environment variable. This might remove the warning. Might. Try it and let us know.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Tue Jul 17, 2007 1:24 am

The keys are same but not working its giving the same warning..

Whats "APT_DISABLE_SORT environment variable" how can i use this..

Maveric wrote:The sort and join keys should be the same and in the same order. If it still gives the warning, then try with APT_DISABLE_SORT environment variable. This might remove the warning. Might. Try it and let us know.

ag_ram · Post by **ag_ram** » Tue Jul 17, 2007 2:21 am

The input needs to be hash-partitioned and sorted for Join . Same partition can be maintained after doing the same.

Alternatively "Auto" partition does the same.
APT_NO_SORT_INSERTION : DataStage automatically inserts sort components based on this , can be included in Job to prevent automatic sort insertions.

prashkvrk · Post by **prashkvrk** » Tue Jul 17, 2007 3:14 am

to remove that warning, jus try this one.
In the stage prior to the external sort stage clear the partition. Probably the you would have given as default. Since the data is again repartioned you have to clear the partition.
I think this would remove the warning which you are getting !

Prasad