Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Optimum method among ORDER BY / SORT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Optimum method among ORDER BY / SORT
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    Post subject:
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: 2150
Location: At my coffee table

PostPosted: Mon Oct 08, 2007 2:51 pm    Post subject:
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    Post subject: Unload
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: 149
Location: Ottawa Canada

PostPosted: Tue Oct 09, 2007 8:02 pm    Post subject:
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    Post subject:
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    Post subject: Re: Unload
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    Post subject:
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: 686
Location: Earth

PostPosted: Thu Oct 11, 2007 5:55 pm    Post subject:
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    Post subject:
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    Post subject:
Reply with quote

See this pdf from IBM regarding DFSORT and SORTDATA parameter on the unload.
http://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: 686
Location: Earth

PostPosted: Fri Oct 12, 2007 10:21 am    Post subject:
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    Post subject: Re: Unload
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Innovations in Your Mainframe Sort Arun Raj PC Guides & IT News 4 Wed Mar 15, 2017 10:38 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts SORT requirement sjiraga COBOL Programming 6 Thu Mar 09, 2017 9:11 pm
No new posts SORT to append Month and YEAR in MMYY... tisamf DFSORT/ICETOOL 1 Wed Mar 08, 2017 4:46 pm
No new posts JCL sort Match and NoMatch rakeshsekar1987 SYNCSORT 2 Thu Feb 02, 2017 10:26 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us