Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Retrieve first 100 records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
nikipathak

New User


Joined: 16 Jul 2009
Posts: 3
Location: pune

PostPosted: Tue Jul 21, 2009 1:43 pm    Post subject: Retrieve first 100 records
Reply with quote

Hi ,

How i can retrieve first 100 records from a db2 to table.

regards,
Niki
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Tue Jul 21, 2009 1:46 pm    Post subject:
Reply with quote

COBOL, SPUFI, REXX

Please explain your question fully, and show us what problems you have encountered with your code.
Back to top
View user's profile Send private message
bipinpeter

Active User


Joined: 18 Jun 2007
Posts: 213
Location: Cochin/Kerala/India

PostPosted: Tue Jul 21, 2009 2:10 pm    Post subject:
Reply with quote

You can use
Code:
FETCH FIRST 100 ROWS ONLY
with the SQL
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jul 21, 2009 2:33 pm    Post subject:
Reply with quote

Quote:
How i can retrieve first 100 records from a db2 to table


you can not retreive records from a db2 table- you can only retrieve rows.

first 100 records Ordered or Unordered?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Jul 21, 2009 5:33 pm    Post subject:
Reply with quote

bipinpeter wrote:
You can use
Code:
FETCH FIRST 100 ROWS ONLY
with the SQL
Without ORDER BY it'll give you some garbage...
Back to top
View user's profile Send private message
bipinpeter

Active User


Joined: 18 Jun 2007
Posts: 213
Location: Cochin/Kerala/India

PostPosted: Wed Jul 22, 2009 10:45 am    Post subject:
Reply with quote

Hi Anuj,
Quote:
Without ORDER BY it'll give you some garbage...
.
What u mean by 'some garbage?[/quote]
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Jul 22, 2009 11:24 am    Post subject:
Reply with quote

Quote:
What u mean by 'some garbage?


DB2 does not store the rows in any predefined order. So without order by clause, it may fetch any 100 rows which does not make any sense.
The only fetch first statement thats meaningful without order by is
Code:
FETCH FIRST ROW ONLY
existence check.
Back to top
View user's profile Send private message
bipinpeter

Active User


Joined: 18 Jun 2007
Posts: 213
Location: Cochin/Kerala/India

PostPosted: Wed Jul 22, 2009 11:30 am    Post subject:
Reply with quote

Okay Bharath and Anuj..I got the point
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Jul 22, 2009 5:43 pm    Post subject:
Reply with quote

DB2 is a relational Database -- there is nothing like first, last or middle from DB2 per se. The ORDER BY clause is required to sort the results in the correct order. If it is removed from the query, the results will still contain the top ten, but they may be in no particular order.

As you will learn if you continue to work with databases, there is no such thing as "first n rows". You might retrieve some n rows, but there is nothing automatically "first" about them.

If you read the same sequential file 10 times, the first n records will always be the first n. If you read a table n times, the first n rows may or may not be the same ones each time...
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Jul 22, 2009 7:00 pm    Post subject:
Reply with quote

Here is an intrestinng article on The “Top Ten” Problem.
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 sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Addition of two records on 2 fields amar143 SYNCSORT 8 Tue Oct 03, 2017 11:14 am
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us