select * from table AND navigation

[TP]

Here is my problem.

I need to display a table about which I have no information except the
table name. Using metadata I can somehow show the column names and
record values.

But my table has 1 million rows and if I do a select * then I do get
1 million rows.

I want to be able to provide page navigation as google does, page
forward, backwards etc. For now it is a read only database.

For reference please see my post here.

http://forum.java.sun.com/thread.jsp?thread=482269&forum=48&message=2252212

I would appreciate any ideas. I have been stuck for a week now.

Thanks.

TP

[Blair Adamache]

Re: select * from table AND navigation

select count(*) will tell you how many rows it has. DESCRIBE TABLE
table_name will give you info on columns and data types.

TP wrote:
[color=blue]
> Here is my problem.
>
> I need to display a table about which I have no information except the
> table name. Using metadata I can somehow show the column names and
> record values.
>
> But my table has 1 million rows and if I do a select * then I do get
> 1 million rows.
>
> I want to be able to provide page navigation as google does, page
> forward, backwards etc. For now it is a read only database.
>
> For reference please see my post here.
> http://forum.java.sun.com/thread.jsp...essage=2252212
>
> I would appreciate any ideas. I have been stuck for a week now.
>
> Thanks.
>
> TP[/color]

[AK]

Re: select * from table AND navigation

Let us consider a very common situation. A GUI application issues a
query, then the first 25 rows from the result set are diplayed on
screen. If the user presses Page Down, then the next 25 rows will be
displayed, and so on. It is well known that FETCH FIRST 25 ROWS and
OPTIMIZE FOR 25 ROWS clauses of SELECT statement are very useful in
this situation. In fact, using either of them is a straightforward way
to tell the optimizer what you actually need. The optimizer, in its
turn, might choose an entirely different access plan to satisfy a
query with such a clause. For example, the query

SELECT * FROM CUSTOMER ORDER BY LAST_NAME

may be satisfied by a tablespace scan, followed by a sort. As a
result, the initial response time may be quite high. However, this is
the fastest way to retrieve the whole result set. Adding an OPTIMIZE
FOR, as well as FETCH FIRST, clause may cause the optimizer to choose
an index access plan, so that the first 25 rows are returned as soon
as possible. The immediate response time usually comes at a price:
should we ever need to retrieve all the rows, the overall execution
time would be high. It could be significantly higher than the
execution time of a tablespace scan.

You may also have come across the advice to use an OLAP function
ROW_NUMBER() to limit the output to 25 rows at a time. This advice is
more controversial. Let us discuss it in more detail and see its
potential drawbacks. If you use Visual Explain to compare execution
plans of these two statements:

SELECT ROW_NUMBER() OVER() AS N, S.* FROM SALES_DETAIL S WHERE N
BETWEEN 26 AND 50;

SELECT * FROM SALES_DETAIL FETCH FIRST 50 ROWS ONLY

you will not notice much difference. Both plans will be tablespace
scans. If you run these two queries against a small table, you will
not notice much difference in response time either. Although the plans
look very similar, the actual execution is quite different:

the first statement will scan the whole table, checking every row
against the criteria N BETWEEN 26 AND 50
the second statement will also start scanning the whole table, but the
scanning will stop as soon as the required amount of rows is
retrieved.

Do not assume that these 2 statements will perform equally well
against a big table. If you use db2batch utility to measure real
execution costs, you will be able to detect the difference: the first
statement will scan the whole table, while the second one will read
just several data pages containing the first 25 rows. Should the table
in production environment grow really big, the difference would be
dramatic, so it is usually a good idea to test your application
against realistically big amounts of data from the early stages of
development.

As we have seen, FETCH FIRST and OPTIMIZE FOR clauses are usually more
appropriate for limiting amount of rows if the result set may be big.
Also I have demonstrated the importance of considering both execution
plan and real execution costs.

[TP]

Re: select * from table AND navigation

Hey,

Thanks for your reply.

I tried the
---------------------------------------------------
select ROW_NUMBER() OVER() AS N, S.* FROM from P390V.FUND s WHERE N
BETWEEN 26 AND 50
---------------------------------------------------
but I got this error
---------------------------------------------------
DBA2191E SQL execution error.

com.ibm.db.Data Exception: A database manager error occurred. :
[IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found
following "". Expected tokens may include: ", FROM INTO ".
SQLSTATE=42601
---------------------------------------------------
That aside, when you say

SELECT * FROM SALES_DETAIL FETCH FIRST 50 ROWS ONLY

and the database gets me 50 rows. how will this help me navigate to
the next 50 rows. I mean how can I say

SELECT * FROM SALES_DETAIL FETCH (the next) 50 ROWS ONLY (now)

and so on?

Thanks for your help.

TP.


ak_tiredofspam@ yahoo.com (AK) wrote in message news:<46e627da. 0401201943.7174 8ec8@posting.go ogle.com>...[color=blue]
> Let us consider a very common situation. A GUI application issues a
> query, then the first 25 rows from the result set are diplayed on
> screen. If the user presses Page Down, then the next 25 rows will be
> displayed, and so on. It is well known that FETCH FIRST 25 ROWS and
> OPTIMIZE FOR 25 ROWS clauses of SELECT statement are very useful in
> this situation. In fact, using either of them is a straightforward way
> to tell the optimizer what you actually need. The optimizer, in its
> turn, might choose an entirely different access plan to satisfy a
> query with such a clause. For example, the query
>
> SELECT * FROM CUSTOMER ORDER BY LAST_NAME
>
> may be satisfied by a tablespace scan, followed by a sort. As a
> result, the initial response time may be quite high. However, this is
> the fastest way to retrieve the whole result set. Adding an OPTIMIZE
> FOR, as well as FETCH FIRST, clause may cause the optimizer to choose
> an index access plan, so that the first 25 rows are returned as soon
> as possible. The immediate response time usually comes at a price:
> should we ever need to retrieve all the rows, the overall execution
> time would be high. It could be significantly higher than the
> execution time of a tablespace scan.
>
> You may also have come across the advice to use an OLAP function
> ROW_NUMBER() to limit the output to 25 rows at a time. This advice is
> more controversial. Let us discuss it in more detail and see its
> potential drawbacks. If you use Visual Explain to compare execution
> plans of these two statements:
>
> SELECT ROW_NUMBER() OVER() AS N, S.* FROM SALES_DETAIL S WHERE N
> BETWEEN 26 AND 50;
>
> SELECT * FROM SALES_DETAIL FETCH FIRST 50 ROWS ONLY
>
> you will not notice much difference. Both plans will be tablespace
> scans. If you run these two queries against a small table, you will
> not notice much difference in response time either. Although the plans
> look very similar, the actual execution is quite different:
>
> the first statement will scan the whole table, checking every row
> against the criteria N BETWEEN 26 AND 50
> the second statement will also start scanning the whole table, but the
> scanning will stop as soon as the required amount of rows is
> retrieved.
>
> Do not assume that these 2 statements will perform equally well
> against a big table. If you use db2batch utility to measure real
> execution costs, you will be able to detect the difference: the first
> statement will scan the whole table, while the second one will read
> just several data pages containing the first 25 rows. Should the table
> in production environment grow really big, the difference would be
> dramatic, so it is usually a good idea to test your application
> against realistically big amounts of data from the early stages of
> development.
>
> As we have seen, FETCH FIRST and OPTIMIZE FOR clauses are usually more
> appropriate for limiting amount of rows if the result set may be big.
> Also I have demonstrated the importance of considering both execution
> plan and real execution costs.[/color]

[AK]

Re: select * from table AND navigation

maybe ROW_NUMBER() does not work on your platrom. I was spaking about V8.1 onLUW

I would try OPTIMIZE FOR clAUSE

[Dieter Wagner]

Re: select * from table AND navigation

TP wrote:[color=blue]
>
> Hey,
>
> Thanks for your reply.
>
> I tried the
> ---------------------------------------------------
> select ROW_NUMBER() OVER() AS N, S.* FROM from P390V.FUND s WHERE N
> BETWEEN 26 AND 50
> ---------------------------------------------------
> but I got this error
> ---------------------------------------------------
> DBA2191E SQL execution error.
>
> com.ibm.db.Data Exception: A database manager error occurred. :
> [IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found
> following "". Expected tokens may include: ", FROM INTO ".
> SQLSTATE=42601
> ---------------------------------------------------
> That aside, when you say
>
> SELECT * FROM SALES_DETAIL FETCH FIRST 50 ROWS ONLY
>
> and the database gets me 50 rows. how will this help me navigate to
> the next 50 rows. I mean how can I say
>
> SELECT * FROM SALES_DETAIL FETCH (the next) 50 ROWS ONLY (now)
>
> and so on?
>
> Thanks for your help.
>
> TP.
>
> ak_tiredofspam@ yahoo.com (AK) wrote in message news:<46e627da. 0401201943.7174 8ec8@posting.go ogle.com>...[color=green]
> > Let us consider a very common situation. A GUI application issues a
> > query, then the first 25 rows from the result set are diplayed on
> > screen. If the user presses Page Down, then the next 25 rows will be
> > displayed, and so on. It is well known that FETCH FIRST 25 ROWS and
> > OPTIMIZE FOR 25 ROWS clauses of SELECT statement are very useful in
> > this situation. In fact, using either of them is a straightforward way
> > to tell the optimizer what you actually need. The optimizer, in its
> > turn, might choose an entirely different access plan to satisfy a
> > query with such a clause. For example, the query
> >
> > SELECT * FROM CUSTOMER ORDER BY LAST_NAME
> >
> > may be satisfied by a tablespace scan, followed by a sort. As a
> > result, the initial response time may be quite high. However, this is
> > the fastest way to retrieve the whole result set. Adding an OPTIMIZE
> > FOR, as well as FETCH FIRST, clause may cause the optimizer to choose
> > an index access plan, so that the first 25 rows are returned as soon
> > as possible. The immediate response time usually comes at a price:
> > should we ever need to retrieve all the rows, the overall execution
> > time would be high. It could be significantly higher than the
> > execution time of a tablespace scan.
> >
> > You may also have come across the advice to use an OLAP function
> > ROW_NUMBER() to limit the output to 25 rows at a time. This advice is
> > more controversial. Let us discuss it in more detail and see its
> > potential drawbacks. If you use Visual Explain to compare execution
> > plans of these two statements:
> >
> > SELECT ROW_NUMBER() OVER() AS N, S.* FROM SALES_DETAIL S WHERE N
> > BETWEEN 26 AND 50;
> >
> > SELECT * FROM SALES_DETAIL FETCH FIRST 50 ROWS ONLY
> >
> > you will not notice much difference. Both plans will be tablespace
> > scans. If you run these two queries against a small table, you will
> > not notice much difference in response time either. Although the plans
> > look very similar, the actual execution is quite different:
> >
> > the first statement will scan the whole table, checking every row
> > against the criteria N BETWEEN 26 AND 50
> > the second statement will also start scanning the whole table, but the
> > scanning will stop as soon as the required amount of rows is
> > retrieved.
> >
> > Do not assume that these 2 statements will perform equally well
> > against a big table. If you use db2batch utility to measure real
> > execution costs, you will be able to detect the difference: the first
> > statement will scan the whole table, while the second one will read
> > just several data pages containing the first 25 rows. Should the table
> > in production environment grow really big, the difference would be
> > dramatic, so it is usually a good idea to test your application
> > against realistically big amounts of data from the early stages of
> > development.
> >
> > As we have seen, FETCH FIRST and OPTIMIZE FOR clauses are usually more
> > appropriate for limiting amount of rows if the result set may be big.
> > Also I have demonstrated the importance of considering both execution
> > plan and real execution costs.[/color][/color]

select * from (select ROW_NUMBER() OVER() AS N, S.* FROM P390V.FUND s)
as dummy WHERE N BETWEEN 26 AND 50

I would prefer

select * from (select ROW_NUMBER() OVER(order by <keycolumn#1> , ...,
<keycolumn#N> ) AS N, S.* FROM P390V.FUND s) as dummy WHERE N BETWEEN 26
AND 50
--
Dieter Wagner Dieter.Wagner@t de-online.de
Software Development Group I TDE - Tele Data Electronic GmbH
Durmersheim, Germany

[AK]

Re: select * from table AND navigation

>[color=blue]
> select * from (select ROW_NUMBER() OVER() AS N, S.* FROM P390V.FUND s)
> as dummy WHERE N BETWEEN 26 AND 50
>
> I would prefer
>
> select * from (select ROW_NUMBER() OVER(order by <keycolumn#1> , ...,
> <keycolumn#N> ) AS N, S.* FROM P390V.FUND s) as dummy WHERE N BETWEEN 26
> AND 50[/color]

if you have a look at real execution costs, yu'll see that the whole
table is scanned to retrieve 25 rows. Very inefficient. Use OPTIMIZE
FOR or FETCH FIRST

[Dieter Wagner]

Re: select * from table AND navigation

AK wrote:[color=blue]
>
>
> if you have a look at real execution costs, yu'll see that the whole
> table is scanned to retrieve 25 rows. Very inefficient. Use OPTIMIZE
> FOR or FETCH FIRST
>[/color]
OK it's inefficient, but it works.
In your former posting you wrote[color=blue]
>
> SELECT ROW_NUMBER() OVER() AS N, S.* FROM SALES_DETAIL S WHERE N
> BETWEEN 26 AND 50;
>[/color]
This doesn't work. My command line interpreter (DB2 7.2.8, AIX) told
me:
db2 => SELECT ROW_NUMBER() OVER() AS N, S.* FROM lzgneu.mdst S WHERE N
BETWEEN 26 AND 50;
SQL0206N "N" is not valid in the context where it is used.
SQLSTATE=4270

How does the correct statement look like?
--
Dieter Wagner Dieter.Wagner@t de-online.de
Software Development Group I TDE - Tele Data Electronic GmbH
Durmersheim, Germany

[PM \(pm3iinc-nospam\)]

Re: select * from table AND navigation

If memory serves mes good, ROW_NUMBER() was introduced in v6..

PM

"AK" <ak_tiredofspam @yahoo.com> a écrit dans le message de
news:46e627da.0 401211819.a6d71 08@posting.goog le.com...[color=blue]
> maybe ROW_NUMBER() does not work on your platrom. I was spaking about V8.1[/color]
onLUW[color=blue]
>
> I would try OPTIMIZE FOR clAUSE[/color]

[Blair Adamache]

Re: select * from table AND navigation

Correct - documented in the release notes for DB2 Linux/UNIX/Windows:

ftp://ftp.software.ibm.com/ps/produc...02/release.txt

PM (pm3iinc-nospam) wrote:
[color=blue]
> If memory serves mes good, ROW_NUMBER() was introduced in v6..
>
> PM
>
> "AK" <ak_tiredofspam @yahoo.com> a écrit dans le message de
> news:46e627da.0 401211819.a6d71 08@posting.goog le.com...
>[color=green]
>>maybe ROW_NUMBER() does not work on your platrom. I was spaking about V8.1[/color]
>
> onLUW
>[color=green]
>>I would try OPTIMIZE FOR clAUSE[/color]
>
>
>[/color]

[Tokunaga T.]

Re: select * from table AND navigation

> I tried the[color=blue]
> ---------------------------------------------------
> select ROW_NUMBER() OVER() AS N, S.* FROM from P390V.FUND s WHERE N
> BETWEEN 26 AND 50
> ---------------------------------------------------
> but I got this error
> ---------------------------------------------------
> DBA2191E SQL execution error.
>
> com.ibm.db.Data Exception: A database manager error occurred. :
> [IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found
> following "". Expected tokens may include: ", FROM INTO ".
> SQLSTATE=42601
> ---------------------------------------------------[/color]
Your DB2 may not support ROW_NUMBER.
But if your DB2 support ROW_NUMBER, you should use nested table expression.
SELECT *
FROM (SELECT ROW_NUMBER() OVER() AS N, S.* FROM P390V.FUND s) s
WHERE N BETWEEN 26 AND 50

[AK]

Re: select * from table AND navigation

Dieter,

my point is different:
OPTIMIZE FOR and FETCH FIRST usually perform much better

maybe
SELECT (ROW_NUMBER() OVER()) AS N ...
could work on V7?

I only have V8 and can't verify

[TP]

Re: select * from table AND navigation

I wish I could tell you if it does support row_number(), but I really
cannot for sure (woah me). When I open up my command center, on
connect, it tells me DB2 version 7.1.1 for os390.

But as far as this problem is concerned. I have gone the round route.

First get the table primary keys from sysibm tables, then do a order
by pk, fetch first 10 where pk_value is greater than the last highest
one shown. I am doing this for next navigation or forward navigation.

The thing with this approach is that I can keep track of the last
shown value and achieve first, previous, next and last row navigation.

Any ideas to optimize this further?

Thanks.

TP
tonkuma@jp.ibm. com (Tokunaga T.) wrote in message news:<8156d9ae. 0401231518.84b5 6af@posting.goo gle.com>...[color=blue][color=green]
> > I tried the
> > ---------------------------------------------------
> > select ROW_NUMBER() OVER() AS N, S.* FROM from P390V.FUND s WHERE N
> > BETWEEN 26 AND 50
> > ---------------------------------------------------
> > but I got this error
> > ---------------------------------------------------
> > DBA2191E SQL execution error.
> >
> > com.ibm.db.Data Exception: A database manager error occurred. :
> > [IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found
> > following "". Expected tokens may include: ", FROM INTO ".
> > SQLSTATE=42601
> > ---------------------------------------------------[/color]
> Your DB2 may not support ROW_NUMBER.
> But if your DB2 support ROW_NUMBER, you should use nested table expression.
> SELECT *
> FROM (SELECT ROW_NUMBER() OVER() AS N, S.* FROM P390V.FUND s) s
> WHERE N BETWEEN 26 AND 50[/color]