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