FFnR is one of those tricky features that does actually exactly what it claims to do - surprise!

 

A simple test:

CREATE TABLE emp(name VARCHAR(10), salary INTEGER);
INSERT INTO emp VALUES
  ('Joe'   , 20000),
  ('Martin', 25000),
  ('Murphy', 30000),
  ('Sophie', 18000),
  ('Brett' , 22000),
  ('Joel'  , 31000);

Selecting the first three rows can be done like this:

SELECT * FROM emp FETCH FIRST 3 ROWS ONLY;
NAME       SALARY
---------- -----------
Joe              20000
Martin           25000
Murphy           30000

  3 record(s) selected.

If you're getting different three rows then that is entirely possible since we did not specify an order by.

 

FFnR is useful if you just want to get a sampling of the resultset or perhaps you just want to get a random row.

But another common requirement to select a defined subset of rows is the desire to page through a table.

That is you may want to scroll forward or backwards, say 10 rows at a time.
While FFnR allows you do do that it has two downsides:

1. Every time you resubmit the query to change FETCH FIRST 10 ROWS to FETCH FIRST 20 ROWS to FETCH FIRST 30 ROWS DB2 needs to recompile the query.
   That is very annoying and can overwhelm the system in a highly concurrent environment.
   DB2 does not allow you to use a parameter marker instead of a constant here.
2. You are getting more than you are asking for when you get those FETCH FIRST 30 ROWS ONLY.
   Reason being that you really didn't care for those first 20 rows. They just take bandwidth on the network and you need to throw them away in the application.

DB2 has several strategies to cope with this problem.

• You can use the "scrollable cursor" in feature.
  In this case you can open a cursor and, instead of re-issuing a query you can FETCH forward and backward.
  This works great if your application can hold state since it doesn't require DB2 to rerun the query every time.
• You can use the ROW_NUMBER() OLAP function to number rows and then return the subset you want.
  This is ANSI SQL
• You can use the ROWNUM pseudo columns which does the same as ROW_NUMBER() but is suitable if you have Oracle skills.
• You can use LIMIT and OFFSET if you are more leaning to a mySQL or PostgreSQL dialect. 

 

 Let's look at each of those:

• ROW_NUMBER()

  SELECT name, salary 
    FROM (SELECT ROW_NUMBER() OVER(ORDER BY salary) AS rn, 
                 emp.* 
            FROM emp)
    WHERE rn BETWEEN 3 AND 5;
  NAME       SALARY
  ---------- -----------
  Brett            22000
  Martin           25000
  Murphy           30000
  
    3 record(s) selected.

  This works and instead of the literals 3 and 5 you can use any variable or a parameter marker.
  Be sure you are on a later fixpack on DB2 9.7 to ensure DB2 stops processing once it has retrieved the 5th row.
   
• ROWNUM
  ROWNUM is pseudo column and only available if you either enable Oracle compatibility or flip the specific ROWNUM bit of the DB2_COMPATIBILITY_VECTOR:

  db2set DB2_COMPATIBILITY_VECTOR=ORA

  or

  db2set DB2_COMPATIBILITY_VECTOR=1

  followed by

  db2stop
  db2start

  You can enable this feature on an existing database.

  SELECT * FROM emp 
    WHERE ROWNUM BETWEEN 3 AND 5 
    ORDER BY salary;
  NAME       SALARY
  ---------- -----------
  Brett            22000
  Martin           25000
  Murphy           30000
  
    3 record(s) selected.

  Note that this is not what you would likely observe on Oracle!
  First, DB2 guarantees that ROWNUM will observe the ORDER BY.
  Second, DB2 supports ROWNUM with a ">" and the "BETWEEN" predicate.
  In Oracle you are likely to get an empty resultset.
  
  
• LIMIT, OFFSET
  Similarly to ROWNUM this feature is not available by default. You have to do either:

  DB2_COMPATIBILITY_VECTOR=MYS

  or

  DB2_COMPATIBILITY_VECTOR=4000

  followed by

  db2stop
  db2start

  This feature can be enabled on an existing database as well.

  SELECT * FROM emp 
    ORDER BY salary 
    LIMIT 3 OFFSET 2;
  NAME       SALARY
  ---------- -----------
  Brett            22000
  Martin           25000
  Murphy           30000
  
    3 record(s) selected.

  DB2 also supports the following variation of this syntax where the offset precedes the limit in a comma separated form:

  SELECT * FROM emp 
    ORDER BY salary 
    LIMIT 2, 3;
  NAME       SALARY
  ---------- -----------
  Brett            22000
  Martin           25000
  Murphy           30000
  
    3 record(s) selected.

  Limit and Offset can be any variable, register, literal or expression composed of those.