MainframeKit

+100

SQLSTATE 02000POSITIVE SQLCODE (WARNING)

No row was found: a FETCH reached the end, a SELECT INTO found nothing, or an UPDATE or DELETE matched no rows.

MOST LIKELY

End of a cursor result set (the usual loop exit).

FIRST CHECK

Test for SQLCODE +100 (or SQLSTATE 02000) explicitly and treat it as end of data, not as a failure.

TRY IT ▸

MainframeKit assistant

What it means

+100 is the normal 'no data' signal. It is not an error. A cursor FETCH returns it when it moves past the last row, a singleton SELECT INTO returns it when no row qualifies, and a searched UPDATE or DELETE returns it when nothing matched the WHERE clause.

It also appears when an INSERT with a subselect selects nothing, when SKIP LOCKED DATA skips every qualifying row, and when a rowset FETCH comes back with fewer rows than the rowset size; in that last case SQLERRD(3) tells you how many rows were returned.

Typical causes

  • End of a cursor result set (the usual loop exit).
  • A key value that does not exist in the table, so SELECT INTO finds nothing.
  • A WHERE clause on UPDATE or DELETE that matches no rows, often because of a wrong value, padding or data type mismatch.
  • Rows skipped because SKIP LOCKED DATA was used and they were locked.
  • A partial last rowset in a multi-row FETCH.

How to fix it

  1. Test for SQLCODE +100 (or SQLSTATE 02000) explicitly and treat it as end of data, not as a failure.
  2. For a multi-row FETCH, process the rows counted in SQLERRD(3) before leaving the loop.
  3. If rows were expected, display the host variable values used in the WHERE clause and run the same query in SPUFI or a query tool.
  4. Check for trailing spaces, wrong case or a wrong date format in the search value.

Example

Illustrative only; names and values are invented.

EXEC SQL FETCH C1 INTO :WS-EMPNO, :WS-NAME END-EXEC
IF SQLCODE = +100  SET END-OF-CURSOR TO TRUE
-- SQLSTATE 02000: no more rows