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
 
How can I select first 10 rows in a table thru SPUFI

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: How can I select first 10 rows in a table thru SPUFI
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    Post subject: Re: How can I select first 10 rows in a table thru SPUFI
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    Post subject: Re: How can I select first 10 rows in a table thru SPUFI
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    Post subject: Re: How can I select first 10 rows in a table thru SPUFI
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    Post subject:
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    Post subject:
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    Post subject: Reddy, also this command is working...
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    Post subject: Attached output...
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    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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm

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