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

Pulling a fixed number of records from a DB2 table


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

New User


Joined: 21 Jul 2007
Posts: 6
Location: phoenix

PostPosted: Fri Jan 12, 2024 9:05 am
Reply with quote

I have a table which has 6 million records. I can't extract all the records from the table due to a limitation. So I am planning to pull the records in 6 batches of 1 million records in one batch. What are the options fro me to do it?

1. Rownum(): I can do a rownum() for all the records and then select rownum<= 1000000 in first batch, rownum > 1000000 and rownum<= 2000000 in 2nd batch and so on. But will the rownum always give the same records i.e. can it happen that when I run for the first time with rownum<= 1000000, can the smae record come when I run rownum > 1000000 and rownum<= 2000000 as its a new query and the order of the records might change as its a new connection. i.e. Will i get the same 1000000 records from the table if I run rownum<= 1000000 multiple times or records might change?


2. Rown_number Order by or Rank Order by: I can do both of these options but will like to avoid these as the Order by Clause will have performace issues based on our requirements.

Any other options we can consider?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Jan 12, 2024 9:54 am
Reply with quote

How about order by keys and hard code that key > last one in next query for fetch next 1M?

What limitation ? You can extract them in batch .
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Sun Jan 14, 2024 1:52 am
Reply with quote

If you UNLOAD the data from an image copy data set, you can minimize the cost of the extract.
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 Load new table with Old unload - DB2 DB2 6
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts Generate random number from range of ... COBOL Programming 3
No new posts Multiple table unload using INZUTILB DB2 2
Search our Forums:

Back to Top