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 12 7648
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: 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
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.
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.DataException: 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************@yahoo.com (AK) wrote in message news:<46**************************@posting.google. com>... 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.
maybe ROW_NUMBER() does not work on your platrom. I was spaking about V8.1 onLUW
I would try OPTIMIZE FOR clAUSE
TP wrote: 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.DataException: 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************@yahoo.com (AK) wrote in message news:<46**************************@posting.google. com>... 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.
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 Di***********@tde-online.de
Software Development Group I TDE - Tele Data Electronic GmbH
Durmersheim, Germany
> 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
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
AK wrote:
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
OK it's inefficient, but it works.
In your former posting you wrote SELECT ROW_NUMBER() OVER() AS N, S.* FROM SALES_DETAIL S WHERE N BETWEEN 26 AND 50;
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 Di***********@tde-online.de
Software Development Group I TDE - Tele Data Electronic GmbH
Durmersheim, Germany
If memory serves mes good, ROW_NUMBER() was introduced in v6..
PM
"AK" <ak************@yahoo.com> a écrit dans le message de
news:46*************************@posting.google.co m... maybe ROW_NUMBER() does not work on your platrom. I was spaking about V8.1
onLUW I would try OPTIMIZE FOR clAUSE
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: If memory serves mes good, ROW_NUMBER() was introduced in v6..
PM
"AK" <ak************@yahoo.com> a écrit dans le message de news:46*************************@posting.google.co m...
maybe ROW_NUMBER() does not work on your platrom. I was spaking about V8.1
onLUW
I would try OPTIMIZE FOR clAUSE
> 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.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found following "". Expected tokens may include: ", FROM INTO ". SQLSTATE=42601 ---------------------------------------------------
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
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
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 to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81*************************@posting.google.c om>... 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.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found following "". Expected tokens may include: ", FROM INTO ". SQLSTATE=42601 --------------------------------------------------- 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Robert P. Stearns |
last post by:
I have been trying to convert the following simple table into CSS.
<table>
<tr><th colspan=2>Header Area</th></tr>
<tr><th>Navigation Area</th><th>Information Area</th></tr>
<tr><th...
|
by: max junker |
last post by:
hello,
i have a table, that serves as a navigation box in the upper left
corner of my
page. i aligned this table with <table align="left">. right to it i
have a heading and some text. but...
|
by: Dirk Bruere at Neopax |
last post by:
Is it possible to have one table somehow 'called' to appear on different pages?
I want something to appear on all pages, yet only want to (re)edit one item.
--
Dirk
The Consensus:-
The...
|
by: lawrence |
last post by:
Can I do something like the following to get a browser to redirect to
a new url every time someone picks a new value in a select box?
function changeUrl() {
var redirect;
redirect =...
|
by: TP |
last post by:
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...
|
by: mick.walker |
last post by:
Hi all,
I need some help with a simple select query using MS SQL Server 2005.
The problem I am having is due to the contents of the table, it can
contain names which use punctuation marks such as:...
|
by: Matthew Wells |
last post by:
I have a listbox set to simple multi select. For this example, users only
select one item at a time. I have command buttons on the form for First,
Previous, Next, Last, New (record). The form...
|
by: nathj |
last post by:
Hi,
I have developed a table for storing navigation items, this is then used to built the navigation panel on my website.
Each item has a field indicating which bar it is to go on:
0 = Every...
|
by: Dave Mathew |
last post by:
I'm in the process of building some navigation for a website. I would
like to use the capabilities of the sitemap in asp.net but have ran
into some styling/usability issues in the past using just...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |