Honestly I only use it in conjunction with a cursor and multiple row fetching for doing some kind of cursor:
Limit and Offset are both passed to my procedure as parameters and the requested rows are returned en bloque to the caller.
If you use limit and offset as host variables, for each call (or each time one of the variables get changed) an OPEN Cursor must be performed.
Depending on how the CLOCSRSQL option is set either a FULL OPEN (complete optimization - must be performed at least at the first 2 opens) or a PSEUDO OPEN (data in the result set is only updated) is performed.
If the SQL statement is dynamically prepared (i.e. based on a String) a FULL OPEN must be performed, except the variables are passed by using parameter markers. In this case PREPARE must be executed only once and the variables are passed with the OPEN cursor statement.
... isn't the SQL statement always prepared dynamically except when using static SQL in either an embedded (RPG/COBOL) SQL program or in an SQL Stored Procedure or User Defined Function?
But both is normal programming behaviour.
Limit and Offset work correctly if you enter and run the SELECT statement in the IBM i Navigator's RUN an SQL Script facility or the ACS RUN SQL Script facility.
I just tested it with my calendar table with something like this:
Select * from Calendar
Where DateYear4 = 2016 and WeeISO between 20 and 40
Order By DateDate
Limit 10 Offset 50;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jon Paris
Gesendet: Monday, 11.1 2016 05:12
An: Midrange-L
Betreff: 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
--
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.
midrange.com
