Paging Cursors And Position To
June 2, 2015 Paul Tuohy
|
Note: The code accompanying this article is available for download here. I had a reader email in relation to my article Paging Cursors. The article described a method for paging large lists using embedded SQL in RPG, and the reader was wondering if there was a way to position the list at a certain value. Of course there is! Please refer to the original article for all of the gruesome details but the basic concept is that you have a subprocedure that returns a “page” of rows from a result set. A page can be any number of rows up to a maximum size. On each call, the caller can specify which page to retrieve by a page number or next/previous page. As well as returning the page of rows, the subprocedure also returns the page number returned, the total number of page, the total number of rows in the result set and the number of rows in the returned page. Changing the subprocedure to cater for Position To requires:
The good news is that all we are going to do is add some code to the original subprocedure that still works as before: declare a cursor, open the cursor, determine how many rows are in the result set, calculate which page to retrieve, and retrieve the page. To help demonstrate how Position To will work, I will use the result set shown below in the code in Figure 1. This is from the Employee table in the DB2 sample database that, as in the original article, lists all employees in a requested department (in this case, department D11). In the examples that follow, we will be requesting a page size of five rows, which means that three possible pages can be returned by the subprocedure (rows 1 to 5, 6 to 10, and row 11 on the last page). 000060 IRVING STERN 32250.00 000150 BRUCE ADAMSON 25280.00 000160 ELIZABETH PIANKA 22250.00 000170 MASATOSHI YOSHIMURA 24680.00 000180 MARILYN SCOUTTEN 21340.00 000190 JAMES WALKER 20450.00 000200 DAVID BROWN 27740.00 000210 WILLIAM JONES 18270.00 000220 JENNIFER LUTZ 29840.00 200170 KIYOSHI YAMAMOTO 24680.00 200220 REBA JOHN 29840.00 Figure 1: A test result set. You can create the DB2 sample database on your system by calling the SQL stored procedure CREATE_SQL_SAMPLE and providing the name of the schema you want to create. For example:
CALL QSYS/CREATE_SQL_SAMPLE('MYSAMPLEDB').
Expected Results Assuming we call the subprocedure for department D11 with a current page of one and a page size of five, these are the results of requesting different Position To values. For each call, returned values for total pages will be three and total rows will be 11. No position to: DSPLY Current page is 1 DSPLY Rows this page 5 DSPLY Position To Rows at 0 DSPLY First Employee on Page 000060 Position to 000160: DSPLY Current page is 1 DSPLY Rows this page 5 DSPLY Position To Rows at 3 DSPLY First Employee on Page 000060 Position to 000180: DSPLY Current page is 1 DSPLY Rows this page 5 DSPLY Position To Rows at 5 DSPLY First Employee on Page 000060 Position to 000190: DSPLY Current page is 2 DSPLY Rows this page 5 DSPLY Position To Rows at 1 DSPLY First Employee on Page 000190 Position to 000210: DSPLY Current page is 2 DSPLY Rows this page 5 DSPLY Position To Rows at 3 DSPLY First Employee on Page 000190 Position to 200170: DSPLY Current page is 2 DSPLY Rows this page 5 DSPLY Position To Rows at 5 DSPLY First Employee on Page 000190 Position to 000001 (Does not exist. Before first row in result set): DSPLY Current page is 1 DSPLY Rows this page 5 DSPLY Position To Rows at 1 DSPLY First Employee on Page 000060 Position to 000205 (Does not exist but within result set): DSPLY Current page is 2 DSPLY Rows this page 5 DSPLY Position To Rows at 3 DSPLY First Employee on Page 000190 Position to 200200 (Does not exist but within result set): DSPLY Current page is 3 DSPLY Rows this page 1 DSPLY Position To Rows at 1 DSPLY First Employee on Page 200220 Position to 300000 (Does not exist. After last row in result set): DSPLY Current page is 3 DSPLY Rows this page 1 DSPLY Position To Rows at 1 DSPLY First Employee on Page 200220 New Parameters Two new parameters need to be added:
Determining The Position At Row The introduction of Online Analytical Processing (OLAP) tools in V5R4 make it fairly straight forward to retrieve a row number in a result set. Full details of OLAP functions can be found in New in V5R4: OLAP Ranking Specifications. The code in Figure 2 below shows the select statement used in generating the result set shown in Figure 1:
exec SQL
declare C001 insensitive scroll cursor for
select empno, firstNme || ' ' || lastname, salary
from employee
where workdept = :department
order by empno
for read only;
Figure 2: The SQL Select statement used in the subprocedure. If we had a means of numbering each of the rows in the result set, we would have a row number that could be used to determine which page to retrieve. The row_Number() over() OLAP functions will give us what we need. The statement shown below in Figure 3 would return the result set shown in Figure 4. The keys (which we will be using for Position To) and corresponding row numbers for the original result set in Figure 1.
select empno,
row_Number() over(order by empno) as rowIs
from employee
where workdept = :workdept;
Figure 3: Using the row_Number() over() OLAP functions. 000060 1 000150 2 000160 3 000170 4 000180 5 000190 6 000200 7 000210 8 000220 9 200170 10 200220 11 Figure 4: Results from using the row_Number() over() OLAP functions. For this to work properly, we need to ensure that order by argument in the over() function and the where clause of the statement are exactly the same as the where and order by clauses in the original select statement in Figure 2. But how do we use this for Position To? Figure 5 shows the code placed into the original subprocedure just before it performs the Fetch of the page of rows. This code is only executed if a Position To has been requested. The row number of the Position To row is determined by placing the OLAP select statement (Figure 3) in a Common Table Expression (CTE) and, from the generated result set, selecting the lowest row number where the value of the key (empno) is greater than or equal to the requested Position To value. If the requested Position To value is greater than any of the keys in the result set, the coalesce(min(rowIs), 0) ensures that a value of zero is returned.
if (positionTo <> *blanks);
exec SQL
with t1 as
(select empno,
row_Number()
over(order by empno) as rowIs
from employee
where workdept = :department)
select coalesce(min(rowIs), 0)
into :positionRow
from t1
where empno >= :positionTo;
re_calculate_Page_Data( positionRow
: totalRows
: pageSize
: currentPageIs
: rowInPage
: rowOffset );
endIf;
Figure 5: Position To logic added to subprocedure. Once the row number of the requested Position To has been determined, we need to re-calculate the Page data. Re-Calculate Page Data Figure 6 shows the re_calculate_Page_Data() subprocedure that recalculates necessary fields: currentPage, the row no of the Position To row in the page (as opposed to the result set) and the relative row number to use on the Fetch.
p re_calculate_Page_Data...
p b export
d pi
d positionRowIn 10i 0 const
d totalRows 10i 0 const
d pageSize 10i 0 const
d currentPageIs 10i 0
d rowInPage 10i 0
d rowOffset 10i 0
d positionRow s 10i 0
/free
positionRow = positionRowIn;
// If positionRow is 0, then requested position is beyond last row
if (positionRow = 0);
positionRow = totalRows;
endIf;
// Calculate page positioned row is in and position of row in page
currentPageIs = (positionRow / pageSize);
rowInPage = %rem(positionRow: pageSize);
if (rowInPage > 0);
currentPageIs += 1;
else;
rowInPage = pageSize;
endIf;
// Re-Calculate offset to 1st row to retrieve
rowOffset = ((currentPageIs - 1) * pageSize) + 1;
/end-Free
p e
Figure 6: The re_calculate_Page_Data() subprocedure. Since this recalculation logic is the same in all instances of Position To logic, best to have it in its own subprocedure! Paging Logic In any environment, paging logic can be cumbersome. With this added method for Position To, using the code provided, the only real concern is with the OLAP functions. Just remember to ensure that order by argument in the over() function is the same as the order by clause in the original select statement and the where clause of the statement is the same as the where clause in the original select statement. Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page. RELATED STORIES New in V5R4: OLAP Ranking Specifications
|
