View previous topic :: View next topic
|
Author |
Message |
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. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
I'd guess that it depends, if the ORDER BY sequence is a existing index, uet it. |
|
Back to top |
|
|
mahsug
New User
Joined: 01 Jul 2003 Posts: 15 Location: Phoenix , AZ
|
|
|
|
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 |
|
|
TG Murphy
Active User
Joined: 23 Mar 2007 Posts: 148 Location: Ottawa Canada
|
|
|
|
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 |
|
|
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
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 |
|
|
snehalpatel
New User
Joined: 13 Sep 2007 Posts: 37 Location: India
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
|
|
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 |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
|
|
You mean using sort to unload or the fact that DFSORT is used by DB2 internally? |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
|
|
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 |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
Thanks stodlas, that was really helpful. |
|
Back to top |
|
|
mahsug
New User
Joined: 01 Jul 2003 Posts: 15 Location: Phoenix , AZ
|
|
|
|
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 |
|
|
|