IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Optimum method among ORDER BY / SORT


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Mon Oct 08, 2007 11:40 am
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Oct 08, 2007 2:47 pm
Reply with quote

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.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Mon Oct 08, 2007 2:51 pm
Reply with quote

I'd guess that it depends, if the ORDER BY sequence is a existing index, uet it.
Back to top
View user's profile Send private message
mahsug

New User


Joined: 01 Jul 2003
Posts: 15
Location: Phoenix , AZ

PostPosted: Mon Oct 08, 2007 6:50 pm
Reply with quote

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
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 148
Location: Ottawa Canada

PostPosted: Tue Oct 09, 2007 8:02 pm
Reply with quote

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.
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Thu Oct 11, 2007 2:08 pm
Reply with quote

Hi

Thanks All for Valuable Suggestion.

The conclusion is that suppose we have one Table say Test_Table

Code:
Test_Table
COL-1    COL-2       COL-3      COL-4      COL-5


And Say We already Have Index on COL-1, COL-2, COL-3 and we need our Unload file to be sorted on this 3 Col’s only then I should go for ORDER-BY..

Else Suppose I need my Unload File sorted on COL-1, COL-3, COL-4, COL-5
Then in this Case I need to go for Jcl Sort.

Please correct me If my interpretation is wrong.
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Thu Oct 11, 2007 2:16 pm
Reply with quote

mahsug wrote:
Hi ,

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.


Hi

Is this DB2 Version Dependent ? We use Platinum Unload .
How to know whether its 'Db2 High Performance Unload ' or Not
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Oct 11, 2007 5:12 pm
Reply with quote

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.
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Thu Oct 11, 2007 5:55 pm
Reply with quote

stodolas wrote:
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.

This sounds interesting. Could you please share your source of information? I would like to know more about it.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Oct 11, 2007 6:22 pm
Reply with quote

You mean using sort to unload or the fact that DFSORT is used by DB2 internally?
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Oct 11, 2007 6:34 pm
Reply with quote

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:
Code:

//STEP01  EXEC PGM=SORT,PARM='DB2=DB2R'                             
//STEPLIB  DD  DSN=DB2.PROD.SDSNLOAD,DISP=SHR                         
//SYSPRINT DD *                                                       
//SYSOUT   DD SYSOUT=*                                               
//*                                                                   
//SORTOUT  DD DSN=OUTFILE.DSN.NAME,                                 
//         DISP=(NEW,CATLG,DELETE),                                   
//         UNIT=WORK,SPACE=(CYL,(50,50),RLSE),                       
//         DCB=(LRECL=50,RECFM=FB,BLKSIZE=0,BUFNO=30)                 
//*                                                                   
//SORTDBIN DD *                                                       
    SELECT    EmpNo, FName, LName
      FROM     Owner.EmpTable
      WHERE  LName Like 'C%'                             
//*
//SYSIN DD *
  SORT FIELDS=(1,10,CH,A)
//*

//
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Fri Oct 12, 2007 10:21 am
Reply with quote

Thanks stodlas, that was really helpful. icon_smile.gif
Back to top
View user's profile Send private message
mahsug

New User


Joined: 01 Jul 2003
Posts: 15
Location: Phoenix , AZ

PostPosted: Fri Oct 12, 2007 7:14 pm
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Calling Java method from batch COBOL ... COBOL Programming 5
No new posts Rotate partition-logical & physic... DB2 0
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
Search our Forums:

Back to Top