|
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.