SQL OFFSET and LIMIT support

Some time ago (while writing a PHP for RPGers series) I came across the fact that DB2 SQL had (shall we say) some shortcomings when it came to retrieving “subfile pages” of data in a stateless web environment.

To give you an idea what I mean in MySQL I can do the equivalent to this:

SELECT CUSTNO, NAME, CITY, STATE FROM CUSTOMERS
ORDER BY CUSTOMER
LIMIT :start, :max

At the time the closest DB2 SQL we could come up with was the rather ugly:

SELECT * FROM (SELECT CUSTOMER, NAME, CITY, STATE, ROW_NUMBER()
OVER (ORDER BY CUSTOMER) AS ROWNUMBER
FROM CUSTOMERS
ORDER BY CUSTOMER )
AS TEMPCUST
WHERE ROWNUMBER > :start AND
ROWNUMBER <= (:start+:max)

At the time the IBMers we consulted told us that support similar to the MySQL was on the way but it would be a while.

When we came across the LIMIT/OFFSET support in TR11/TR3 would celebrated because it looked as if the support was now available.

Then I noted on theIBM page (https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/OFFSET%20and%20LIMIT <https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM i Technology Updates/page/OFFSET and LIMIT>) that STRSQL support was not provided and more importantly that "This support is only possible when LIMIT is used as part of the outer fullselect of a DECLARE CURSOR statement or prepared select-statement.”

If I read that correctly then this really doesn’t do a lot for me since I would either have to do a full prepare each time or use a cursor.

Has anyone tried using this? Am I reading it correctly? If I am then I am more than a little disappointed and don’t really see the utility.


Jon Paris

www.partner400.com
www.SystemiDeveloper.com