SQLCODE +100: Row not found

What it means

Db2 reports the outcome of every SQL statement in the SQLCA. A zero SQLCODE means success, a negative one means the statement failed, and a positive one means it worked but with a warning. +100 is the most common warning of all. It says there was no data to return or change.

For a cursor it is normal and expected. Every FETCH loop ends with +100 once the last row has been read. For a singleton SELECT INTO it means no row matched the WHERE clause. No values come back, so the host variables hold nothing new and the program shouldn't use them. For a searched UPDATE or DELETE it means the WHERE clause matched nothing, so no row changed. SQLERRD(3) in the SQLCA holds the number of rows affected, which is zero here.

+100 only becomes a problem when the program doesn't expect it. Many batch programs test for an SQLCODE other than zero and treat anything else as fatal. In those programs a missing row ends the job with an error message and a user abend, even though Db2 reported no error.

Common causes

• A FETCH loop reached the end of the result table. This is the normal way a cursor tells the program there are no more rows.
• A SELECT INTO looked up a key that isn't in the table, for example a customer number from an input record that was never loaded.
• The WHERE clause compares values that look equal but aren't. A host variable with missing leading zeros, lowercase letters or trailing low-values won't match the stored key.
• A searched UPDATE or DELETE matched no rows because another job or user had already changed or deleted them.
• The program is reading the wrong copy of the table. A package bound with a test QUALIFIER, or a dynamic query run under a different CURRENT SCHEMA, can point at an empty or out-of-date table.
• An INSERT with a subselect found nothing to copy.

How to find the problem

1. Display SQLCODE, SQLSTATE and SQLERRD(3) right after the statement, along with the key values in the host variables. Most programs already print the SQLCA through DSNTIAR.
2. Run the same SELECT in SPUFI, QMF or DSNTEP2 with the same values typed in as literals. If it returns nothing there, the data is missing. If it returns a row, the host variables in the program don't hold what you think they hold.
3. Show the key host variables in hex. Low-values (X'00') or an unexpected character turn a match into a miss.
4. Check which table the program actually read. For static SQL, the QUALIFIER column of SYSIBM.SYSPACKAGE shows the schema Db2 used for unqualified table names.

How to fix it

• Test for +100 on its own. In COBOL, check SQLCODE = +100 before the general error test, or code EXEC SQL WHENEVER NOT FOUND. Treat it as end of data after a FETCH, or as record not on file after a SELECT INTO.
• End FETCH loops on +100, then CLOSE the cursor.
• If the row should have been there, fix the data or the earlier step that was meant to create it, then rerun.
• To check whether a row exists, use a SELECT INTO with FETCH FIRST 1 ROW ONLY or an EXISTS test. You get a clean 0 or +100 and can't hit -811.
• After UPDATE and DELETE, read SQLERRD(3) when the program needs to know how many rows changed.

What it looks like

PAYUPD1 - SELECT EMP_RATE FAILED FOR EMPNO 0047113
DSNT404I SQLCODE = 100, NOT FOUND:  ROW NOT FOUND FOR FETCH, UPDATE OR DELETE, OR THE
         RESULT OF A QUERY IS AN EMPTY TABLE
DSNT418I SQLSTATE   = 02000 SQLSTATE RETURN CODE

Last reviewed 2026-10-12 by the WND Software team. Written in our own words. For the full reference, check IBM’s documentation for your z/OS, CICS or Db2 release.