As more and more IBM i programmers are writing web applications these days, we're all having to deal with the challenges of stateless programming. In particular, it's difficult to implement something equivalent to the "single-page load subfile" in a web application.

In a traditional interactive application, you'd simply keep the file open with the cursor pointing to the last record you loaded into your subfile. If the user presses the Page Down key, you read from that point forward to load the next page. If the user presses Page Up, you read backwards to load the next page. This technique works equally well with native I/O (using the READ and READP opcodes in RPG) as well as SQL, using a scrollable cursor.

But how do you do it in a web application where you can't keep the file open between calls, and you can't maintain your position with an open cursor? There have been some useful SQL techniques posted to the various online forums that help you solve this problem. In this article, I list the ones that I find interesting.

Fetch a Small Chunk of a Big Chunk

The first answer I found that had promise was one posted to the System iNetwork forums by Birgitta Hauser. Her sample code follows:

With x as (Select Fld1, Fld2, ... FldN
              From MyTable
              Where ...
              Order By Key1, Key2, ... KeyN
              Fetch first 2025 rows only)
     y as (Select *
              From x
              Order By Key1 Desc, Key2 Desc, ... KeyN Desc
              Fetch first 25 rows only)
Select *
  From y
  Order By Key1, Key2, ... KeyN  

In this example, Birgitta was filling up pages of 25 rows at a time. In this case, she wanted the eightieth page of her data. To get that page, she told SQL to fetch the first 2,025 rows of data (thus records 1-2025) in ascending sequence. Then she viewed the same data in descending sequence, therefore starting at the end of the 2,025 rows, and asked to get the first 25 of that result. Very clever.

Obviously, you'd have to insert code to calculate the numbers to fetch, and you'd have to make them variable, but the idea has merit. One major disadvantage of this technique, however, is that it requires each row to have a unique set of keys. If there are duplicate keys, this technique will be problematic.

Fetch by OLAP Row Number

In the same thread as above, Lev Ostromich demonstrated a technique that used the ROW_NUMBER() OVER() function. This function is part of the Online Analytical Processing (OLAP) capabilities of DB2 starting at OS version 5.4. This technique solves the duplicate key issue, but it does require 5.4 and the OLAP functionality.

SELECT * FROM (
  SELECT A.*, ROW_NUMBER()  OVER (ORDER BY key1,key2,key3)  AS seqNumber
  FROM myTable A)  AS temp_result
WHERE seqNumber BETWEEN 2000 AND 2025;

An earlier reference to this technique can be found in the December 12, 2007, issue of Centerfield Technology's Out In Left Field newsletter. On the last page of the newsletter, SQL guru Elvis Budimlic discusses the ROW_NUMBER() technique and provides sample code.

MySQL's LIMIT Keyword

These days, it's possible to run MySQL on i. If you do that, you can use the IBMDB2I storage engine for MySQL to enable access to physical files and logical files, but instead of using the query engine provided with DB2, you use MySQL.

One nice thing about MySQL is the LIMIT keyword. This is a nonstandard SQL keyword that can be used to control the page of data received. Here's an example of that:

select * from myTable order by key1, key2, key3 LIMIT 2000, 25

The disadvantage of this is that you have to go through MySQL. That's not too bad if you're coding in PHP, where MySQL fits nicely. But for other environments, such as RPG, C, Cobol, or Java, it's a little awkward to use MySQL. Plus, DB2 runs much faster on i than MySQL does.

Using Relative Cursor Positioning

Another technique was posted to the System iNetwork forums in this thread. In that thread, the user called asmith explained that you can open a new cursor each time, but position to the appropriate page each time with a relative start position.

c/exec SQL                                             
C+ FETCH RELATIVE :START FROM CURSOR INTO :DS 
c/end-exec  

The preceding code works great in embedded SQL, but how would you do the same thing in PHP, where you don't use embedded SQL and can't execute the FETCH RELATIVE command? Recently, Kelly Cookson posted the answer to the WEB400 mailing list of midrange.com.

$query = "SELECT EOPYEAR, EOPPRD, EOPBGSDT, EOPCLSDT FROM DOTDATA.EOPMAST";
$result = db2_exec($conn, $query, array('cursor' => DB2_SCROLLABLE));

Notes from Cookson:

The script then uses a for loop to get individual rows from the result set. The variable $rn is the row on which we want to start the loop. The variable $limit is the row on which we want to end the loop.

$i=$rn;

for ($i=$rn; $i<=$limit; $i++)
{
   $row = db2_fetch_both($result, $i);
   echo "".$row["EOPYEAR"]."";
   echo "".$row["EOPPRD"]."";
   echo "".$row["EOPBGSDT"]."";
   echo "".$row["EOPCLSDT"]."";
}

At this point, it's tough to see which of these options works the best, but at least we have several options. Try them all and see which one works the best for you!