Row Number or subset of select.

[Alex Kizub]

Is it possible to retrive only part of query result?

Like Oracle has, for example, rownum and it can be part of conditions:
select * from table where rownum < 10

Problem is that table is huge (billions records) and when user is
subborn enough and reach page #100,000 then application has to read and
skip these 100,000,000 records to obtaine next page.

Please, don't tell me that table can be changed, sorted and so on.
I'm talking about one particular request in one time. But I need rows
from N to N+10 from table with M rows.

Is it possible in DB2 (Version 7)?
Alex Kizub.

[Mark A]

"Alex Kizub" <ak****@yahoo.com> wrote in message
news:40***************@yahoo.com...

Is it possible to retrive only part of query result?

Like Oracle has, for example, rownum and it can be part of conditions:
select * from table where rownum < 10

Problem is that table is huge (billions records) and when user is
subborn enough and reach page #100,000 then application has to read and
skip these 100,000,000 records to obtaine next page.

Please, don't tell me that table can be changed, sorted and so on.
I'm talking about one particular request in one time. But I need rows
from N to N+10 from table with M rows.

Is it possible in DB2 (Version 7)?
Alex Kizub.

If you just need the first 10 rows, you can use the following on the end of
your select
FETCH FIRST 10 ROWS ONLY
See the SQL reference.

If you want some other range of rows (in the middle of the answer set), then
look at the OLAP functions with row_number () over

[Alex Kizub]

Mark A wrote:

"Alex Kizub" <ak****@yahoo.com> wrote in message
news:40***************@yahoo.com... If you just need the first 10 rows, you can use the following on the end of
your select
FETCH FIRST 10 ROWS ONLY
See the SQL reference.

If you want some other range of rows (in the middle of the answer set), then
look at the OLAP functions with row_number () over

Mark: Thanks a lot. I'm really happy! Before our DBAs told me that this
impossible :(
But I still have a little problem. It doesn't work:
For SQL
select a,b,c from t order by a
I have the result.
A little modification to
select row_number() over (order by a), a,b,c from t order by a
returns me this:
SQL0104N An unexpected token "(" was found following "". Expected tokens may
include: ", FROM INTO ". SQLSTATE=42601

and this
select a,b,c from t where row_number() over (order by a) < 10 order by a
returns this:

SQL0104N An unexpected token "OVER" was found following "". Expected tokens may
include: "< > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT ". SQLSTATE=42601

I played a bit during all these hours. Have no clue.
Can you help me again, please?

Alex.

[Blair Adamache]

What operating system are you on? This is definitely valid syntax for v7
on Linux/Windows/Unix (sample database):

select row_number() over (order by lastname), lastname,salary from
employee order by lastname

Alex Kizub wrote:

Mark A wrote:

"Alex Kizub" <ak****@yahoo.com> wrote in message
news:40***************@yahoo.com...

If you just need the first 10 rows, you can use the following on the end of
your select
FETCH FIRST 10 ROWS ONLY
See the SQL reference.

If you want some other range of rows (in the middle of the answer set), then
look at the OLAP functions with row_number () over

Mark: Thanks a lot. I'm really happy! Before our DBAs told me that this
impossible :(
But I still have a little problem. It doesn't work:
For SQL
select a,b,c from t order by a
I have the result.
A little modification to
select row_number() over (order by a), a,b,c from t order by a
returns me this:
SQL0104N An unexpected token "(" was found following "". Expected tokens may
include: ", FROM INTO ". SQLSTATE=42601

and this
select a,b,c from t where row_number() over (order by a) < 10 order by a
returns this:

SQL0104N An unexpected token "OVER" was found following "". Expected tokens may
include: "< > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT ". SQLSTATE=42601

I played a bit during all these hours. Have no clue.
Can you help me again, please?

Alex.

[Alex Kizub]

Of course it's valid sysnax because I took it from documentation. And it has only
this one :(
Actually this is JDBC on Windows XP which uses DB2 Connect Client (app driver) to
connect to
DB2 Connect Server on Linux on mainframe which connect to real DB2 on mainframe.
I don't know shorter way :) And that's only DV2 part of whole huge chain.

Good enough for regualar SQL. but very, very slow for billions of records.
That why I try, without success, this row_number function.

Alex Kizub.

Blair Adamache wrote:

What operating system are you on? This is definitely valid syntax for v7
on Linux/Windows/Unix (sample database):

select row_number() over (order by lastname), lastname,salary from
employee order by lastname

[Mark A]

"Alex Kizub" <ak****@yahoo.com> wrote in message
news:40***************@yahoo.com...

Of course it's valid sysnax because I took it from documentation. And it has only this one :(
Actually this is JDBC on Windows XP which uses DB2 Connect Client (app driver) to connect to
DB2 Connect Server on Linux on mainframe which connect to real DB2 on mainframe. I don't know shorter way :) And that's only DV2 part of whole huge chain.

Good enough for regualar SQL. but very, very slow for billions of records.
That why I try, without success, this row_number function.

Alex Kizub.

The row_number() over function will not be supported until V8 of DB2 for
z/OS (which replaces OS/390). The platform where you start from (XP) or the
DB2 Connect Server server (Linux) is irrelevant. You need an SQL manual for
DB2 mainframe.

[Alex Kizub]

Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<c0**********@hanover.torolab.ibm.com>...
Problem does persist on mainframe DB2 (V7).

What operating system are you on? This is definitely valid syntax for v7
on Linux/Windows/Unix (sample database):

select row_number() over (order by lastname), lastname,salary from
employee order by lastname

[Mark A]

> Problem does persist on mainframe DB2 (V7).

Not supported on that platform until version 8.