FETCH, isolation and other query constructs in DB2

One SELECT, several different decisions

Beginners often treat every clause after WHERE as a performance option. That is unsafe. Some clauses change the rows in the result, some change locking, and some only give Db2 information for optimization. Understanding the category prevents mistakes such as expecting OPTIMIZE FOR 1 ROW to enforce one-row logic or using WITH UR on a report that drives financial decisions.

1
2
3
4
5
6
7
8
9
10
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'A00'
 ORDER BY SALARY DESC, EMPNO
 OFFSET 0 ROWS
 FETCH FIRST 10 ROWS ONLY
 FOR READ ONLY
 OPTIMIZE FOR 10 ROWS
 WITH CS
 QUERYNO 91001;

This query asks for a deterministic first page: salary descending and employee number as a tie-breaker. It returns no more than ten rows, explicitly declares a read-only cursor, tells the optimizer to expect ten rows, uses cursor stability for this statement, and labels the query for EXPLAIN. Each clause has one job.

FETCH FIRST and FETCH NEXT

The fetch clause sets a maximum number of rows that can be retrieved. FETCH FIRST 10 ROWS ONLY means the result contains at most ten rows. If a cursor tries to fetch beyond that limit, Db2 handles the attempt like normal end of data. In an embedded SQL program, that normally means SQLCODE +100 after the last available row.

FETCH FIRST does not automatically mean "the best ten." Without ORDER BY, rows have no guaranteed sequence. A new index, updated statistics, a REBIND, or a different access path can change which qualifying rows happen to arrive first. For top-N logic, always state the business order.

1
2
3
4
5
-- Ten highest salaries, with a stable tie-breaker
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 ORDER BY SALARY DESC, EMPNO
 FETCH FIRST 10 ROWS ONLY;

FIRST and NEXT are alternative keywords in the fetch clause. FIRST reads naturally for an initial result; NEXT reads naturally after an OFFSET. ROW and ROWS are also interchangeable, although ROW is clearer for one and ROWS for larger values. The ONLY form never returns more than the requested count.

Limiting at the server can save work and network traffic. Db2 might stop processing after it has found enough rows, especially when a matching index can supply the ORDER BY sequence. This is different from retrieving a huge result into an application and discarding all but ten rows there.

OFFSET and pagination

OFFSET n ROWS skips n rows of the intermediate result before rows are returned. OFFSET 0 starts at the beginning. If the offset is greater than the number of rows available, the result is empty. Combine OFFSET with a fetch clause to make numbered pages.

1
2
3
4
5
6
-- Page 3 when each page contains 20 rows
SELECT EMPNO, LASTNAME, HIREDATE
  FROM DSN8C10.EMP
 ORDER BY HIREDATE DESC, EMPNO
 OFFSET 40 ROWS
 FETCH NEXT 20 ROWS ONLY;

ORDER BY is essential to meaningful paging. The final EMPNO key resolves ties, so two employees with the same hire date still have a defined order. Even then, separate page requests are separate units of work: inserts, deletes, or updates between requests can move rows across boundaries. Isolation protects a statement or unit of work; it does not freeze an application's multi-request browsing session forever.

Large offsets can be expensive because Db2 still has to locate and skip the earlier rows. For deep paging, keyset pagination is often better. Save the last ordered key from the current page, then request values after that key.

1
2
3
4
5
6
-- Continue after the last key from the previous page
SELECT EMPNO, LASTNAME, HIREDATE
  FROM DSN8C10.EMP
 WHERE (HIREDATE, EMPNO) < (:LAST-HIREDATE, :LAST-EMPNO)
 ORDER BY HIREDATE DESC, EMPNO DESC
 FETCH FIRST 20 ROWS ONLY;

The comparison and ORDER BY directions must describe the same continuation rule. Test composite-key paging carefully, especially when columns are nullable. OFFSET is easier for a small number of pages; keyset paging is usually more scalable and stable for a long result.

Statement isolation: WITH UR, CS, RS, and RR

A package or plan has an ISOLATION bind option, but an SQL isolation clause can override it for an individual statement. Db2 for z/OS supports WITH UR, WITH CS, WITH RS, and WITH RR. Stronger isolation usually provides more repeatable answers by holding more locks for longer, which reduces concurrency.

WITH UR: uncommitted read

UR offers the greatest concurrency and the least stability. A read-only query can see an insert or update that another unit of work has not committed. If that writer rolls back, the reader observed data that never became permanent. This is called a dirty read. UR avoids most data locks, but "no locking anywhere" is too broad: Db2 still needs serialization for objects and special data such as LOBs has additional rules.

1
2
3
4
5
-- Appropriate only when an approximate answer is acceptable
SELECT COUNT(*) AS ACTIVE_COUNT
  FROM APP.ACCOUNT
 WHERE STATUS = 'A'
 WITH UR;

A dashboard count might tolerate a temporary discrepancy. A payment, stock allocation, audit total, or authorization check should not. UR applies to read-only operations; it is not a way to perform unsafe uncommitted updates.

WITH CS: cursor stability

CS is the normal choice for transactional applications. It returns committed data and usually protects the row or page where a cursor is currently positioned. After the cursor moves on, the previous read lock can usually be released unless the application changed the row. Another transaction can then update that previous row. A second query in the same unit of work can therefore see a changed value or a newly qualifying row.

WITH RS: read stability

RS keeps locks on rows that qualify for the query until commit or rollback. Those rows cannot be changed underneath the application. However, a row that did not qualify can be updated so that it qualifies later, or a new qualifying row can be inserted. Such a newly appearing row is called a phantom. RS is useful when the rows already selected must remain stable but complete range protection is unnecessary.

WITH RR: repeatable read

RR provides the greatest stability and the least concurrency. Db2 protects the selection range, including data examined but rejected, so another transaction cannot make the answer set change before commit. That can hold many row or page locks and can increase waits or escalation risk. Use RR only when the business transaction truly requires a repeatable set, not as a general cure for application design problems.

SELECT and SELECT INTO statements using RS or RR can also request a lock mode with USE AND KEEP SHARE LOCKS, UPDATE LOCKS, or EXCLUSIVE LOCKS. Those locks are held until commit. This is a deliberate serialization tool, not a routine tuning switch.

FOR READ ONLY and FOR UPDATE

FOR READ ONLY

FOR READ ONLY, also expressed as FOR FETCH ONLY, says that the cursor will not be used for a positioned UPDATE or DELETE. The declaration removes ambiguity and can let Db2 use efficient row blocking for retrieval. Queries containing grouping, DISTINCT, set operations, or other non-updatable constructs are already read-only, but writing the clause can still document intent on an otherwise updatable select.

1
2
3
4
5
6
7
DECLARE C_REPORT CURSOR FOR
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE WORKDEPT = :DEPT
 ORDER BY LASTNAME, EMPNO
 FOR READ ONLY
 WITH CS;

FOR UPDATE

FOR UPDATE declares that an updatable cursor can be used for positioned changes. The optional OF list identifies columns that later positioned UPDATE statements can assign. It does not update anything at OPEN or FETCH time. The actual change occurs in UPDATE or DELETE with WHERE CURRENT OF.

1
2
3
4
5
6
7
8
9
10
DECLARE C_PAY CURSOR FOR
SELECT EMPNO, SALARY
  FROM DSN8C10.EMP
 WHERE WORKDEPT = :DEPT
 FOR UPDATE OF SALARY;
 
-- After OPEN and a successful FETCH:
UPDATE DSN8C10.EMP
   SET SALARY = :NEW-SALARY
 WHERE CURRENT OF C_PAY;

FOR READ ONLY and FOR UPDATE are mutually exclusive. A query must also be inherently updatable before FOR UPDATE is valid. Aggregation, DISTINCT, many joins, set operators, and other constructs can make a result read-only. Listing an updated column helps Db2 avoid an access path that could cause a changed index key to make the same row appear again during cursor processing.

OPTIMIZE FOR n ROWS

OPTIMIZE FOR tells Db2 approximately how many rows the application expects to retrieve. The optimizer can favor fast first-row response instead of the lowest cost for consuming the entire result. For example, a matching index path might return the first row quickly while a table scan plus sort would be cheaper only if the program reads every row.

1
2
3
4
5
6
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE LASTNAME >= :START-NAME
 ORDER BY LASTNAME, EMPNO
 OPTIMIZE FOR 1 ROW
 FOR READ ONLY;

The result is not limited to one row. The application can continue fetching, although a plan selected for a quick first row might perform poorly when thousands are consumed. Match the value to real behavior. Use FETCH FIRST when a hard cap is required. FETCH FIRST also gives Db2 useful row-limit information for optimization, so specifying both clauses is often unnecessary unless the expected number fetched differs from the maximum result size.

In distributed access, OPTIMIZE FOR can also influence retrieval and DRDA query-block behavior. A larger value can be useful for an application that downloads many rows, while a small value suits an interactive inquiry. It is still guidance, not a promise that Db2 will choose one particular index.

Other useful query constructs

EXISTS

EXISTS tests whether a subquery produces at least one row. The values selected inside the subquery do not matter; only existence matters. It is a natural way to express "return this department if it has at least one employee."

1
2
3
4
5
6
7
8
9
SELECT D.DEPTNO, D.DEPTNAME
  FROM DSN8C10.DEPT AS D
 WHERE EXISTS (
       SELECT 1
         FROM DSN8C10.EMP AS E
        WHERE E.WORKDEPT = D.DEPTNO
          AND E.SALARY > 90000
       )
 ORDER BY D.DEPTNO;

Db2 can often stop the existence test after finding one match. Do not add ORDER BY to the EXISTS subquery merely to find a "first" row; the predicate asks only whether any qualifying row exists.

SKIP LOCKED DATA

SKIP LOCKED DATA tells Db2 not to wait for certain incompatible row or page locks. It is intended for CS or RS and is useful for queue-like work where another worker already owns a locked item. The trade-off is an incomplete result. Db2 does not issue a warning that rows were skipped, and page locking can skip every row on a locked page.

1
2
3
4
5
6
7
8
SELECT WORK_ID, PAYLOAD
  FROM APP.WORK_QUEUE
 WHERE STATUS = 'READY'
 ORDER BY WORK_ID
 FETCH FIRST 1 ROW ONLY
 FOR UPDATE OF STATUS
 SKIP LOCKED DATA
 WITH CS;

Do not use SKIP LOCKED DATA for totals, compliance reports, or any query that must see every qualifying row. It is a work-distribution behavior, not a generic way to hide lock contention.

QUERYNO

QUERYNO assigns an integer to a query. It helps identify the statement in EXPLAIN tables and performance investigations, especially when a program contains many similar static statements. QUERYNO does not change rows, locks, or access-path semantics. Use stable, unique values within a package when your operational process depends on them.

How the clauses work together

Consider a screen that displays the newest twenty orders. ORDER BY defines newest. FETCH FIRST limits the result. FOR READ ONLY confirms that the screen will not use positioned updates. OPTIMIZE FOR 20 ROWS tells Db2 the retrieval expectation. WITH CS requires committed data without holding every order row until commit. QUERYNO makes the statement easy to find in EXPLAIN. If the screen advances to later pages, OFFSET is simple, while keyset pagination is better for deep or frequently changing data.

Now consider a worker that claims one queue row. FOR UPDATE expresses the positioned update intent. SKIP LOCKED DATA prevents waiting behind another worker. WITH CS provides the supported isolation behavior. FETCH FIRST 1 ROW ONLY prevents the cursor result from containing more than one candidate. These clauses form one concurrency design; copying only SKIP LOCKED DATA into unrelated reporting SQL would be wrong.

• Use ORDER BY whenever "first," "next," or a page number matters.
• Use FETCH FIRST or FETCH NEXT for a real maximum row count.
• Use OPTIMIZE FOR only when the expected retrieval count is truthful.
• Choose the weakest isolation that still meets the business correctness rule.
• State FOR READ ONLY or FOR UPDATE so cursor intent is unambiguous.
• Commit promptly when RS, RR, or update locks are held.

Explain It Like I'm Five

Imagine a shelf of numbered storybooks. ORDER BY tells the librarian how to line them up. OFFSET says, "walk past the first ten." FETCH FIRST says, "give me no more than five." OPTIMIZE FOR says, "I probably want only five, so choose a quick way to reach them," but it does not close the shelf after five. FOR READ ONLY means you promise not to write in the books. FOR UPDATE means you plan to edit the book under your finger. Isolation decides what happens while other children use the shelf: UR may show pencil marks not yet approved, CS protects the book you are holding, RS protects every book you chose, and RR reserves the whole part of the shelf you checked.

Exercises

1. Write a query for the five most recently hired employees. Include a unique tie-breaker and explain why it is needed.
2. Write page four of a result with 25 rows per page using OFFSET and FETCH NEXT. How many rows must be skipped?
3. Rewrite that page as keyset pagination using the last key from page three.
4. Compare WITH UR and WITH CS for an account-balance query. Which one is acceptable, and why?
5. Declare a cursor FOR UPDATE OF one column, then write the positioned UPDATE using WHERE CURRENT OF.
6. Explain why OPTIMIZE FOR 1 ROW cannot replace FETCH FIRST 1 ROW ONLY in correctness logic.
7. Design a queue-reader query using FETCH FIRST, FOR UPDATE, SKIP LOCKED DATA, and WITH CS. State why incomplete results are acceptable in that use case.

Frequently asked questions

Can FETCH FIRST prevent SQLCODE -811 in SELECT INTO?

A fetch clause can cap a SELECT INTO at one row, but you must still decide which row is correct. Add a meaningful ORDER BY when multiple rows can qualify. Better yet, make the predicate unique when business logic expects exactly one row; silently selecting one duplicate can hide a data-quality defect.

Is WITH UR always faster?

No. UR reduces data-locking overhead and waits, but the access path, predicates, indexes, statistics, sorting, and amount of data still dominate many queries. Its defining feature is weaker consistency, not guaranteed speed.

Can an ORDER BY cursor be used for positioned update?

Cursor updatability has restrictions, and an ORDER BY commonly makes a cursor read-only except for specific supported cursor forms. Check the DECLARE CURSOR and SELECT rules for your Db2 function level. Do not assume that adding FOR UPDATE can override a query that is inherently read-only.