Optimum method among ORDER BY / SORT

snehalpatel · New User Joined: 13 Sep 2007 Posts: 37 Location: India

Hi All

My requirement is to unload DB2 table data into sequential file and then use that file for further process.

Now there are two options for me to get sorted data into sequential file.

1. I can use ORDER BY clause during Select Itself.
2. I can Unload DB2 data without ORDER BY clause and in next step I can go for SORT card .

I want to know which Is Most Preferred/Optimum method. Does it depends on Volume of Data to be Handled.

dbzTHEdinosauer · Posted: Mon Oct 08, 2007 2:47 pm

it depends on which resources you want to use. DB2's or a SORT.
I would personally use SORT in an effort to relieve DB2 of processing requirements - thus leaving more DB2 resources to other jobs/tasks. The larger the set of items to be sorted the more resources required. SORT's main purpose is to sort.

CICS Guy · Posted: Mon Oct 08, 2007 2:51 pm

I'd guess that it depends, if the ORDER BY sequence is a existing index, uet it.

mahsug · Posted: Mon Oct 08, 2007 6:50 pm

Hi ,

I would recommend you to perform a unload and go for a SORT. This not only relieves the Db2 resources, but only will get executed thru a job more faster than Db2 resources.

If you have a product called 'Db2 High Performance Unload ' , pass the SQL and create a INDEX , such that your query qualifies for a index only access. To my personal knowledge, If you have Db2 High performance unload , then no other methods can be better for unloads than this.

Mahesh

TG Murphy · Posted: Tue Oct 09, 2007 8:02 pm

I agree with CICS Guy. It depends. DB2 may already have an index that maintains the data in the sort sequence you desire. If so, use an ORDER BY because DB2 will not have to sort the data.

If there is no index support do not use ORDER BY - use the external sort. Using the DB2 ORDER BY to sort this data is like delivering pizza using a Mercedes Benz - misuse of a valuable resource.

snehalpatel · New User Joined: 13 Sep 2007 Posts: 37 Location: India

Hi

Thanks All for Valuable Suggestion.

The conclusion is that suppose we have one Table say Test_Table

snehalpatel · New User Joined: 13 Sep 2007 Posts: 37 Location: India

stodolas · Posted: Thu Oct 11, 2007 5:12 pm

DB2 uses DFSORT internally. So I assume using the ORDER BY would invoke the sort utility anyway. You could do a sort straight out of DB2 as well if you don't want to use the order by. We use SYNCSORT to unload some tables in my shop.

agkshirsagar · Posted: Thu Oct 11, 2007 5:55 pm

stodolas · Posted: Thu Oct 11, 2007 6:22 pm

You mean using sort to unload or the fact that DFSORT is used by DB2 internally?

stodolas · Posted: Thu Oct 11, 2007 6:34 pm

See this pdf from IBM regarding DFSORT and SORTDATA parameter on the unload.
www.ibm.com/servers/storage/support/software/sort/mvs/tuning/pdf/srtmltun.pdf

Here is an unload we use in production:

agkshirsagar · Posted: Fri Oct 12, 2007 10:21 am

Thanks stodlas, that was really helpful.

mahsug · Posted: Fri Oct 12, 2007 7:14 pm

Hi ,

This is not Db2 Version dependent . I am not sure about platinum unload , but Db2 High Performance Unload is a seperate product from IBM Information Management family.