Re: SQL OFFSET and LIMIT support

Thanks for the comments Joe and Birgitta.

I’ll have to see how it works in PHP - I’m guessing it should since the statements have to be prepared.

Now I’ll have to start a campaign to get … INTO aDSArray on a straight select to work - that would make the code so much cleaner!

On Jan 11, 2016, at 9:28 AM, Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx> wrote:

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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com