I'm checked around a bit and did not see a similiar post, please correct me if I am wrong. I need to grab a set of rows from my query. I can select those rows by saying FETCH FIRST <however many> ROWS. That works great for the first page. But what happens when I am on page 4 and I need to grab that number of rows with an offset of 4 times that amount.
Can this be done without a stored procedure? That would take way to long to get past the admins here. Can anyone direct me to some resources that might get me started on my way. Can this be done without a cursor?
I'm new to DB2, thanks for your help.
Thinking that you must be ordering this query by some column,
ID or whatever, and initially might do:
select colA
, colB
, colC
from sometable
order by colA
Fetch first N rows only
itererate through the result set and display those N rows on a page, save off the value of that last column which you are ordering by.
For subsequent pages add a predicate:
where colA > savedLastColAValue
You could save that last value as a Session variable or perhaps as a hidden form field. Perhaps even use that where predicate even in the first page by initially setting the saved value to something less that what you know the min value is for that sorted field..
fv
It would be great if your table had some unique numeric column like an integer counter. DB2 does not really support pagination style resultsets. Even through cursors and the likes, you are still stuck with the lack of an ability to select only a set from the resultset before it is returned to use except for using fetch first N rows only. The only really plausable solution is one like what fractalvibes listed although you may need to use more than one column depending on how your table is set up. If you have any primary keys set in the table that would be the column(s) to use in the corresponding queries to continue to the next "page" of the resultset.
Yes, ideally you would have an identity column for the table,
but actually a char or date/timestamp column would work.
Basically - whatever you are sorting the result set on...and you would want an index on that column, as Onslaught
says.
fv
thanks for the replies guys. I think I'm going to have to adding in some index of my own, we shall see.
In case ytou do have any other ideas, perhaps a little more infomation on the setup would be in order:
The results are currently sorted on ROW_NR, or the row number corresponding to where they woud be printed out with a full list. The row number assumes that each company will have exactly 10 lines of it's own, and so some row numbers will be missing when there is no more data from the company.
The query can go through two different paths.
Path 1 will print out the full set and so you can get a general idea of how many your getting on each page. For this the query had a start and end page based off of a multiplication of the page*rowsPerPage.
Then there is the second path, where some of those results are missing. There can be a company on row 1-10 and the next company is on row 120-130 then 130-140 and so on. this caused me to switch over to the FETCH FIRST * ROWS statement.
Now, by pulling out the last row I had I would be able to iterate through the pages in order, but this is actually seldom the case. I need to be able to just from page 1 to page 5 and would not have knowledge of where they left off.
The multiplication times the page wwoud work to a certain capacity here. I can multiply the page * rowsPerPage and grab everything leading up to it and delete off the calculated offset, but it's inefficient.
Again, thanks for your help guys. I'm still new on this and always looking for a better way to write my code.
Due to things like deletions, I would not count on the
row after row N to be row N+1...could be some gaps in there.
You need some way after displaying rows 1 through N on a page to persist what that last N was so that for the next page
you can query where colA > N. Might want to also persist the beginning key for a page also, so you can traverse backwards.
fv
You didn't specify the language you were using so I'm going to just throw out a couple of suggestions.
1. I access DB2 using C++ most of the time and there is an API function called SQLFetchScroll (http://webdocs.caspur.it/ibm/web/udb-6.1/db2l0/sqll1407.htm#HDRFNNFS1) that allows you to specify FetchOrientation with flags like SQL_FETCH_ABSOLUTE, SQL_FETCH_RELATIVE, SQL_FETCH_BOOKMARK combined with a FetchOffset value to position the cursor to the desired spot.
2. There is a ROW_NUMBER function that allows you to number the rows based on a specified order clause. You can then select on a range of row numbers. I haven't had to use it but it works something like:
SELECT field1, field2, ..., fieldx,
ROW_NUMBER() OVER (ORDER BY field5, field7) AS RN
FROM tablename
WHERE RN BETWEEN 11 AND 20
ORDER BY RN
You might have to do a little research to get the exact syntax right, but something like this might work. Here's one page that had an example that I was able to Google fairly quickly: http://www.devx.com/getHelpOn/10MinuteSolution/16573/1954?pf=true.
There is also a RANK() function that allows you to do a similar type of sorting. The ROW_NUMBER() and RANK() functions are usually mentioned in relation to OLAP type queries. I'm not sure if every version of DB2 has these functions and I'm not at a machine where I can test it.
I know about the row_number and rank functionality, I use the rank olap functionality in a couple of queries, but unfortunately I've never been able to get something like your example to work.
Here is an example of what I get when I try it:
C:\Program Files\SQLLIB\bin>db2 select article, row_number() over (order by article) as rn from my.articles order by rn fetch first 10 rows only
C:\Program Files\SQLLIB\bin>db2 select article, row_number() over (order by article) as rn from my.articles where rn between 1 and 10 order by rn
SQL0206N "RN" is not valid in the context where it is used. SQLSTATE=42703
C:\Program Files\SQLLIB\bin>db2 select article, row_number() over (order by article) as rn from my.articles where row_number() between 1 and 10 order by rn
SQL0104N An unexpected token "" was found following "SYSIBM.ROW_NUMBER".
Expected tokens may include: "OVER". SQLSTATE=42601
C:\Program Files\SQLLIB\bin>db2 select article, row_number() over (order by article) as rn from my.articles where row_number() over (order by rehau_art) between 1 and 10 order by rn
SQL0120N A WHERE clause, GROUP BY clause, SET clause, or SET
transition-variable statement contains a column function. SQLSTATE=42903I would really like to figure this out in the end on a possible way to do this, but haven't found out a method that is acceptable as of yet unless the table is design to do so. As far as the language goes, we were refering to just sql as a way to do this.
I just tested the following query against a UDB 8.1 database. The trick is to alias the initial select by wrapping it in a WITH clause and then selecting from the alias:
WITH DEVSHEDTEST AS
(SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY,
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RN
FROM EMPLOYEE)
SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY, RN
FROM DEVSHEDTEST
WHERE RN BETWEEN 10 AND 15
FIRSTNME MIDINIT LASTNAME SALARY RN
------------ ------- --------------- ----------- --------------------
SEAN O'CONNELL 29250.00 10
SALVATORE M MARINO 28760.00 11
HEATHER A NICHOLLS 28420.00 12
DAVID BROWN 27740.00 13
MARIA L PEREZ 27380.00 14
ETHEL R SCHNEIDER 26250.00 15
That's what I was missing, you are the man!
This also works with version 7.2. :D