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
 

 

SYNCSORT to sort huge volume of data?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
vinuthna2004

New User


Joined: 30 Dec 2005
Posts: 6

PostPosted: Thu Oct 04, 2007 8:30 pm    Post subject: SYNCSORT to sort huge volume of data?
Reply with quote

We are using BMC UNLOAD utility to unload huge table having minimum 8 million records daily. We wanted to remove the ORDER BY clause from SELECT query, since it is consuming more DB2 resources. And instead we wanted to use SORT program to sort the unloaded file. How many temporary sort work files it can allocated max?

Also there is another table having 91 partitions, i.e i need to write a job that needs to unload 91*8 million records (having 7 primary keys). Then use SORT program to sort on these 7 primary keys . So will the temporary work files will be sufficient to SORT all the table (all 91 partitions)? If i need extra sort work files, then how to specify that? I wanted to know if there is a way to unload all partitions at a time (instead of few and running the job multiple times)? Since maintenance window is only 3 hrs.
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu Oct 04, 2007 8:38 pm    Post subject:
Reply with quote

Regarding SyncSort Technical issues, Alissa Margulies (SyncSort Mainframe Product Services) suggestions might come handy for you.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Thu Oct 04, 2007 9:03 pm    Post subject:
Reply with quote

Syncsort handles large volumes, check the manual....
If you need a manual a phone call or email from the company's email should do the trick...
Check out this link for more information....
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Oct 05, 2007 2:11 am    Post subject:
Reply with quote

Hello,

Typically, 8 million records is not such a large volume. What is the lrecl of the data to be sorted?
Back to top
View user's profile Send private message
vinuthna2004

New User


Joined: 30 Dec 2005
Posts: 6

PostPosted: Fri Oct 05, 2007 7:41 am    Post subject:
Reply with quote

LRECL is 209.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Oct 05, 2007 8:39 am    Post subject:
Reply with quote

Hello,

My apologies (i missed the 8 million times 91) icon_redface.gif

That would make each file size around 1.7 billion chars. You will get over 55,000 bytes per track, so that would be just over 30,000 tracks which is a bit over 2000 cylendars. There may be a difference due to dasd model used.

Will you be able to do some timing tests? How long does it take to perform the unload and sort for 1 partition?

If you talk with your storage management people, they can advise on whether this would need special scheduling or there would typically be sufficient transient space available.
Back to top
View user's profile Send private message
vinuthna2004

New User


Joined: 30 Dec 2005
Posts: 6

PostPosted: Wed Oct 10, 2007 10:04 pm    Post subject:
Reply with quote

Thanks for your valuable suggestion. It took 10 minutes to do this for each partition when we did volumn testing. Total of 90 minutes it took to unload, sort and load all the partitions.

We are planning to make changes to online program to handle unavailability of DB2 tables during non-maintenance time only for particular day. Since we have 5 tables to do the same process in production.

It was awesome, when we dropped the NPI and replaced NPI with these new tables. With NPI the LOAD and UNLOAD took 9 hrs to complete the task. We dropped the NPI and replaced them with new tables. When we replaced NPI with new tables (having primary keys as NPI fields in this new tables), it took 10 min to complete the same task.

As per your suggestion D.Sch, I need to follow up with storage group, to unload all existing data from 91 partitions and load into new tables having 91 partitions (that replaced NPI). If that is not possible, then atleast they can provide me an estimate of how many records i can sort in one run.

Thanks a lot!
Vinu
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Oct 10, 2007 11:01 pm    Post subject:
Reply with quote

You're welcome - good luck icon_smile.gif
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts copying data without knowing location arunsoods DFSORT/ICETOOL 6 Thu Jul 20, 2017 1:03 pm
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts SORT to unpack a feild shr_amar DFSORT/ICETOOL 7 Fri Jul 07, 2017 4:32 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am


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