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

How can I select first 10 rows in a table thru SPUFI


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

New User


Joined: 30 Jan 2006
Posts: 10
Location: Chennai

PostPosted: Mon Jan 30, 2006 8:41 am
Reply with quote

Hi All,

Without using a cursor, how can I select first 10 rows in a table having millions of rows ?
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Mon Jan 30, 2006 9:08 am
Reply with quote

iwannaknow,

Me too, But somethings are to never know.

Try using
'FETCH FIRST 10 ROWS ONLY' as the lastline of your select. This will work in SPUFI and batch submits of SQL, but not in COBOL programs outside of CUSROR unless it's 'FETCH FIRST 1 ROW ONLY'

Let me know, cause i wanna know too. icon_wink.gif
Back to top
View user's profile Send private message
iwannaknow

New User


Joined: 30 Jan 2006
Posts: 10
Location: Chennai

PostPosted: Mon Jan 30, 2006 9:44 am
Reply with quote

Ya David,

I knew that but let me be clear on the point that is it permissible to code 'FETCH FIRST 10 ROWS ONLY' as the last line of tht SQL statements?

I mean to ask whether no errors will be there?


DavidatK wrote:
iwannaknow,

Me too, But somethings are to never know.

Try using
'FETCH FIRST 10 ROWS ONLY' as the lastline of your select. This will work in SPUFI and batch submits of SQL, but not in COBOL programs outside of CUSROR unless it's 'FETCH FIRST 1 ROW ONLY'

Let me know, cause i wanna know too. icon_wink.gif
Back to top
View user's profile Send private message
iwannaknow

New User


Joined: 30 Jan 2006
Posts: 10
Location: Chennai

PostPosted: Mon Jan 30, 2006 10:18 am
Reply with quote

Ya David,

I tried that but it's coming with SQLERR -104. Could you let me know about the possible solutions to this.

DavidatK wrote:
iwannaknow,

Me too, But somethings are to never know.

Try using
'FETCH FIRST 10 ROWS ONLY' as the lastline of your select. This will work in SPUFI and batch submits of SQL, but not in COBOL programs outside of CUSROR unless it's 'FETCH FIRST 1 ROW ONLY'

Let me know, cause i wanna know too. icon_wink.gif
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Mon Jan 30, 2006 5:02 pm
Reply with quote

Hi Satya,

The syntax is Okey. For example

SELECT * FROM USER_TAB FETCH FIRST 10 ROWS ONLY;

Will fetch first 10 rows from the table USER_TAB.

In your case you may be using DB2 older version (<7). Which will not support for FIRST N ROWS ONLY Option. So first check which DB2 version u r working in. To find the DB2 version you go to DB2 COMMANDS from DB2 main panel probabily by selecting option 7 from DB2 panel.
Then enter "-DISPLAY GROUP" in command line 1 and enter. It will show the version of the Db2 you are using. If it is 7 or higher then this option (FIRST N ROWS ONLY) will work.

Thanks,
Reddy.
Back to top
View user's profile Send private message
iwannaknow

New User


Joined: 30 Jan 2006
Posts: 10
Location: Chennai

PostPosted: Mon Jan 30, 2006 6:49 pm
Reply with quote

Thanks Reddy,

You r right it works absolutely fine in DB2V7(what I've the information only). But, even I've tried it with changing the CS to RR in Spufi and max rows per select I mentioned there 10. It's retriving the rows but not in proper order. I'll resume the talk with you with a screen shot of tht.



ovreddy wrote:
Hi Satya,

The syntax is Okey. For example

SELECT * FROM USER_TAB FETCH FIRST 10 ROWS ONLY;

Will fetch first 10 rows from the table USER_TAB.

In your case you may be using DB2 older version (<7). Which will not support for FIRST N ROWS ONLY Option. So first check which DB2 version u r working in. To find the DB2 version you go to DB2 COMMANDS from DB2 main panel probabily by selecting option 7 from DB2 panel.
Then enter "-DISPLAY GROUP" in command line 1 and enter. It will show the version of the Db2 you are using. If it is 7 or higher then this option (FIRST N ROWS ONLY) will work.

Thanks,
Reddy.
Back to top
View user's profile Send private message
iwannaknow

New User


Joined: 30 Jan 2006
Posts: 10
Location: Chennai

PostPosted: Mon Jan 30, 2006 7:17 pm
Reply with quote

Hi Reddy,

ya this command is now working fine. Just I've changed the isolation level to RR and MAX SELECT LINES to 10. and that simple query ( select * from user_tab;) but your syntax is absolutely okay for DB2V7 and above.



iwannaknow wrote:
Thanks Reddy,

You r right it works absolutely fine in DB2V7(what I've the information only). But, even I've tried it with changing the CS to RR in Spufi and max rows per select I mentioned there 10. It's retriving the rows but not in proper order. I'll resume the talk with you with a screen shot of tht.



ovreddy wrote:
Hi Satya,

The syntax is Okey. For example

SELECT * FROM USER_TAB FETCH FIRST 10 ROWS ONLY;

Will fetch first 10 rows from the table USER_TAB.

In your case you may be using DB2 older version (<7). Which will not support for FIRST N ROWS ONLY Option. So first check which DB2 version u r working in. To find the DB2 version you go to DB2 COMMANDS from DB2 main panel probabily by selecting option 7 from DB2 panel.
Then enter "-DISPLAY GROUP" in command line 1 and enter. It will show the version of the Db2 you are using. If it is 7 or higher then this option (FIRST N ROWS ONLY) will work.

Thanks,
Reddy.
Back to top
View user's profile Send private message
iwannaknow

New User


Joined: 30 Jan 2006
Posts: 10
Location: Chennai

PostPosted: Mon Jan 30, 2006 7:21 pm
Reply with quote

Thanks Reddy,

You r right it works absolutely fine in DB2V7(what I've the information only). But, even I've tried it with changing the CS to RR in Spufi and max rows per select I mentioned there 10. It's retriving the rows but not in proper order. I'll resume the talk with you with a screen shot of tht.

Please find the attachment for output.

ovreddy wrote:
Hi Satya,

The syntax is Okey. For example

SELECT * FROM USER_TAB FETCH FIRST 10 ROWS ONLY;

Will fetch first 10 rows from the table USER_TAB.

In your case you may be using DB2 older version (<7). Which will not support for FIRST N ROWS ONLY Option. So first check which DB2 version u r working in. To find the DB2 version you go to DB2 COMMANDS from DB2 main panel probabily by selecting option 7 from DB2 panel.
Then enter "-DISPLAY GROUP" in command line 1 and enter. It will show the version of the Db2 you are using. If it is 7 or higher then this option (FIRST N ROWS ONLY) will work.

Thanks,
Reddy.
[/quote]
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 DELETE SPUFI DB2 1
No new posts Load new table with Old unload - DB2 DB2 6
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Adding QMF and SPUFI to the ISPF menu DB2 20
Search our Forums:

Back to Top