"Result set pagination" refers to retrieval of a query result set in "pages," with each page including a sub-set of the result set's rows. A typical scenario might involve presentation to a user of employee names (with, perhaps, accompanying information), starting with a particular name and going forward from there in ascending sequence based on last name followed by first name. Let's suppose that such a request has been initiated by a user, with the supplied "starting point" name being MARY ERIKSON.
Prior to Db2 12 for z/OS, the programmatic way of dealing with such a request was not entirely developer-friendly, in a couple of respects. First, there's the matter of generating the desired result set. That required a predicate with the following syntax:
WHERE (LASTNAME = ‘ERIKSON’ AND FIRSTNAME >= ‘MARY’) OR (LASTNAME > ‘ERIKSON’)
A little clunky; and, it gets clunkier as more columns are examined (as would be the case if, for example, the request were to display names in ascending order by last name, then first name, then middle name, beginning with MARY TAYLOR ERIKSON).
The second not-totally-developer-friendly issue arises when the second page (and subsequent pages) of the result set has to be retrieved. Getting the first page is easy: you just fetch the first, say, 20 rows of the result set (if rows are to be displayed 20 to a page). How about getting the second page of 20 rows? Generally speaking, you could retrieve page n+1 of the result set by taking the last row in page n and plugging values from that row into a query predicate that would get you to the start of the next page's rows (and then fetch forward from there), utilizing the rather cumbersome syntax shown above. Referring to the example I've been using, if page 1 of the name-ordered result set ends with SAMUEL FIGGINS, page 2 could be retrieved via a query coded with the following predicate:
WHERE (LASTNAME = ‘FIGGINS’ AND FIRSTNAME >= ‘SAMUEL’) OR (LASTNAME > ‘FIGGINS’)
Again, this gets clunkier if more than two columns are to be considered for row ordering.
Alternatively, you could use ordinal positioning within the result set to generate succeeding pages, perhaps using a query with the OLAP specification ROW_NUMBER; or, you could just issue your initial result-set-building query, and fetch the first 40 rows and throw away the first 20 of those to display rows 21-40 in page 2 (an approach that gets progressively more wasteful, overhead-wise, as you progress through the result set's pages). Maybe you could use a scrollable cursor (though in some cases it might not be feasible to leave a cursor open for the purpose of retrieving succeeding pages of rows). In any case, you're looking at a level of coding complexity that is probably greater than you'd like.
Things get easier all around with Db2 12 (at function level V12R1M500 or later). For starters, initial result set generation is easier because of a newly supported predicate syntax. Want rows in last name, first name sequence, starting with ERIKSON, MARY? Just use this predicate:
WHERE (LASTNAME, FIRSTNAME) >= (‘ERIKSON’, ‘MARY’)
And that simple syntax stays simple if more columns (e.g., middle name) are considered for row ordering.
Retrieval of succeeding result set pages also gets easier with Db2 12. You actually have a couple of alternative approaches, both simpler versus the pre-Db2 12 options. One option would be to take the last name of page n of the result set and use it to get to the starting point of page n+1, utilizing the simple syntax shown above. If, for example, FIGGINS, SAMUEL is in the last row of page 1 of our example result set, we can get to the starting point of page 2 by way of this predicate:
WHERE (LASTNAME, FIRSTNAME) >= (‘FIGGINS’, ‘SAMUEL’)
The second of the two new options also involves the use of SQL syntax previously unavailable with Db2 for z/OS: to get (keeping with the same example) the second page of 20 result set rows, just re-issue the query initially used to generate the result set, with this clause added (the new-with-Db2 12 part is underlined):
OFFSET 20 ROWS FETCH FIRST 20 ROWS ONLY
To get page 3, you'd issue the initial query with the clause OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY, and so on for subsequent pages, each time bumping the value "n" in OFFSET n ROWS up by 20, if you want 20 rows per page (and for the sake of consistency, you might issue the query used to get page 1 with the clause OFFSET 0 ROWS FETCH FIRST 20 ROWS ONLY). Note that both the OFFSET value and the FETCH FIRST value can be provided via variables, so the clause could look like this:
OFFSET :hv1 ROWS FETCH FIRST :hv2 ROWS ONLY
That way, the initial query can be used again and again as a user pages through the result set, with values provided as needed to get to the starting row of page n (and this approach also allows for the number of displayed rows to change, if desired, as pages beyond the first, or beyond the first few, are displayed).
Of the two new (with Db2 12) SQL pagination options, which should you use? The OFFSET n ROWS FETCH FIRST m ROWS approach delivers maximum coding simplicity. The other approach (plug values from the last row of page n into a query predicate to get to the starting point of page n+1, utilizing the simplified syntax shown previously) can provide a performance edge, as it takes you directly to the start of page n+1, whereas the OFFSET n ROWS option requires skipping over the first "n" rows of the initial result set -- an action that has some overhead cost (the performance difference could increase as the value of "n" in the OFFSET n ROWS clause gets larger). You can decide whether you want to go the maximally simple route, or the not-quite-as-simple-but-still-pretty-simple route that can be more efficient.
Whichever choice you make, it's a better one, from a developer's perspective, than the options available in a pre-Db2 12 environment.
More of my favorite Db2 12 SQL-related enhancements will be covered in subsequent posts to this blog, so check back in.
WHERE (LASTNAME = ‘ERIKSON’ AND FIRSTNAME >= ‘MARY’) OR (LASTNAME > ‘ERIKSON’)
A little clunky; and, it gets clunkier as more columns are examined (as would be the case if, for example, the request were to display names in ascending order by last name, then first name, then middle name, beginning with MARY TAYLOR ERIKSON).
The second not-totally-developer-friendly issue arises when the second page (and subsequent pages) of the result set has to be retrieved. Getting the first page is easy: you just fetch the first, say, 20 rows of the result set (if rows are to be displayed 20 to a page). How about getting the second page of 20 rows? Generally speaking, you could retrieve page n+1 of the result set by taking the last row in page n and plugging values from that row into a query predicate that would get you to the start of the next page's rows (and then fetch forward from there), utilizing the rather cumbersome syntax shown above. Referring to the example I've been using, if page 1 of the name-ordered result set ends with SAMUEL FIGGINS, page 2 could be retrieved via a query coded with the following predicate:
WHERE (LASTNAME = ‘FIGGINS’ AND FIRSTNAME >= ‘SAMUEL’) OR (LASTNAME > ‘FIGGINS’)
Again, this gets clunkier if more than two columns are to be considered for row ordering.
Alternatively, you could use ordinal positioning within the result set to generate succeeding pages, perhaps using a query with the OLAP specification ROW_NUMBER; or, you could just issue your initial result-set-building query, and fetch the first 40 rows and throw away the first 20 of those to display rows 21-40 in page 2 (an approach that gets progressively more wasteful, overhead-wise, as you progress through the result set's pages). Maybe you could use a scrollable cursor (though in some cases it might not be feasible to leave a cursor open for the purpose of retrieving succeeding pages of rows). In any case, you're looking at a level of coding complexity that is probably greater than you'd like.
Things get easier all around with Db2 12 (at function level V12R1M500 or later). For starters, initial result set generation is easier because of a newly supported predicate syntax. Want rows in last name, first name sequence, starting with ERIKSON, MARY? Just use this predicate:
WHERE (LASTNAME, FIRSTNAME) >= (‘ERIKSON’, ‘MARY’)
And that simple syntax stays simple if more columns (e.g., middle name) are considered for row ordering.
Retrieval of succeeding result set pages also gets easier with Db2 12. You actually have a couple of alternative approaches, both simpler versus the pre-Db2 12 options. One option would be to take the last name of page n of the result set and use it to get to the starting point of page n+1, utilizing the simple syntax shown above. If, for example, FIGGINS, SAMUEL is in the last row of page 1 of our example result set, we can get to the starting point of page 2 by way of this predicate:
WHERE (LASTNAME, FIRSTNAME) >= (‘FIGGINS’, ‘SAMUEL’)
The second of the two new options also involves the use of SQL syntax previously unavailable with Db2 for z/OS: to get (keeping with the same example) the second page of 20 result set rows, just re-issue the query initially used to generate the result set, with this clause added (the new-with-Db2 12 part is underlined):
OFFSET 20 ROWS FETCH FIRST 20 ROWS ONLY
To get page 3, you'd issue the initial query with the clause OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY, and so on for subsequent pages, each time bumping the value "n" in OFFSET n ROWS up by 20, if you want 20 rows per page (and for the sake of consistency, you might issue the query used to get page 1 with the clause OFFSET 0 ROWS FETCH FIRST 20 ROWS ONLY). Note that both the OFFSET value and the FETCH FIRST value can be provided via variables, so the clause could look like this:
OFFSET :hv1 ROWS FETCH FIRST :hv2 ROWS ONLY
That way, the initial query can be used again and again as a user pages through the result set, with values provided as needed to get to the starting row of page n (and this approach also allows for the number of displayed rows to change, if desired, as pages beyond the first, or beyond the first few, are displayed).
Of the two new (with Db2 12) SQL pagination options, which should you use? The OFFSET n ROWS FETCH FIRST m ROWS approach delivers maximum coding simplicity. The other approach (plug values from the last row of page n into a query predicate to get to the starting point of page n+1, utilizing the simplified syntax shown previously) can provide a performance edge, as it takes you directly to the start of page n+1, whereas the OFFSET n ROWS option requires skipping over the first "n" rows of the initial result set -- an action that has some overhead cost (the performance difference could increase as the value of "n" in the OFFSET n ROWS clause gets larger). You can decide whether you want to go the maximally simple route, or the not-quite-as-simple-but-still-pretty-simple route that can be more efficient.
Whichever choice you make, it's a better one, from a developer's perspective, than the options available in a pre-Db2 12 environment.
More of my favorite Db2 12 SQL-related enhancements will be covered in subsequent posts to this blog, so check back in.
No comments:
Post a Comment