FETCH, OFFSET, and result limiting in DB2 SQL

Result limiting versus optimization hints

IBM’s programming guide: when you need only a few of the thousands of rows that satisfy a query, use the optimize clause or the fetch clause of SELECT. FETCH FIRST changes the result. OPTIMIZE FOR changes (or tries to change) the access path.

FETCH FIRST n ROWS ONLY

FETCH FIRST n ROWS ONLY limits the result table to n rows. Benefits IBM documents:

• Local and distributed programs retrieve only the rows they need.
• If you FETCH the n+1st row from a cursor, Db2 returns SQLCODE +100.
• If you omit OPTIMIZE FOR, OPTIMIZE FOR n ROWS is implied.
• Distributed queries can fast implicit close the cursor after prefetching the nth row, saving a network trip.

1
2
3
4
5
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'A00'
 ORDER BY SALARY DESC
 FETCH FIRST 10 ROWS ONLY;

Always pair FETCH FIRST with ORDER BY when “top 10” has a meaning. Without ORDER BY, you get ten rows the access path happened to produce first—not the ten highest salaries.

FETCH FIRST also appears on SELECT INTO to prevent -811 when more than one row qualifies. That is a correctness use, not a paging use: add ORDER BY if you care which single row you keep.

WITH TIES

FETCH FIRST n ROWS WITH TIES (Db2 12 pagination) keeps extra rows that match the ORDER BY key of the last included row. Fifth place in a salary contest should not drop the other employee who has the same salary. ONLY never returns more than n rows; WITH TIES might.

OFFSET and FETCH NEXT

Db2 12 added SQL pagination. OFFSET k ROWS skips k rows of the result table before returning rows. Combined with a fetch clause you get page 2, page 3, and so on:

1
2
3
4
5
6
7
-- Page 2 of 10 (skip 10, return 10)
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY > 50000
 ORDER BY EMPNO
 OFFSET 10 ROWS
 FETCH FIRST 10 ROWS ONLY;

FETCH NEXT n ROWS ONLY is the standard spelling of “return n rows” after an OFFSET. On z/OS you will also see FETCH FIRST used for the same cap. Read your SQL Reference for the exact diagram on your function level; the idea is skip-then-take.

OFFSET may be a literal or a variable castable to BIGINT, which makes a host variable page number easy. Restrictions IBM and community write-ups emphasize: OFFSET belongs on the outermost fullselect of a prepared statement or DECLARE CURSOR (and SELECT INTO), not in a view definition, MQT, SQL table function RETURN, row permission, or column mask, and not with a sensitive dynamic cursor. Statements with OFFSET must not use non-deterministic or external-action expressions in ways the manual forbids.

OFFSET gets expensive

OFFSET 10000 still makes Db2 find and throw away those 10000 rows. Page 500 of a busy index is much slower than page 1. Prefer keyset (data-dependent) pagination:

1
2
3
4
5
6
7
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY > 50000
   AND (EMPNO) > :LAST-EMPNO
 ORDER BY EMPNO
 FETCH FIRST 10 ROWS ONLY
 OPTIMIZE FOR 10 ROWS;

The WHERE starts at the next key after the last row the user saw. An index on EMPNO can position directly. Composite keys use row comparison: (LASTNAME, EMPNO) > (:LN, :EN). This is the pattern IDUG and IBM paging talks recommend for high page numbers.

OPTIMIZE FOR n ROWS

OPTIMIZE FOR n ROWS does not limit the result. It tells Db2 you probably will not fetch more than n rows, so the optimizer may pick a path that returns the first row quickly (often avoiding a sort, using a matching index).

1
2
3
4
5
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE LASTNAME >= 'JONES'
 ORDER BY LASTNAME, EMPNO
 OPTIMIZE FOR 1 ROW;

• If you fetch far more than n rows after OPTIMIZE FOR n, performance can be worse than the default “assume all rows” plan.
• OPTIMIZE FOR 1 ROW is the classic “online random inquiry” hint.
• When both FETCH FIRST n and OPTIMIZE FOR m are specified, Db2 uses the smaller of n and m for optimization. The fetch clause still caps at n.
• Block fetch / DRDA row blocking can also be influenced so blocks are not huge when you asked for a handful of rows.

JDBC/SQLJ setMaxRows discards extra rows in the driver; it does not replace FETCH FIRST for server-side limiting. IBM notes that server optimization to limit rows happens when FETCH FIRST is in the SQL, not merely because the client set max rows.

Cursors, distributed, and SELECT INTO

For a local COBOL cursor, FETCH FIRST means the result table really has n rows; further FETCH gets +100. For SELECT INTO, FETCH FIRST 1 ROW ONLY is how you force a single row. For DDF, FETCH FIRST plus implicit close reduces CHATTY chat. Do not use OPTIMIZE FOR 1 ROW on a batch job that will drain the whole cursor—that job wants the all-rows plan.

ORDER BY plus FETCH FIRST can use an in-memory “keep the best n” technique instead of sorting the entire answer set (Db2 has done this for years for small n). That is another reason FETCH FIRST is more than a client-side loop that discards rows.

Explain It Like I'm Five

FETCH FIRST 10 is the teacher saying “only ten kids get to come in.” OPTIMIZE FOR 10 is the teacher whispering to the bus driver “we think only ten kids are riding, so a small bus is fine”—if 200 kids show up, the small bus was a bad idea. OFFSET 20 is “skip the first twenty kids in line,” which still means counting twenty kids. A smarter line is “start after kid number 000340” (keyset), so you do not recount everyone from the door.

Exercises

1. Write a top-5 salary query with ORDER BY and FETCH FIRST 5 ROWS ONLY.
2. Add OPTIMIZE FOR 5 ROWS and explain whether the result can still contain 5 rows if you also FETCH FIRST 5.
3. Write OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY for page 3 of size 10. Then rewrite page 3 as keyset pagination using EMPNO.
4. When would WITH TIES return more than 5 rows for FETCH FIRST 5?
5. Explain why a nightly unload job should not use OPTIMIZE FOR 1 ROW.

Frequently asked questions

Is LIMIT 10 valid on Db2 for z/OS?

Db2 for z/OS uses FETCH FIRST, not MySQL-style LIMIT. Some other Db2 families accept LIMIT as an alias. Write FETCH FIRST on z/OS.

Can OFFSET be used in a view?

Typically no—OFFSET is for the outermost fullselect of a statement, not a view body. Put paging in the application query that reads the view.

Does FETCH FIRST n apply before or after ORDER BY?

The result is ordered, then limited (conceptually). That is why ORDER BY matters: you limit the ordered result, not a random subset that is sorted afterward in the client.