Re: SQL OFFSET and LIMIT support

Jon, LIMIT [START, ] MAX is completely functional in iSeries Navigator. I'm doing some testing with SQLRPGLE which I suspect is your area of concern but so far I've had no problems. LIMIT MAX works in STRSQL, but not the optional START parameter.

Performance is a bit rough on initial access and considerably better on subsequent calls provided you have an access path, even on tables with tens of millions of record. I avoid the billion row DBs if I can help it :). I'm doing some additional research on ad hoc access (much less efficient as you might suspect) and using host variables for the start and limit values. I'll report back when I get a chance.

Joe

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 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.