View previous topic :: View next topic
|
Author |
Message |
iwannaknow
New User
Joined: 30 Jan 2006 Posts: 10 Location: Chennai
|
|
|
|
Hi All,
Without using a cursor, how can I select first 10 rows in a table having millions of rows ? |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
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. |
|
Back to top |
|
|
iwannaknow
New User
Joined: 30 Jan 2006 Posts: 10 Location: Chennai
|
|
|
|
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. |
|
|
Back to top |
|
|
iwannaknow
New User
Joined: 30 Jan 2006 Posts: 10 Location: Chennai
|
|
|
|
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. |
|
|
Back to top |
|
|
ovreddy
Active User
Joined: 06 Dec 2004 Posts: 211 Location: Keane Inc., Minneapolis USA.
|
|
|
|
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 |
|
|
iwannaknow
New User
Joined: 30 Jan 2006 Posts: 10 Location: Chennai
|
|
|
|
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 |
|
|
iwannaknow
New User
Joined: 30 Jan 2006 Posts: 10 Location: Chennai
|
|
|
|
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 |
|
|
iwannaknow
New User
Joined: 30 Jan 2006 Posts: 10 Location: Chennai
|
|
|
|
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 |
|
|
|